Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: Does "INSERT INTO XXX" lock the whole table?

    Hi,

    My application's Connection is in default transaction isolation level. I observed that the "INSERT INTO XXX" takes long time to commit if multiple threads are doing the same thing. I guess "Insert into XXX" lock the whole table if the connection is in default isolation level. Can anyone confirm it?

    Thx

  2. #2
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: Does "INSERT INTO XXX" lock the whole table?

    Originally posted by AlmostCrazy
    Hi,

    My application's Connection is in default transaction isolation level. I observed that the "INSERT INTO XXX" takes long time to commit if multiple threads are doing the same thing. I guess "Insert into XXX" lock the whole table if the connection is in default isolation level. Can anyone confirm it?

    Thx
    If you're running it on Windows/Unix it depends on the number of locks, the locklist size (LOCKLIST) and the percentage of this locklist which an application is allowed to use (MAXLOCKS).
    Both are set at the database level and you can change them with the db2 update db cfg for <dbname> using .. command.

    To find which locks your application is holding you can use the db2 get snapshot for locks on <dbname> command after enabling the switch to collect lock statistics with db2 update monitor switches using lock on.

    Hope this helps

  3. #3
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Suggestions

    Hi,

    As suggested please take the snapshots as suggested also would like you to check whether are you firing commits it's recommended that you fire commits often lest you will run into problems.

    Also create indexes on the table that way it will avoid it to lock at the table level.

    Moreover have a look at your bufferpool parameters and the sort heap parameters as well.

    Regards

    Nitin.

Posting Permissions

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