DB2 INSERT INTO…SELECT锁定 [英] DB2 INSERT INTO … SELECT lock

问题描述

有一个应用程序Application1，它发出我们在Prod中使用的插入语句

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)

还有另一个应用程序Apllication2正在对Table2进行选择查询

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.

我试图查找Table2是否由于作为插入语句的一部分而被锁定，但是我找不到任何具体证据.

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.

我确实为MySQL找到了一些东西如何提高INSERT INTO ... SELECT锁定行为

I did find something for MySQL How to improve INSERT INTO ... SELECT locking behavior

但是对于DB2则什么也没有.

but nothing for DB2.

有人可以帮我理解缓慢的原因吗?

Can somebody please help me understand the cause of slowness ?

推荐答案

insert语句几乎可以肯定地针对table2发出了锁.但是，如果您的第二条语句具有 with UR ，则很可能可以避免这些锁定.如果您有测试系统，则可以尝试将注册表变量DB2_WORKLOAD设置为WAS(这将设置所有这些设置:

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).

我建议使用 dsmtop 或 MONREPORT.DBSUMMARY (

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.

这篇关于DB2 INSERT INTO…SELECT锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！