Hi,

I am facing a locking problem with DB2.
Following is the scenario :

A select query is executed on XX table in one trasaction
after that a batch(batch size is 10) insert on XX table is executed in different transaction.

The batch insert hangs up, it is waiting for select statement.
Same thing is working fine with Oracle database.

We are using DB29.5 with JBOSS5.
These flags are activated on DB side:

DB2_SKIPINSERTED=ON
DB2_SKIPDELETED=ON
DB2_EVALUNCOMMITTED=ON

Can you pls help us?

Thanks

What is your isolation level specified in java? I would add the following to the end of the select:

WITH CS;

If that doesn't work, add this to the end of the select:

WITH UR:

Hi Fedman,

Thanks for your reply.

I tried with both the options, but it's not working.

Is there any other configuration parameter that we are missing?

Thanks

I think you need to supply details that show why you think the select is blocking the insert.

I found it using a monitoring tool "db2mon"

I have Attached the screenshot.

Thanks

121 is in lockwait state, but how do you who is blocking it? UOW Waiting does not mean anything in this context.

Change the lockwait parm in the db config from -1 to 30 seconds (or whatever is necessary to force a locktimeout error) and follow the directions below to determine exactly what happens when you get a locktimeout (-911 rc 68):
New options for analyzing lock timeouts in DB2 9.5

Thank you.

Here is the locktimeout output :
LOCK TIMEOUT REPORT

Date: 14/09/2009
Time: 19:07:18
Instance: DB2
Database: TEST
Database Partition: 0


Lock Information:

Lock Name: 03003A00370000000000000052
Lock Type: Row
Lock Specifics: Tablespace ID=3, Table ID=58, Row ID=x3700000000000000


Lock Requestor:
System Auth ID: TEST
Application Handle: [0-32]
Application ID: 10.97.98.89.33805.090914130413
Application Name: db2jcc_application
Requesting Agent ID: 3760
Coordinator Agent ID: 3760
Coordinator Partition: 0
Lock timeout Value: 30000 milliseconds
Lock mode requested: .NS
Application Status: (SQLM_UOWEXEC)
Current Operation: (SQLM_EXECUTE)
Lock Escalation: No

Context of Lock Request:
Identification: UOW ID (5); Activity ID (1)
Activity Information:
Package Schema: (NULLID )
Package Name: (SYSSH200NULLID )
Package Version: ()
Section Entry Number: 1
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: INSERT INTO XX (YY_ID, SEQUENCE) VALUES ( ?, ?)


Lock Owner (Representative):
System Auth ID: TEST
Application Handle: [0-29]
Application ID: 10.97.98.89.33037.090914130410
Application Name: db2jcc_application
Requesting Agent ID: 1000
Coordinator Agent ID: 1000
Coordinator Partition: 0
Lock mode held: ..X

List of Active SQL Statements: Not available

List of Inactive SQL Statements from current UOW:

Entry: #1
Identification: UOW ID (84); Activity ID (2)
Package Schema: (NULLID )
Package Name: (SYSSH200)
Package Version: ()
Section Entry Number: 1
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: UPDATE YY SET col1 = ? , col2 = ? , col3 = ? WHERE YYID = ?

Entry: #2
Identification: UOW ID (84); Activity ID (1)
Package Schema: (NULLID )
Package Name: (SYSSH200)
Package Version: ()
Section Entry Number: 1
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: SELECT * from YY WHERE YYID = ?


Here YY is the parent table and XX is the child table.
While iserting into xx table, yy table id is used.

Thanks

Looks to me like maybe the UPDATE is blocking the insert, not SELECT. You could do a snaphot for locks (turn lock monitor on in the dbm cfg before hand) to see what locks are being held.

also doyou have an index on child table to support the RI check being performed when you update he YY table?
Dave

Have you identified the table referred to in the Lock Specifics of your Lock Time Out Report? Make sure you positively identify what tables are holding locks.
We have had nothing but problems since implementing v9.5. I have an added layer of bugs called Federation Server. I found all my deadlocks and lock timeouts are on SYSIBM tables. I found some of my packages had changed from valid to invalid. This causes my applications to rebind on every execution. The locking happens during the binding process, nothing to do with our code or source tables. Rebinding my packages only makes them valid for a period of time. I had to figure all that out in order to defend the Federation Support suggestion that we may need to check our application code. Rebinding my packages only makes them valid for a period of time. I guess they don't actually go through at all those traces, logs and command output they always ask for. Federation Server support and db2 support keep passing the PMR off to one another.
Good luck! I am about ready to return to the unsupported v8.2.

Thank you very much for your all responses

The problem was with update on yy table.

Now we are excuting both the statements in same transaction and it resolve the problem.

Can you please provide a link which will give the information about how we can resolve the locking problems in DB2?

Thanks

Locks are released when a transaction is closed or a commit is issued. Many applications these days are poorly coded, and don't do these things properly. I don't know if that is the problem in your case, but it sound like it is.