Results 1 to 10 of 10
  1. #1
    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,

  2. #2
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  4. #4
    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

  5. #5
    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

  6. #6
    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/

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  8. #8
    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/

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,102
    I would tend to go another way on this topic. Why do want to lock the entire table while you insert some records?

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •