Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Question Unanswered: Selecting Record results in Lock Timeout

    I have a table that has two records that if they are queries result in a Lock Timeout. For instance:

    select * FROM FBNEXUS.FBLOCATION WHERE locationid=231959
    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001

    All other records in the table can be queried without problem. When taking a snapshot, I do see that there is one reorg that is also in a lock wait status. But otherwise, I cannot figure out what is keeping these records from being read. Out of desperation, I did bounce the db2 server, but the problem remained.

    1. Is there some way I can force DB2 to "release" whatever is keeping these records from being read?

    2. What circumstances can lead to a record being held in such a way that trying to read it results in a lock timeout?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Please include application and lock snapshot. Also, check for indoubt transactions using "db2 list indoubt transactions"

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    Thanks for your help. I attached both snapshots.
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Posts
    24
    Also, there was one indoubt transaction:

    1. originator: XA

    appl_id: 10.100.112.221.37584.1102211300 sequence_no: 0001 status: i

    timestamp: 02/21/2011 06:58:52 auth_id: DB2ADMIN

    log_full: n type: RM

    xid: 030002001D000000 1B000000312D6136 34373064643A6533 32353A3464333961

    3030663A39653539 6661363437306464 3A653332353A3464 3339613030663A39

    65356231

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    It was an indoubt transaction that was holding the locks. The application handle is 0:


    Application handle = 0

    Application ID = 10.100.112.221.37584.1102211300

    Sequence number = 0001

    Application name = db2jcc_application

    CONNECT Authorization ID = DB2ADMIN

    Application status = Transaction Prepared

    Status change time = Not Collected

    Application code page = 1208

    Total wait time (ms) = 0



    List Of Locks

    Lock Name = 0x07000D0208002B9F0A00000052

    Lock Attributes = 0x00000200

    Release Flags = 0x40000000

    Lock Count = 255

    Hold Count = 0

    Lock Object Name = 45620068360

    Object Type = Row

    Mode = X



    Lock Name = 0x07000D0202006CCB0A00000052

    Lock Attributes = 0x00000200

    Release Flags = 0x40000000

    Lock Count = 255

    Hold Count = 0

    Lock Object Name = 46362525698

    Object Type = Row

    Mode = X



    Lock Name = 0x07000D02000000000000000054

    Lock Attributes = 0x00000200

    Release Flags = 0x40000000

    Lock Count = 255

    Hold Count = 0

    Lock Object Name = 525

    Object Type = Table

    Mode = IX

Posting Permissions

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