If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > how to know whether a table is locked

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Posts: 3
how to know whether a table is locked

I want to create a SP. In that SP,

1. I will lock table A
2. And then insert records to table A
3. At last unlock table A

My question, how to know whether table A is locked by other transaction?

Thanks,
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2007
Location: somewhere in dbforums
Posts: 221
DB2 will handle that internally....

Suppose your transaction A does what you have mentioned and you have another transaction B which does the same thing, then B will see that table is in locked state and will wait till the lock is released to carry out its transcation...you dont hae to handle that explicitly

With the amount of data that you have provided i hope that i am pointing you in the right direction....
__________________
IBM Certified Database Associate, DB2 9 for LUW
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,722
You can use the following command before you try to obtain the lock to find out if your lock can be obtained immediately:

SET CURRENT LOCK TIMEOUT NOT WAIT

This means that application is not to wait for locks that cannot be obtained, and an error (SQLSTATE 40001 or SQLSTATE 57033) will be returned. Otherwise, the application will wait the amount of time specified in the LOCKTIMEOUT db cfg parm (but if = 0 in the db cfg it will wait forever unless a deadlock occurs).

The statement is not under transaction control (not affected by a COMMIT) but it only applies to the application connection that issued it. I am not sure about how connection pooling might affect this (if multiple applications are using the same connection).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
if you just want to see the LOCKs, the LOCK-type and the corresponding Application holding the lock on the table, you can take the SNAPSHOT of Locks on real-time and analyse the same:

e.g.
db2 "get snapshot for LOCKS on SAMPLE global" > snap_file.dat (for DPF, SAMPLE is the DB name)
db2 "get snapshot for LOCKS on SAMPLE " > snap_file.dat (for non-DPF)

Now, open the file snap_file.dat and search for TABLE A, you will see the associated LOCKS on that table, The authorization ID holding the Lock and the Application Details.
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Posts: 3
Quote:
Originally Posted by Marcus_A
You can use the following command before you try to obtain the lock to find out if your lock can be obtained immediately:

SET CURRENT LOCK TIMEOUT NOT WAIT

This means that application is not to wait for locks that cannot be obtained, and an error (SQLSTATE 40001 or SQLSTATE 57033) will be returned. Otherwise, the application will wait the amount of time specified in the LOCKTIMEOUT db cfg parm (but if = 0 in the db cfg it will wait forever unless a deadlock occurs).

The statement is not under transaction control (not affected by a COMMIT) but it only applies to the application connection that issued it. I am not sure about how connection pooling might affect this (if multiple applications are using the same connection).
Thanks a lot
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Quote:
Originally Posted by smell
I want to create a SP. In that SP,
1. I will lock table A
2. And then insert records to table A
3. At last unlock table A
My question, how to know whether table A is locked by other transaction?
Setting the LOCK TIMEOUT to NOT WAIT is probably a bit too extreme, certainly within a SP.
The "normal" way for a SP to "gracefully" handle locks, would be to just wait for the timeout (which could happen either after the explicit LOCK TABLE (your step 1), or after the INSERT (when you would have no step 1)), and then catch the SQLSTATE mentioned by Marcus. Then, options are to either retry (which you probably don't want to do) or to return from the SP to the calling application with a specific return code, telling the user of the unsuccessful INSERT.
You could e.g. use LOCK TIMEOUT to set the timeout to a more reasonable (i.e., smaller) value than the current default. (BTW, is that still 30 sec?)
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,722
Quote:
Originally Posted by Peter.Vanroose
Setting the LOCK TIMEOUT to NOT WAIT is probably a bit too extreme, certainly within a SP.
The "normal" way for a SP to "gracefully" handle locks, would be to just wait for the timeout (which could happen either after the explicit LOCK TABLE (your step 1), or after the INSERT (when you would have no step 1)), and then catch the SQLSTATE mentioned by Marcus. Then, options are to either retry (which you probably don't want to do) or to return from the SP to the calling application with a specific return code, telling the user of the unsuccessful INSERT.
You could e.g. use LOCK TIMEOUT to set the timeout to a more reasonable (i.e., smaller) value than the current default. (BTW, is that still 30 sec?)
The default in the db cfg is 0, which means wait forever (unless deadlock occurs).

Setting the "LOCK TIMEOUT to NOT WAIT" is not too extreme, especially for a stored procedure. I don't know where you got that idea. Obviously it depends on the application logic, but it is a very useful technique in the right situation.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Quote:
Originally Posted by Marcus_A
Setting the "LOCK TIMEOUT to NOT WAIT" is not too extreme, especially for a stored procedure.
Which would mean that you would get an SQL error whenever an other application has an outstanding lock at the moment of your "LOCK TABLE" statement, OK?

In case of a heavily accessed database, a table could be (partially) locked 90% of the time, while the average waiting time could be just half a second or so; in those cases I would set the lock timeout to somewhere between 1 and 5 seconds.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,722
Quote:
Originally Posted by Peter.Vanroose
Which would mean that you would get an SQL error whenever an other application has an outstanding lock at the moment of your "LOCK TABLE" statement, OK?

In case of a heavily accessed database, a table could be (partially) locked 90% of the time, while the average waiting time could be just half a second or so; in those cases I would set the lock timeout to somewhere between 1 and 5 seconds.
You would get a return code back to the program telling you that resource you want is locked. At that point, you may want to do something else (immediately) instead of waiting up to 30 seconds (typical LOCKTIMEOUT value) to do something else.

This is certainly not "normal" application logic, but there are situations where it is very useful. Since the OP asked how to do this, I assume they know what they are doing and actually need this feature. I have used this feature myself in some situations.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 1,042
I would tend to go another way on this topic. Why do want to lock the entire table while you insert some records?

Dave
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On