Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
Can a select query participate in a deadlock situation

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

New User

Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Fri May 29, 2015 12:39 pm    Post subject: Can a select query participate in a deadlock situation
Reply with quote

Hi All,
Can you please clarify if a select query can participate in a deadlock situation resulting is SQLCODE -913 sqlerrmc 00c90088, type 302
Back to top
View user's profile Send private message

Rohit Umarjikar

Global Moderator

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

PostPosted: Fri May 29, 2015 8:27 pm    Post subject:
Reply with quote

Always acquire locks in the same order (this avoids deadlocks) and use with UR.

The select statement runs, and first has to be connected and in the context
 of a database, so it get's a shared database lock:
 S DB (shared database)
 Then it wants to access a table, so it gets a shared lock on the table:
 S Table (shared table)
 Next, it signals its intention to get shared locks on stuff in the table by
 placing an intent shared.
 IS Table (table intent shared)
Now that it has these locks, it can begin to hunt for data. Assuming the
 worst case of query that requires a few hundred bookmark lookups: it needs
 to place some shared locks on the index it's using. Let's assume the index
 requires 5 pages to hold all of it. It first places an
 IS Index (index intent shared)
 lock on the index that it will be getting shared locks on, and then a
 S Index (shared index)
 lock on the first page of the index. The index page is scanned, and matching
 cluster keys are stored. It then releases the S lock on the first index
 page, and places an S lock on the 2nd index page....

Meanwhile, back in the other process and UPDATE wants to run. The connection
 has a S DB lock. Next it is dealing with a table, and places an S lock on
 the table. Now that it will be updating rows in that table, it signals its
 intention by placing an
 IU Table (table intent update)
Next it needs to figure out which rows it will update, so it scans the table
 and indexes, placing S locks as it reads the values, releasing the S locks
 when it's done reading that stuff, and then placing an U (update) lock on
 rows it wants to update. If it wants to eventually update a lot of rows, it
 might escalate the row level U locks to page level U locks. Since U locks
 are compatible with S locks, and S locks are compatible with U locks, there
 is no lock contention.
Back in the select statement, it has been getting and releasing S (shared)
 locks without incident.
When the UPDATE has finally marked all rows it will be updating with U
 (update) locks, it then goes back and tries to convert all those U locks to
 X (exclusive) locks. It indicates it's intention to place exclusive locks on
 the table by trying to place an
 IX Table (table intent exlusive)
But since our select has some S shared locks on the table, the UPDATE will
 hang waiting for the select to finish.
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 Need to select all records belonging ... Q5P418 DB2 2 Mon Jun 22, 2020 11:34 pm
No new posts DB2 deadlock Vasanth Kumar S K DB2 2 Mon Jun 08, 2020 8:23 am
No new posts Mainframe ISPREDIT Macro query upendrasri TSO/ISPF 3 Wed May 27, 2020 1:12 pm
No new posts DFSORT Output file order query A_programmers DFSORT/ICETOOL 2 Thu Mar 26, 2020 11:59 pm
No new posts Query on JCL abdulrafi DFSORT/ICETOOL 5 Wed Oct 16, 2019 7:02 pm

Back to Top
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us