db2与oracle锁,DB2 主要行级锁模拟 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜...

S行锁和X行锁模拟

--会话1

[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei(id int,name varchar(100))"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 +c "insert into t_xifenfei values(1,'www.xifenfei.com')"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:07:02 -- Date 2012-04-29-01.46.48.462742

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg

0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000

0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000

0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000

--会话2

[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"

hang住

--会话3查询等待

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:08:40 -- Date 2012-04-29-01.48.26.676607

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg

0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000

0x99B3A540 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000

0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000

0x99B40C60 7 02000700040080000000000052 Row .NS W 8 1 0 0x00 0x00000001

0x99B3A420 7 02000000010000000100407056 Internal V ..S G 7 1 0 0x00 0x40000000

0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000

0x99B3A510 7 02000700000000000000000054 Table .IS G 7 1 0 0x00 0x00000001

--这里可以发现Sts=W(STATUS=WAIT),会话的NS锁处于等待状态

--会话1

[db2inst1@xifenfei ~]$ db2 commit

DB20000I The SQL command completed successfully.

--会话2

[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"

ID NAME

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

1 www.xifenfei.com

1 record(s) selected.

--结果出现

--会话3

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:09:06 -- Date 2012-04-29-01.48.52.069878

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg

U锁模拟

--会话1

[db2inst1@xifenfei ~]$ db2 +c "declare c1 cursor for select * from t_xifenfei for update"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 +c open c1

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 +c fetch c1

ID NAME

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

1 WWW.XIFENFEI.COM

1 record(s) selected.

--会话2

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:27 -- Date 2012-04-29-02.07.13.594441

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg

0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000

0x99B3A510 7 02000700040080000000000052 Row ..U G 7 1 0 0x00 0x00000001

0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000

0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 1 0 0x00 0x00000001

--会话1

[db2inst1@xifenfei ~]$ db2 +c "update t_xifenfei set name='www.xifenfei.com'"

DB20000I The SQL command completed successfully.

--会话2

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:44 -- Date 2012-04-29-02.07.30.684616

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg

0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000

0x99B3A510 7 02000700040080000000000052 Row ..X G 7 2 0 0x00 0x40000001

0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000

0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 2 0 0x00 0x40000001

--行级锁由U升级到X

--会话1

[db2inst1@xifenfei ~]$ db2 commit

DB20000I The SQL command completed successfully.

--会话2

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:31:43 -- Date 2012-04-29-02.11.29.167659

Locks:

Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg