|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I have a cursor declared as blow
| Code: |
declare c1 cursor for
select a,b,c,d,e,f,g
from taba
where a=:a
and b=:b
for update
|
if a duplicable index defined on column(a,b), there is no problem for high volumn transaction ;
but if no index defined on column a,b, when fetch operation is performed against the above declared cursor, program error with sqlcode -911, which indicates deadlock occurred on RID of tablespace.
the default isolation level of the program is CS, when no index defined on the columns in the where clause, we can expect tablescan is chosen by DB2.
I'm confused why tablescan can cause deadlock for high-volumn transaction? I can understand that might happend for isolation level RR or RS, but why CS? |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi There,
What is the lock size defined for this tablespace? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
-911 indicates deadlock OR timeout, and both need at least 2 transactions.
a timeout is more likely in this case.
* The "for update " causes a U-lock to each page you're trying to read.
* A tablescan reads a lot more pages than a Direct indexscan.
thus the chance that a page you're trying to read is already U- or X-locked is a lot higher.
replace "page" by "row" if you have row-locking. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| guptae wrote: |
Hi There,
What is the lock size defined for this tablespace? |
locksize is ROW lock |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| GuyC wrote: |
-911 indicates deadlock OR timeout, and both need at least 2 transactions.
a timeout is more likely in this case.
* The "for update " causes a U-lock to each page you're trying to read.
* A tablescan reads a lot more pages than a Direct indexscan.
thus the chance that a page you're trying to read is already U- or X-locked is a lot higher.
replace "page" by "row" if you have row-locking. |
thanks for your kind reply.
I examed the reason code for -911, and it indicates deadlock.
The locksize of the tablespace is already set to ROW lOCK |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Did you check for any other thread holding this table at the same time or did you make another run afterwords? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| Rohit Umarjikar wrote: |
| Did you check for any other thread holding this table at the same time or did you make another run afterwords? |
I can be sure that records that satisfies the selection criteria of the cursor only belongs to only one task, and will not become selection candidates for other tasks. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Did you make another run? Did you try the same query in SPUFI or any tool? And can you try doing this by removing FOR UPDATE from the cursor and see what SQLCODE you get? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
* it is irrelevant wether the row satisfies the selectgion criteria. The U-lock will be taken before evaluation of the criteria.
* just to be sure : yoiu don't have lock escalation ? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| GuyC wrote: |
* it is irrelevant wether the row satisfies the selectgion criteria. The U-lock will be taken before evaluation of the criteria.
* just to be sure : yoiu don't have lock escalation ? |
how to check if there is lock escalation? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
| Back to top |
|
|
|
|
