DM数据库锁知识分享

1.DM事务锁

1.创建表

create table t_test01(id int primary key, name varchar(20));

2.查询这个表，会生成IS（意向共享锁）

select * from t_test01;

3.insert表，会生成IX（意向排他锁）

insert into t_test01(id, name) values(1, 'cheng');

查询锁信息,

IS（意向共享锁）和IX（意向排他锁）

1.先查询表对应的ID号

select name,id from sysobjects where name='T_TEST01';

2.根据ID号，查询v$lock;

select TRX_ID,LTYPE,LMODE,BLOCKED,TABLE_ID,TID FROM v$lock where table_id='2607';

执行过程输出如下：

SQL> select name,id from sysobjects where name='T_TEST01';

LINEID NAME ID

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

1 T_TEST01 2607

used time: 10.913(ms). Execute id is 1603500.

SQL> select TRX_ID,LTYPE,LMODE,BLOCKED,TABLE_ID,TID FROM v$lock where table_id='2607';

LINEID TRX_ID LTYPE LMODE BLOCKED TABLE_ID TID

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

1 1186554 OBJECT IS 0 2607 1186554

used time: 0.846(ms). Execute id is 1603503.

SQL> /

LINEID TRX_ID LTYPE LMODE BLOCKED TABLE_ID TID

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

1 1186554 OBJECT IS 0 2607 1186554

2 1186554 OBJECT IX 0 2607 1186554

used time: 0.643(ms). Execute id is 1603504.

2.锁等待测试

2.1 insert 锁等测试

当多个事务同时试图向有主键或UNIQUE约束的表中插入相同的数据时，前一个事务未提交，后面的事务将被阻塞，直到前一个事务提交或回滚。

测试过程

1.会话1，插入数据，不提交

insert into t_test01(id, name) values(2, 'liu');

2.会话2，插入同样的数据，会被阻塞

insert into t_test01(id, name) values(2, 'liu');

3.查看锁的信息

LINEID TRX_ID LTYPE LMODE BLOCKED TABLE_ID TID

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

1 1187705 OBJECT IS 0 2607 1187705

2 1187705 OBJECT IX 0 2607 1187705

3 1187716 OBJECT IX 0 2607 1187716

4 1187716 TID X 1 2607 1187705

used time: 0.681(ms). Execute id is 1603513.

4.查询v$trxwait

SQL> select * from v$trxwait;

LINEID ID WAIT_FOR_ID WAIT_TIME THRD_ID

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

1 1187716 1188049 63965 29121

如果会话1和会话2，insert不同的主键字段会生成什么锁。

2.2 update和delete效果

自行实验，跟insert基本一致

3.锁等待的解决方法

1.查询处于等待的锁

select TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, TID from v$lock where blocked=1;

2.根据trx_id查询sess_id号

select sess_id,sql_text,trx_id,thrd_id from v$sessions where trx_id='1187716';

3.根据session_id杀进程

sp_close_session(140387456909800);

4.再次查询锁信息

select TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, TID from v$lock where blocked=1;

执行过程输出如下:

SQL>

select TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, TID

from v$lock where blocked=1;SQL> 2

LINEID TRX_ID LTYPE LMODE BLOCKED TABLE_ID TID

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

1 1187716 TID X 1 2607 1188049

used time: 26.253(ms). Execute id is 1603523.

SQL> select sess_id,sql_text,trx_id,thrd_id from v$sessions where trx_id='1187716';

LINEID SESS_ID SQL_TEXT TRX_ID THRD_ID

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

1 140387456909800 insert into t_test01(id, name) values(3, 'cheng'); 1187716 30364

used time: 290.158(ms). Execute id is 1603524.

SQL> sp_close_session(140387456909800);

DMSQL executed successfully

used time: 65.781(ms). Execute id is 1603525.

SQL> select TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, TID from v$lock where blocked=1;

no rows

used time: 0.620(ms). Execute id is 1603528.

4.死锁处理方式

4.1 什么是死锁

死锁包括两个或者多个已阻塞事务，它们之间形成了等待环，每个都等待其他事务释放锁。例如事务1给表T1上了排他锁，第二个事务给表T2上了排他锁，此时事务1请求T2的排他锁，就会处于等待状态，被阻塞。若此时T2再请求表T1的排他锁，则T2也处于阻塞状态。

4.2死锁场景模拟

4.2.1 会话一

创建t1表，插入数据

create table t1(id int primary key,name varchar(20));

insert into t1(id,name) values(1,'t1');

4.2.2 会话二

创建t2表，插入数据

create table t2(id int primary key,name varchar(20));

insert into t2(id,name) values(1,'t1');

4.2.4 会话一insert t2数据

会话一t2表插入相同数据，因为有主键，产生锁等待：

insert into t2(id,name) values(1,'t1');

4.2.5 会话一insert t1数据

会话一t1表插入相同数据，因为有主键，产生锁等待：

insert into t1(id,name) values(1,'t1');

产生死锁，dm数据库自动检测到死锁的产生，并报错。

[-6403]:Deadlock.

4.2.6 杀进程

新开打会话

SQL> select * from v$trxwait;

LINEID ID WAIT_FOR_ID WAIT_TIME THRD_ID

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

1 1318466 1318491 325755 31473

used time: 0.353(ms). Execute id is 1862503.

SQL> select sess_id,sql_text,trx_id,thrd_id from v$sessions where trx_id='1318491';

LINEID SESS_ID SQL_TEXT TRX_ID THRD_ID

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

1 140387465432808 insert into t1(id,name) values(1,'t1'); 1318491 31473

used time: 0.401(ms). Execute id is 1862504.

SQL> sp_close_session(140387465432808);

DMSQL executed successfully

used time: 1.544(ms). Execute id is 1862505.

5.DDL锁超时

当我们对某张表执行DDL操作时（比如修改某张表的表结构），若当前表上有排他锁（未提交的DML事务），此时数据库会根据锁的等待时间抛出锁超时的异常。

DM数据库的DDL锁超时时间是由参数DDL_WAIT_TIME指定，默认十秒，可根据实际需要修改。

select * from v$dm_ini t where t.para_name='DDL_WAIT_TIME';

SQL> select * from v$dm_ini t where t.para_name='DDL_WAIT_TIME';

LINEID PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE

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

1 DDL_WAIT_TIME 10 0 604800 10 N 10 10 Maximum waiting time in seconds for DDLs SESSION

used time: 13.837(ms). Execute id is 1862506.

5.1 DDL超时场景测试

5.1.1 会话一

在t1表中插入数据（未提交），产生一个排他锁：

insert into t1(id,name) values(2,'t1');

5.1.2 会话二

执行DDL语句（对t1新增字段），此时会话处理等待状态：

alter table t1 add column info1 varchar(50);

查询v$trxwait表

SQL> select * from v$trxwait;

LINEID ID WAIT_FOR_ID WAIT_TIME THRD_ID

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

1 1319933 1319844 3201 31326

used time: 0.545(ms). Execute id is 1862511.

会话二抛出锁超时异常

SQL> alter table t1 add column info1 varchar(50);

alter table t1 add column info1 varchar(50);

[-6407]:Lock timeout.

used time: 00:00:12.505. Execute id is 0.

6.总结

锁产生本质是发生等待。解决锁等待的问题，就是解决了锁超时的问题。