Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

table scan can cause deadlock over RID?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 04, 2014 8:36 am    Post subject: table scan can cause deadlock over RID?
Reply with quote

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
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Jul 04, 2014 11:52 am    Post subject:
Reply with quote

Hi There,

What is the lock size defined for this tablespace?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jul 04, 2014 1:14 pm    Post subject:
Reply with quote

-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
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 04, 2014 9:48 pm    Post subject:
Reply with quote

guptae wrote:
Hi There,

What is the lock size defined for this tablespace?

locksize is ROW lock
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 04, 2014 9:50 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Sun Jul 06, 2014 9:34 am    Post subject:
Reply with quote

Did you check for any other thread holding this table at the same time or did you make another run afterwords?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Jul 06, 2014 5:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Sun Jul 06, 2014 11:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jul 07, 2014 3:01 pm    Post subject:
Reply with quote

* 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
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Jul 08, 2014 7:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Jul 08, 2014 8:19 pm    Post subject:
Reply with quote

Quote:
how to check if there is lock escalation?


Did you attempt to speak to your DBA about this? He can provide you all the details but also can be referenced to this link.

http://www-01.ibm.com/support/knowledgecenter/?lang=en#!/SSEPEK_10.0.0/com.ibm.db2z10.doc.perf/src/tpc/db2z_lockescalation.dita
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts CICS TS 5.3 migr after DFHCSDUP UPGRA... Kyle Carroll CICS 6 Wed Aug 31, 2016 10:58 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us