mysql insert 阻塞_insert遭遇阻塞

insert的阻塞确实不常见，今天碰到了一个，看书又了解一个，整理下。

1、多个会话同时向unique字段插入相同的值

session1：

首先建测试表test，并在字段id上创建一个主键索引(唯一键也可以)。

SQL> create table test(id number,name char(1));

Table created.

SQL> insert into test values(1,'a');

1 row created.

SQL> rollback;

Rollback complete.

SQL> alter table test add constraint pk_test_id primary key(id);

Table altered.

SQL> insert into test values(1,'a');

1 row created.

SQL>

session2：

SQL> insert into test values(1,'b');

session2的insert被阻塞。

如果应用确实需要生成主键或唯一键，可以采用序列生成。

2、主外键关系的表可能会相互阻塞

将1中创建的test表作为父表，再创建一个子表，如test_sun。

session1：

SQL> create table test_sun(id number,name char(1));

Table created.

SQL> alter table test_sun add constraint fk_test_sun_id foreign key(id) references test(id);

Table altered.

SQL> delete from test where id=1;

1 row deleted.

SQL>

session2：

SQL> insert into test_sun values(1,'c');

session2 被阻塞。

测试发现，当在一个会话中对子表插入一行(2,'d')，也会阻塞对父表delete from test where id=1;

查锁等待为：

SQL> select event,blocking_session,row_wait_obj#,sid,serial# from v$session where username=user and status='ACTIVE';

EVENT BLOCKING_SESSION ROW_WAIT_OBJ# SID SERIAL#

---------------------------------------------------------------- ---------------- ------------- ---------- ----------

enq: TM - contention 38 13892 1 29

SQL> select object_id,object_name from dba_objects where object_id=13892;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

13892 TEST_SUN

SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2

--------------- --------------- -------------------------- ------------------------------- ---------------------------------------- ---------- ----------

1 38 DML Row-X (SX) Share 13892 0

可以看到，父表正被子表test_sun阻塞，子表加的是一个行锁。

但是因为有主外键关系，主表必须保证子表的值都在主表里，所以必须能确定子表的值后才能做出回应，否则就处于等待状态了。

经测试发现：

因为主外表关系，导致主表必须保证子表的值都是主表里。当删除数据时，如果保证不报错，必须先删除子表数据再删除主表数据，插入数据时，必须先插入主表再插入子表。

1、删除时，先删除子表不提交或回滚，然后删除主表，会发现删除主表的会话处于等待。

2、插入时，先插入主表不提交或回滚，然后插入子表，会发现插入子表的会话处于等待。