窗口1:插入1065不提交
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create table employee1(id int not null primary key,name varchar(10))"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +c "insert into employee1 values(1065,'liys')"
DB20000I The SQL command completed successfully.
窗口2:插入1066会成功,但是插入1065会锁等
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "insert into employee1 values(1066,'zhang3')"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "insert into employee1 values(1065,'zhang3')"
窗口3:锁等信息
db2pd -d test -wlocks
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
9907 [000-09907] 3 02000101040000000000000052 RowLock ..X G 393 db2bp DB2INST1 *LOCAL.db2inst1.230725050756
9926 [000-09926] 14 02000101040000000000000052 RowLock ..U W 424 db2bp DB2INST1 *LOCAL.db2inst1.230725051255
U 更改锁(Update)某个程序正在读取并有可能修改该行,其他程序只能读取该行
X 排他锁(eXclusive)该行正在被某个程序修改,其他程序不能访问该行
如何理解U锁呢?
下面这个测试中,为什么窗口2事务更新id=2的行也需要id=1行U锁呢?
原因是name没有索引,在select查询时候使用的是全表扫描,U是update的一个临时状态,update
时会在每一行都尝试加U锁,并最后在实际更新符合条件行时变成X行锁,
窗口1:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "select * from employee1"
ID NAME
----------- ----------
1 liys4
2 liys66
2 record(s) selected.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "update employee1 set id=11 where name='liys4'"
DB20000I The SQL command completed successfully
窗口2:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "update employee1 set id=22 where name='liys66'"
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test -wlocks
Database Member 0 -- Database TEST -- Active -- Up 0 days 01:15:06 -- Date 2023-07-25-14.23.02.229267
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
9907 [000-09907] 3 02000101040000000000000052 RowLock ..X G 393 db2bp DB2INST1 *LOCAL.db2inst1.230725050756
9926 [000-09926] 14 02000101040000000000000052 RowLock ..U W 424 db2bp DB2INST1 *LOCAL.db2inst1.230725051255
如果走索引的话就不会出现这个情况
窗口1;
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "update employee1 set name='liys01' where id=1"
DB20000I The SQL command completed successfully.
窗口2:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "update employee1 set name='liys02' where id=2"
DB20000I The SQL command completed successfully.
窗口3:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test -wlocks
Database Member 0 -- Database TEST -- Active -- Up 0 days 01:27:06 -- Date 2023-07-25-14.35.02.286080
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test -locks
Database Member 0 -- Database TEST -- Active -- Up 0 days 01:27:13 -- Date 2023-07-25-14.35.09.917419
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007F007CD52E80 14 02000101050000000000000052 RowLock ..X G 14 1 0 0x00200000 0x40000000 0
0x00007F007CD44D00 3 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007F007CD42D00 14 4141414141664164FE8BC716C1 PlanLock ..S G 14 1 0 0x00000000 0x40000000 0
0x00007F007CD44E80 3 02000101040000000000000052 RowLock ..X G 3 1 0 0x00200000 0x40000000 0
0x00007F007CD42C80 3 02000101000000000000000054 TableLock .IX G 3 1 0 0x00202000 0x40000000 0
0x00007F007CD53100 14 02000101000000000000000054 TableLock .IX G 14 1 0 0x00203000 0x40000000 0
再进一步理解U锁存在的意义,可以看到其实U锁是一个中间状态,是和S锁兼容的的,是不和X锁兼容的。..U ..X C 表示状态是Convert转变状态,要转变成X锁。
那为什么不直接使用S锁或者X锁呢,使用S锁的话就会有可能造成两个应用都拿着S锁,但是都在申请X锁而等待对方释放S锁,造成死锁。如果是X锁呢,就会影响读了,每一行都申请X锁读完再释放,那其他应用读就会申请S锁,验证影响并发度
窗口1:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "select * from employee1 where name='liys4' with rs"
ID NAME
----------- ----------
1 liys4
1 record(s) selected.
窗口2:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 +C "update employee1 set id=10 where name='liys4'"
窗口3:
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test -wlocks
Database Member 0 -- Database TEST -- Active -- Up 0 days 01:46:37 -- Date 2023-07-25-14.54.33.214208
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
9907 [000-09907] 3 02000101040000000000000052 RowLock .NS G 393 db2bp DB2INST1 *LOCAL.db2inst1.230725050756
9926 [000-09926] 14 02000101040000000000000052 RowLock ..U ..X C 424 db2bp DB2INST1 *LOCAL.db2inst1.230725051255