Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Unanswered: db2 OS390 TABLE LOCK DURING DELETE

    Hi all.


    We are having a typical problem:

    Under a transaction, we are deleting some records ( say delete from Table1 where ID = 'X') , before we commit another process wants to issue the same command with different parameter ( say delete from Table1 where ID = 'Y') - getting a dead lock. We set the lock size to high limit to prevent escalation.

    Any suggestions?


    Regards
    Satya

  2. #2
    Join Date
    Nov 2002
    Posts
    4

    Re: db2 OS390 TABLE LOCK DURING DELETE

    Originally posted by satya400
    Hi all.


    We are having a typical problem:

    Under a transaction, we are deleting some records ( say delete from Table1 where ID = 'X') , before we commit another process wants to issue the same command with different parameter ( say delete from Table1 where ID = 'Y') - getting a dead lock. We set the lock size to high limit to prevent escalation.

    Any suggestions?


    Regards
    Satya

  3. #3
    Join Date
    Nov 2002
    Posts
    4

    Re: db2 OS390 TABLE LOCK DURING DELETE

    Further we even tried with combinations of UR, RS, CS - still having the same problem. The requirement is that , we can not stop the 2nd transaction till the commit/rollback of the first one.


    Please suggest



    TIA

    Satya

  4. #4
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    "We set the lock size to high limit to prevent escalation."
    - If you set the lock to high limit, you're getting a tablespace lock (X-Lock). X-Locks (eXclusive Locks) do not allow any other transaction to read or update the data (exception: Uncommited Read - UR). So, it's working as designed!

    How can you change it?
    1.- you can increase the timeout parameters (I think, there are ZPARMs for RESOURCE TIMEOUT and DEADLOCK TIMEOUT). Take care: you're changing your environment and what is good for one transaction can be very bad for many others!
    2.- you can check the SQLCODE in transaction 2; if you become a -911, you can restart the transaction (DB2 made a ROLLBACK); if you become a -913, you can resend the SQL command (no ROLLBACK).
    3.- "we are deleting some records ( say delete from Table1 where ID = 'X') ". If ID is not unique and you're getting many pages, you could define a CURSOR WITH HOLD, set the locksize to PAGE, set your ISOLATION LEVEL to CS and COMMIT after every UPDATE.
    4. you could, in transaction 2, issue a "LOCK TABLESPACE IN EXCLUSIVE MODE". Just start the transaction after getting the locks ("LOCK" in a loop)
    - WHILE SQLCODE <> 0
    "LOCK TABLESPACE..."
    END-WHILE
    BEGIN TRANSACTION
    ...
    END-TRANSACTION

    5. (...)



    HTH
    Rodney Krick

  5. #5
    Join Date
    Nov 2002
    Posts
    4
    Hi Rodney,
    Thank u very much. I changed the code so that it catches the SQLState and either retries / rollsback. Thanks a lot.

    By the way any reason on why DB2 is locking the table when i delete few records?

    With Regards
    Satya

  6. #6
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    You can define the lock size when you create your tablespace (parameter LOCKSIZE). If you set it to TABLE or TABLESPACE, DB2 will lock the entire TABLE/TABLESPACE when you INSERT/UPDATE/DELETE against the table. For OLTP systems, it's normally recommended to use LOCKSIZE PAGE or ANY (DB2 optimizes it), so you have more concurrency among transactions. As you wrote, "We set the lock size to high limit to prevent escalation.", so I suppose, you have LOCKSIZE TABLESPACE. Remember: if you choose a fine granularity, you have an overhead in IRLM (250 bytes for each lock in the internal lock table and the management of locks) but you win concurrency. If you don't wish this, you can avoid the lock overhead, and increase the locksize. You loose concurrency.
    Also: know your data, know your workload, design for performance.

    HTH
    Rodney Krick

Posting Permissions

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