分析大量enq-TX锁
1. 发现较多enq-TX锁
120，126，127都在等待60233对象，查60233对象
SQL> select object_name,owner,object_id from dba_objects where object_id = 60233;
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER OBJECT_ID
------------------------------ ----------
AGT202T1
ASIA 60233
发现update agt202t0表，需要锁agt202t1表。
看agt202t1建表语句：
1* select dbms_metadata.get_ddl('TABLE','AGT202T1','ASIA') from dual
SQL> /
DBMS_METADATA.GET_DDL('TABLE','AGT202T1','ASIA')
--------------------------------------------------------------------------------
CREATE TABLE "ASIA"."AGT202T1"
( "BILL_ID" VARCHAR2(18) NOT NULL ENABLE,
"SEQ" NUMBER,
"IO_KIND" VARCHAR2(3),
"PRODUCT_ID" VARCHAR2(20),
"QTY_I" NUMBER(8,0),
"QTY_F" NUMBER(5,0),
"PRICE" NUMBER(7,4),
"SALE_PRICE" NUMBER(7,4),
"AMT" NUMBER(13,2),
DBMS_METADATA.GET_DDL('TABLE','AGT202T1','ASIA')
--------------------------------------------------------------------------------
PRIMARY KEY ("SEQ", "PRODUCT_ID", "BILL_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PESD" ENABLE,
FOREIGN KEY ("BILL_ID")
REFERENCES "ASIA"."AGT202T0" ("BILL_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PESD"
发现： agt202t1引用agt202t0表的bill_id做为外键。即 agt202t1是子表，agt202t0是父表。
当update agt202t0记录时，锁了agt202t1表。
查asktom
un‐indexed foreign keys were the biggest single cause
of deadlocks that I encounter, due to the fact that an update to a parent table’s primary key or the removal of a parent record will place a full table lock on the child table (no modifications to the child table will be allowed until the transaction commits). This locks many more rows then it should, and decreases concurrency.
查有没有外键索引。
1* select index_name,column_name,column_position,table_name from dba_ind_columns where table_name = 'AGT202T1'
SQL> /
INDEX_NAME COLUMN_NAME COLUMN_POSITION TABLE_NAME
-------------------- -------------------- --------------- --------------------
SYS_C0011596 PRODUCT_ID 2 AGT202T1
SYS_C0011596 BILL_ID 3 AGT202T1
SYS_C0011596 SEQ 1 AGT202T1
有索引，但是外键bill_id不是第一个字段。
查142sql:
UPDATE agt202t0
SET branch_no=:1 ,
yyyymmdd=:2 ,
cust_id=:3 ,
sale_id=:4 ,
receive_addr=:5 ,
car_id=:6 ,
amt_bal=:7 ,
amt_dis=:8 ,
vouch_no=:9 ,
bill_id=:10 ,
source_bill=:11 ,
memo=:12 ,
amt_t=:13 ,
amt_y=:14 ,
ins_user=:15 ,
create_time=:16 ,
state=:17
WHERE rowid=:18
结论：
因为在子表上没有外键的索引（以外键字段昨晚第一个字段），导致对主表的修改都引起对子表的全表锁定。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17172228/viewspace-1063824/ ，如需转载，请注明出处，否则将追究法律责任。
