一个TX锁

gtlions

如题，整理了相关日志文件在附件，帮忙分析下，谢谢！

Maclean Liu(刘相兵

ODM DATA:
发生多次 Global Enqueue Services Deadlock detected:

Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/product/admin/mydb/bdump/mydb1_lmd0_14857.trc.
Sun May  6 15:30:10 2012
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/product/admin/mydb/bdump/mydb1_lmd0_14857.trc.
Sun May  6 15:31:12 2012
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/product/admin/mydb/bdump/mydb1_lmd0_14857.trc.


触发 dead lock的 SQL语句一直是 " update mytab1 set USER_ID=:1, PHONENUMBER=:2, LAST_MODIFY_TIME=:3 where ID=:4"    pid=24 serial=9920



user session for deadlock lock 0xc00000023c8acf30
  pid=24 serial=9920 audsid=22864739 user: 59/RBT
  O/S info: user: root, term: unknown, ospid: 1234, machine: rbt103
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  update mytab1 set USER_ID=:1, PHONENUMBER=:2, LAST_MODIFY_TIME=:3 where ID=:4


resname       : [0x1a0028][0x55720],[TX]

[0x1a0028][0x55720] 是 TX lock的ID1 和 ID2

DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[24.25760] on resource TX-001A0028-00055720

ID1  =Decimal RBS & slot   ( (0xRRRRSSSS  RRRR = RBS number, SSSS = slot ))
ID2   = Decimal WRAP number

Global Wait-For-Graph(WFG) at ddTS[0.1754] :
BLOCKED c00000023c8acf30 5 wq 2 cvtops x1 [0x140027][0xe6e0b],[TX] [1001-0018-000009CD] 0
BLOCKER c00000023c577c00 5 wq 1 cvtops x8 [0x140027][0xe6e0b],[TX] [2003-003D-0000200E] 1
BLOCKED c00000023b0d5500 5 wq 2 cvtops x1 [0x22002a][0x1d185],[TX] [2003-003D-0000200E] 1
BLOCKER c00000023c4b73d8 5 wq 1 cvtops x8 [0x22002a][0x1d185],[TX] [1001-0018-000009CD] 0

因为是 RAC deadlock 所以 看不到 其他block session的SQL， 建议你通过ASH 信息确定 当时 block   pid=24 serial=9920的回话 及其SQL，  同时确认mytab1  没有bitmap index 或者 其他可能导致死锁的约束。

gtlions

mytab1有个外键，应用表mytab2，在mytab1和mytab2上都在外键user_id上创建了索引，user_id为mytab2的pk