IBM Mainframe Forum Index
Log In
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register

Can a select query participate in a deadlock situation

IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 2588
Location: NYC,USA

PostPosted: Fri May 29, 2015 8:27 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2


Similar Topics
Topic Forum Replies
No new posts problem in select max when executing ... IMS DB/DC 6
No new posts Need Help with on of the coalesce query DB2 4
No new posts Need to select all records belonging ... DB2 2
No new posts DB2 deadlock DB2 2
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
Search our Forums:

Back to Top