建立了一个测试表之后,下面看看达梦是否采用行级锁来锁定记录:
SQL>UPDATE T
2 SET AGE = 20
3 WHERE ID = 1;
UPDATE T
SET AGE = 20
WHERE ID = 1;
1 rows affected
time used: 0.554(ms) clock tick:917730.
2 SET AGE = 20
3 WHERE ID = 1;
UPDATE T
SET AGE = 20
WHERE ID = 1;
1 rows affected
time used: 0.554(ms) clock tick:917730.
需要注意,在进行这个测试的时候,需要确保AUTOCOMMIT是关闭的。
在另外的会话中更新ID为2的记录,这时会话被阻塞:
SQL>UPDATE T
2 SET AGE = 30
3 WHERE ID = 2;
2 SET AGE = 30
3 WHERE ID = 2;
在第一个会话中检查系统的锁状态:
SQL>SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT';
SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT';
TRX_ID LTYPE LMODE BLOCKED TABLE_ID ROW_ID
1 1245 TABLE IX 0 1026 0x0000000000000000
2 1246 TABLE IX 0 1026 0x0000000000000000
3 1245 ROW X 0 1026 0x000000000033FD31
4 1246 ROW S 1 1026 0x000000000033FD31
4 rows got
time used: 22.091(ms) clock tick:36925520.
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT';
SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT';
TRX_ID LTYPE LMODE BLOCKED TABLE_ID ROW_ID
1 1245 TABLE IX 0 1026 0x0000000000000000
2 1246 TABLE IX 0 1026 0x0000000000000000
3 1245 ROW X 0 1026 0x000000000033FD31
4 1246 ROW S 1 1026 0x000000000033FD31
4 rows got
time used: 22.091(ms) clock tick:36925520.
可以看到,事务ID为1246并不是由于要获取独占锁而被阻塞,而是在查询到被修改的记录时被锁定。
也就是说,在达梦数据库中,写是阻塞读的。
如果要避免这种情况的产生,可以建立ID列上的索引。
首先,会话1提交是否锁:
SQL>COMMIT;
COMMIT;
time used: 50.458(ms) clock tick:84356900.
COMMIT;
time used: 50.458(ms) clock tick:84356900.
这时会话2上UPDATE操作成功:
1 rows affected
time used: 6652041.104(ms) clock tick:3358507298.
time used: 6652041.104(ms) clock tick:3358507298.
下面再次检查当前的锁信息:
SQL>SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT';
SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT';
TRX_ID LTYPE LMODE BLOCKED TABLE_ID ROW_ID
1 1246 TABLE IX 0 1026 0x0000000000000000
2 1246 ROW X 0 1026 0x000000000033FD32
2 rows got
time used: 0.405(ms) clock tick:668360.
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT';
SELECT TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, ROW_ID
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT';
TRX_ID LTYPE LMODE BLOCKED TABLE_ID ROW_ID
1 1246 TABLE IX 0 1026 0x0000000000000000
2 1246 ROW X 0 1026 0x000000000033FD32
2 rows got
time used: 0.405(ms) clock tick:668360.
刚才被阻塞的事务1246现在获得了独占锁,可以放心当前锁定的行并不是1245锁定的记录。看来达梦中实现的确实是行级锁,不过达梦并没有解决读写相互锁定的问题。为了避免刚才的现象,在ID列增加索引:
SQL>COMMIT;
COMMIT;
time used: 11.548(ms) clock tick:19298280.
SQL>CREATE INDEX IND_T_ID
2 ON T(ID);
CREATE INDEX IND_T_ID
ON T(ID);
time used: 36.302(ms) clock tick:60422530.
COMMIT;
time used: 11.548(ms) clock tick:19298280.
SQL>CREATE INDEX IND_T_ID
2 ON T(ID);
CREATE INDEX IND_T_ID
ON T(ID);
time used: 36.302(ms) clock tick:60422530.
下面再次执行刚才的操作:
SQL>UPDATE T
2 SET AGE = 30
3 WHERE ID = 1;
UPDATE T
SET AGE = 30
WHERE ID = 1;
1 rows affected
time used: 38.401(ms) clock tick:64196100.
2 SET AGE = 30
3 WHERE ID = 1;
UPDATE T
SET AGE = 30
WHERE ID = 1;
1 rows affected
time used: 38.401(ms) clock tick:64196100.
会话一的UPDATE成功。
SQL>UPDATE T
2 SET AGE = 40
3 WHERE ID = 2;
UPDATE T
SET AGE = 40
WHERE ID = 2;
1 rows affected
time used: 0.467(ms) clock tick:772190.
2 SET AGE = 40
3 WHERE ID = 2;
UPDATE T
SET AGE = 40
WHERE ID = 2;
1 rows affected
time used: 0.467(ms) clock tick:772190.
而会话二执行UPDATE也成功了。