Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums 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
apvardhini

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

Senior Member


Joined: 21 Sep 2010
Posts: 1805
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.


Code:
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)
 lock.
 
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am

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