SQLFetchScroll and deadlock
Nigel Robbins
My application uses the CLI on AIX.
I periodically (typically when under load) get an error on calling
SQLFetchScroll...
[IBM][CLI Driver][DB2/6000] SQL0501N The cursor specified in a FETCH
or CLOSE statement is not open. SQLSTATE=24501
The preceding error is...
[IBM][CLI Driver][DB2/6000] SQL0911N The current transaction has been
rolled back because of a deadlock or timeout. Reason code "2".
SQLSTATE=40001
It looks like the deadlock/rollback has closed the cursor.
Thoughts on how/why the deadlock occurred ?
Many thanks,
Nigel Robbins
Jerry Stuckle
Deadlocks are always application problems. They occur with one
transaction has a lock on a resource another transaction requires, and
vice versa.
For instance:
Transaction "A" updates a row "1". "A" has "1" locked.
Transaction "B" updates a row "2". "B" has "2" locked.
Transaction "A" tries to update row "1". "A" waits due to the lock
"B" holds.
Transaction "B" tries to update row "2". "B" waits due to the lock
"A" holds.
This is a typical deadlock. Rather than wait forever, DB2 will rollback
one of the transactions and return SQLCODE -911.
They can be very timing sensitive - for instance, if either "A" or "B"
above finishes before the other makes its requests, both transactions
will run fine.
You can start an event monitor to watch for deadlocks; you may also be
able to limit the effects of deadlocks by using a less restrictive
isolation level (depending on the exact cause).
But your best choice is to find out what transaction(s) is/are involved
and recode them, if possible.
BTW, the DB2 help files contain a fair amount of info on deadlocks. I
suggest you search the doc for "deadlock" for more help.
--
====================================
To reply, delete the 'x' from my email
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
====================================
Fan Ruo Xin
Nigel Robbins
Many thanks for your replies.
I've looked at the DB2 documentation regarding deadlocks but I am
still a bit confused.
I have a read stability isolation level.
I set up a test scenario with one session doing...
update temp set col1=5 where col1=1
(without doing a commit).
Another session was trying to read a different row from the table...
select * from temp where col1=2 for ready only
This session timed out with SQL0911N, Reason code “68”,
SQLSTATE=40001.
When I looked at the snapshot I found that three locks had been
granted…
For the update, X for row and IX for table.
For the select, IS for table.
Do you know if there is a locking strategy that will enable me to read
committed data even when there are outstanding uncommitted writes ?
Best regards,
Nigel
Jerry Stuckle
he only way you can do a SELECT * from a table with uncommitted updates
and not wait is to use the UR (Uncommitted Read) isolation level. But
this will return all rows, whether they are committed or not, which
isn't what you want.
The secret here is to not have a long time between an insert or update
and the rollback or commit. For instance - don't update one row then
wait for user input before the commit/rollback.
Also, have appropriate indicies on the table will help prevent table
scans (which can also run into locks). For instance - if you have two
programs which do:
UPDATE TABLEA SET BCOLUMN = 'John' WHERE ACOLUMN = 1; -- Transaction
"A"
UPDATE TABLEA SET BCOLUMN = 'DICK' WHERE ACOLUMN = 2; -- Transaction
"B"
If you have no index on ACOLUMN, DB2 may do a table scan in both cases -
and the second one will wait on the lock. However, if you have an index
on ACOLUMN, DB2 should do an index scan and Transaction B will not lock.
Also, as I hinted at above, the isolation level will affect the locks
being held. Typically, the higher the isolation level, the more
restrictive the concurrency - but the more accurate any one transaction
is. For example - UR is the least restrictive, but can return
uncommitted data. RR is much more restrictive, and typically holds more
locks than anything else - but guarantees that a second read within the
same UOW will get the same data.
There's always a tradeoff between concurrency and data integrity (i.e.
repeatability of a read). Good application design can limit the
problems which may occur.
--
Fan Ruo Xin
Nigel Robbins wrote:
> Hi Jerry/Fan,
>
> Many thanks for your replies.
>
> I've looked at the DB2 documentation regarding deadlocks but I am
> still a bit confused.
>
> I have a read stability isolation level.
>
> I set up a test scenario with one session doing...
> update temp set col1=5 where col1=1
> (without doing a commit).
>
> Another session was trying to read a different row from the table...
> select * from temp where col1=2 for ready only
> This session timed out with SQL0911N, Reason code “68”,
> SQLSTATE=40001.
>
When you select, besides IS, the application still need aquire an NS, and
which will not compatible with X lock, So it will wait until timeout or
the other application release the X lock.
Sailesh Krishnamurthy
> Nigel,
>
> Deadlocks are always application problems. They occur with one
> transaction has a lock on a resource another transaction requires, and
> vice versa.
We must remember that Nigel's second query (transaction T2) was
conditional. Since he is using the RS isolation level, T2 should only
lock the matching objects and not all the records. Given that the
first transaction updated only records where col1 matched the value
"1" I'm not sure why there should be a deadlock.
T1,Q1: update T set c1 = 5 where c1 = 1
T2,Q2: select * from T where c1 = 2 (for read only)
So T1 gets an IX lock for T and an X lock for records with C1 = 5.
These will be commit duration locks. T2 gets an IS lock on the table
(compatible with IX) and should only _need_ S locks on records with c1
= 2: which should not interfere with T1.
I guess when there is no index on T.c1, both transactions perform
table scans and so T2 ends up waiting on the X lock acquired by T1 on
the record with c1=1 (used to be 5). If T1 does not commit but just
waits, I don't understand why there is a deadlock here. T2 -> T1 ..
maybe there is a timeout which causes the T2 cursor to be closed, but
that's a bit dubious.
If there is an index, things change. Although both transactions are
RS, the updating transactions has to follow a protocol to make sure it
doesn't step onto potential RR transactions. Since DB2 UDB uses
ARIES/IM (data-only locking) T1 should grab an IX lock on T and an X
lock on the record corresponding to value (c1=1). Is that enough ? T2
could rollback and that will require the index key value (c1=5) being
safeguarded .. even for RS isolation. So T1 gets an X lock on new
value (c1=1) and it has to get a commit duration X lock on the row
corresponding to the value after (c1=5) which maybe end-of-table. I am
treating an update as a delete+insert. Deletes require commit duration
X lock on the record corresponding to next key, inserts require
instant duration IX on the record corresponding to next key. Of course
another scheme could involve pseudo-deletes but I'm pretty sure that
DB2 UDB uses does not use that.
For the rest of this discussion let's assume that locks are only
grabbed on rids inspite of my using index key values.
With RR isolation I can see how a deadlock can happen assuming index
has entries (0,1,2,5,6):
T1 gets an IX lock on Table T
T2 gets an IS lock on Table T, begins index scan for value 2.
T1 gets a commit duration X lock on 6. Deletes value 5.
T2 gets an S lock for value 2 and tries for S lock on next value 6 -
it blocks.
T1 now needs to insert value 1 -
Tries to get an "instant duration" IX lock on the next key (value
2!!)
So now T2 waits on T1 and vice-versa leading to a deadlock. But the
key to recreate this was T1 being in RR isolation. Since the posters
avers that he was using RS isolation, I am a bit flummoxed.
I am interested in solving the problem or having my calculations
pointed wrong !! Actually vitally interested for selfish self-interest
reasons, but that is a story for later less tense times.
Finally, I believe all the problems can be solved by the reader using
CURSOR STABILITY (CS) isolation (Degree 2). That way the reader only
holds short-duration read locks for the duration the cursor is
positioned against that record. When tricks like Commit_LSN get
implemented that can be very fast.
Pip-pip
Sailesh