DB2 INSERT INTO … SELECT lock

Question

There is an application Application1 which is issuing insert statement that we are using in Prod

    INSERT INTO Table1
    (SELECT FROM Table2
    WHERE conditions are true)

There is another application Apllication2 which is doing a select query on Table2

    SELECT FROM Table2
    WHERE conditions are true
    with ur

Now whenever the insert query is running, the second query is running very slow, sometimes getting read timed out.

I tried to find if the Table2 was getting locked due to being part of the insert statement, but I couldn't find any concrete evidence.

but nothing for DB2.

Can somebody please help me understand the cause of slowness ?

Do you just want an explanation, or also a solution? – Tim Biegeleisen Aug 03 '18 at 06:01 — Tim Biegeleisen, Aug 03 '18 at 06:01

score 2 · Answer 1 · answered Aug 03 '18 at 11:31

The insert statement is almost certainly issuing locks against table2. However, if your second statement has with UR then it is likely it can avoid those locks. If you have a test system you can try setting the registry variable DB2_WORKLOAD to WAS (which sets all these: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0011218.html).

I suggest using dsmtop or MONREPORT.DBSUMMARY (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0056370.html) to determine where time is actually being spent for the read-only query.

score 0 · Answer 2 · answered Aug 03 '18 at 06:40

0

You can use second query as below: SELECT FROM Table2 WITH(NOLOCK) WHERE conditions are true

Note: But It will give you dirty read.

answered Aug 03 '18 at 06:40

Deep patel

Edited the question.The second query is run WITH UR. – mks17 Aug 03 '18 at 07:02

2 Answers2