SQL 如何使用 Oracle 解决 ORA-02049 和锁定问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2506411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to troubleshoot ORA-02049 and lock problems in general with Oracle
提问by sandos
I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT.
对于一些长时间运行和/或密集的事务，我偶尔会收到 ORA-02049。这似乎没有模式，但它发生在一个简单的 INSERT 上。
I have no clue how to get any sort of information out or Oracle, but there has to be a way? A log over locking or atleast a way to see current locks?
我不知道如何从 Oracle 或 Oracle 中获取任何类型的信息，但必须有办法吗？登录锁定或至少一种查看当前锁定的方法？
采纳答案by VikrantY
One possible way might be to increase the INIT.ORAparameter for distributed_lock_timeoutto a larger value. This would then give you a longer time to observe the v$locktable as the locks would last for longer.
一种可能的方法是将INIT.ORAfor 参数distributed_lock_timeout增加到更大的值。这将使您有更长的时间来观察v$lock表，因为锁会持续更长时间。
To achieve automation of this, you can either
要实现此自动化，您可以
Run an SQL job every 5-10 seconds that logs the values of
v$lockor the query that sandos has given above into a table and then analyze it to see which session was causing the lock.Run a
STATSPACKor anAWRReport. The sessions that got locked should show up with high elapsed time and hence can be identified.
每 5-10 秒运行一次 SQL 作业，将
v$locksandos 上面给出的值或查询记录到表中，然后分析它以查看导致锁定的会话。运行一个
STATSPACK或一个AWR报告。被锁定的会话应该显示时间很长，因此可以被识别。
v$sessionhas 3 more columns blocking_instance, blocking_session, blocking_session_statusthat can be added to the query above to give a picture of what is getting locked.
v$session还有 3 列blocking_instance, blocking_session, blocking_session_status可以添加到上面的查询中，以显示被锁定的内容。
回答by diederikh
Use this query to determine possible blocking locks:
使用此查询来确定可能的阻塞锁：
SELECT se.username,
NULL,
se.sid,
DECODE( se.command,
0, 'No command',
1, 'CREATE TABLE',
2, 'INSERT',
3, 'SELECT',
4, 'CREATE CLUSTER',
5, 'ALTER CLUSTER',
6, 'UPDATE',
7, 'DELETE',
8, 'DROP CLUSTER',
9, 'CREATE INDEX',
10, 'DROP INDEX',
11, 'ALTER INDEX',
12, 'DROP TABLE',
13, 'CREATE SEQUENCE',
14, 'ALTER SEQUENCE',
15, 'ALTER TABLE',
16, 'DROP SEQUENCE',
17, 'GRANT',
18, 'REVOKE',
19, 'CREATE SYNONYM',
20, 'DROP SYNONYM',
21, 'CREATE VIEW',
22, 'DROP VIEW',
23, 'VALIDATE INDEX',
24, 'CREATE PROCEDURE',
25, 'ALTER PROCEDURE',
26, 'LOCK TABLE',
27, 'NO OPERATION',
28, 'RENAME',
29, 'COMMENT',
30, 'AUDIT',
31, 'NOAUDIT',
32, 'CREATE DATABASE LINK',
33, 'DROP DATABASE LINK',
34, 'CREATE DATABASE',
35, 'ALTER DATABASE',
36, 'CREATE ROLLBACK SEGMENT',
37, 'ALTER ROLLBACK SEGMENT',
38, 'DROP ROLLBACK SEGMENT',
39, 'CREATE TABLESPACE',
40, 'ALTER TABLESPACE',
41, 'DROP TABLESPACE',
42, 'ALTER SESSION',
43, 'ALTER USER',
44, 'COMMIT',
45, 'ROLLBACK',
46, 'SAVEPOINT',
47, 'PL/SQL EXECUTE',
48, 'SET TRANSACTION',
49, 'ALTER SYSTEM SWITCH LOG',
50, 'EXPLAIN',
51, 'CREATE USER',
52, 'CREATE ROLE',
53, 'DROP USER',
54, 'DROP ROLE',
55, 'SET ROLE',
56, 'CREATE SCHEMA',
57, 'CREATE CONTROL FILE',
58, 'ALTER TRACING',
59, 'CREATE TRIGGER',
60, 'ALTER TRIGGER',
61, 'DROP TRIGGER',
62, 'ANALYZE TABLE',
63, 'ANALYZE INDEX',
64, 'ANALYZE CLUSTER',
65, 'CREATE PROFILE',
67, 'DROP PROFILE',
68, 'ALTER PROFILE',
69, 'DROP PROCEDURE',
70, 'ALTER RESOURCE COST',
71, 'CREATE SNAPSHOT LOG',
72, 'ALTER SNAPSHOT LOG',
73, 'DROP SNAPSHOT LOG',
74, 'CREATE SNAPSHOT',
75, 'ALTER SNAPSHOT',
76, 'DROP SNAPSHOT',
79, 'ALTER ROLE',
85, 'TRUNCATE TABLE',
86, 'TRUNCATE CLUSTER',
88, 'ALTER VIEW',
91, 'CREATE FUNCTION',
92, 'ALTER FUNCTION',
93, 'DROP FUNCTION',
94, 'CREATE PACKAGE',
95, 'ALTER PACKAGE',
96, 'DROP PACKAGE',
97, 'CREATE PACKAGE BODY',
98, 'ALTER PACKAGE BODY',
99, 'DROP PACKAGE BODY',
TO_CHAR(se.command) ) command,
DECODE(lo.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'JQ', 'Job Queue',
lo.type) ltype,
DECODE( lo.lmode,
0, 'NONE', /* Mon Lock equivalent */
1, 'Null Mode', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share (S)', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Excl (X)', /* X */
lo.lmode) lmode,
DECODE( lo.request,
0, 'NONE', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share (S)', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Excl (X)', /* X */
TO_CHAR(lo.request)) request,
lo.ctime ctime,
DECODE(lo.block,
0, 'No Block',
1, 'Blocking',
2, 'Global',
TO_CHAR(lo.block)) blkothr,
'SYS' owner,
ro.name image
FROM v$lock lo,
v$session se,
v$transaction tr,
v$rollname ro
WHERE se.sid = lo.sid
AND se.taddr = tr.addr(+)
AND tr.xidusn = ro.usn(+)
ORDER BY sid
回答by Padmarag
回答by BurnsBA
Ok, this was a silly problem.
好吧，这是一个愚蠢的问题。
We are using Entity Framework 6.0 (upgraded to 6.2, but no change), Oracle.ManagedDataAccess +EntityFramework 12.2.1100, .NET 4.5.
我们正在使用 Entity Framework 6.0（升级到 6.2，但没有变化）、Oracle.ManagedDataAccess +EntityFramework 12.2.1100、.NET 4.5。
I was getting ORA-02049: timeout: distributed transaction waiting for lockwith the following query:
我收到ORA-02049: timeout: distributed transaction waiting for lock以下查询：
update "schemaname"."tablename"
set "DUE_DATE" = :p0
where ("ID" = :p1)
(via EF context.Database.Log event). A really simple query, shouldn't have any issues.
（通过 EF context.Database.Log 事件）。一个非常简单的查询，应该没有任何问题。
Well, I was using the same login on the remote server, on my local debugger, and in Oracle SQL Developer. A co-worker pointed out I should kill all these multiple connections while debugging .... and it worked. So the solution in my case was not to connect to the database multiple times with the same login.
嗯，我在远程服务器、本地调试器和 Oracle SQL Developer 上使用了相同的登录名。一位同事指出我应该在调试时终止所有这些多个连接......并且它起作用了。因此，在我的情况下，解决方案不是使用相同的登录名多次连接到数据库。
