这时再次检查锁信息:
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 1247 TABLE IX 0 1026 0x0000000000000000
2 1249 TABLE IX 0 1026 0x0000000000000000
3 1249 ROW X 0 1026 0x000000000033FD32
4 1247 ROW X 0 1026 0x000000000033FD31
4 rows got
time used: 0.435(ms) clock tick:717070.
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 1247 TABLE IX 0 1026 0x0000000000000000
2 1249 TABLE IX 0 1026 0x0000000000000000
3 1249 ROW X 0 1026 0x000000000033FD32
4 1247 ROW X 0 1026 0x000000000033FD31
4 rows got
time used: 0.435(ms) clock tick:717070.
现在可以清晰的看到,两个并发的事务分别锁定同一张表的两条不同的记录。这说明达梦中实现了行级锁定。虽然这个动态视图提供的信息如此直观和清晰,甚至在Oracle中我们都不知道具体锁定了哪条记录,但是这也暴露了一个问题。就是在达梦数据库中锁是一种资源,数据库需要记录每条锁定的记录。当数据量过大的时候,为了避免消耗更多的资源,达梦数据库会采用锁升级的策略:
SQL>CREATE TABLE T_RECORD
2 (ID NUMBER);
CREATE TABLE T_RECORD
(ID NUMBER);
time used: 11.990(ms) clock tick:19871840.
SQL>INSERT INTO T_RECORD
2 SELECT ROWNUM FROM SYSTEM.SYSDBA.DUAL
3 CONNECT BY ROWNUM < 1000000;
INSERT INTO T_RECORD
SELECT ROWNUM FROM SYSTEM.SYSDBA.DUAL
CONNECT BY ROWNUM < 1000000;
1000000 rows affected
time used: 75496.092(ms) clock tick:1644487636.
SQL>COMMIT;
COMMIT;
time used: 41.571(ms) clock tick:69449680.
SQL>SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
1 1
1 rows got
time used: 0.395(ms) clock tick:651870.
SQL>UPDATE T_RECORD
2 SET ID = ID + 1;
UPDATE T_RECORD
SET ID = ID + 1;
1000000 rows affected
time used: 15544.108(ms) clock tick:186236054.
SQL>SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
1 803
1 rows got
time used: 2.850(ms) clock tick:4754460.
SQL>SELECT LTYPE, LMODE, COUNT(*)
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT'
4 GROUP BY LTYPE, LMODE;
SELECT LTYPE, LMODE, COUNT(*)
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT'
GROUP BY LTYPE, LMODE;
LTYPE LMODE
1 TABLE IX 1
2 TABLE X 1
3 ROW X 800
3 rows got
time used: 3.446(ms) clock tick:5750600.
2 (ID NUMBER);
CREATE TABLE T_RECORD
(ID NUMBER);
time used: 11.990(ms) clock tick:19871840.
SQL>INSERT INTO T_RECORD
2 SELECT ROWNUM FROM SYSTEM.SYSDBA.DUAL
3 CONNECT BY ROWNUM < 1000000;
INSERT INTO T_RECORD
SELECT ROWNUM FROM SYSTEM.SYSDBA.DUAL
CONNECT BY ROWNUM < 1000000;
1000000 rows affected
time used: 75496.092(ms) clock tick:1644487636.
SQL>COMMIT;
COMMIT;
time used: 41.571(ms) clock tick:69449680.
SQL>SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
1 1
1 rows got
time used: 0.395(ms) clock tick:651870.
SQL>UPDATE T_RECORD
2 SET ID = ID + 1;
UPDATE T_RECORD
SET ID = ID + 1;
1000000 rows affected
time used: 15544.108(ms) clock tick:186236054.
SQL>SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
SELECT COUNT(*) FROM SYSTEM.SYSDBA.V$LOCK;
1 803
1 rows got
time used: 2.850(ms) clock tick:4754460.
SQL>SELECT LTYPE, LMODE, COUNT(*)
2 FROM SYSTEM.SYSDBA.V$LOCK
3 WHERE LTYPE != 'DICT'
4 GROUP BY LTYPE, LMODE;
SELECT LTYPE, LMODE, COUNT(*)
FROM SYSTEM.SYSDBA.V$LOCK
WHERE LTYPE != 'DICT'
GROUP BY LTYPE, LMODE;
LTYPE LMODE
1 TABLE IX 1
2 TABLE X 1
3 ROW X 800
3 rows got
time used: 3.446(ms) clock tick:5750600.
可以看到,在获取了800个行级锁后,数据库自动将行级锁升级为表级锁。
在Oracle中锁并不是一种昂贵的资源,因此不会出现锁升级的情况。在达梦中,由于锁是一种资源,因此为了避免大量的持有锁,达梦采用了升级锁的方法,虽然这会在一定情况下影响并发性,但是这是资源权衡的结果,SQLSERVER等数据库也是这样实现的。
不过达梦数据库中,似乎读并不阻塞写,尝试建立一个100W记录的表,在一个会话先开始一个SELECT * FROM TABLE的语法,然后在另一个会话执行一个UPDATE全表的语句。由于SELECT需要将结果打印到屏幕,因此SELECT语句在UPDATE之后完成,这显然说明SELECT并没有阻塞UPDATE。而且可以肯定SELECT是发生在UPDATE之前,因为再次执行同样的SELECT语句就会被UPDATE更新操作锁住。
总的来说,达梦数据库的锁机制虽然不能和Oracle的比,但是也已经不错了,可以实现行级锁定,而且读并不阻塞写。而且达梦数据库还能实现多版本一致性读,不过要实现这种方式需要修改初始化参数配置中的默认设置,而且可能会对性能造成一定的影响。