晚上在lock争用小实验中出现疑惑,求安慰。。。

这是今天晚上我做的一个关于lock争用的一个实验,试验的方法大致如下,我开启3个sqlplus客户端,其中2个用scott用户登录,1个用sys用

户登录。
实验步骤大概如下:
1.用sys用户来确定2个scott用户的sid(查视图v$session);
2.用sid为159的scott用户,修改emp表中empno为7900的员工的工资(不提交);
3.以sys用户检查v$lock,查看锁的情况
4.用sid为137的scott用户,修改emp表中的empno为7902的员工的工资(不提交);
5.再次以sys来查看v$lock;
6.以sid为137的scott用户,修改emp表中empno为7900的员工的工资(出现lock争用);
7.以sys来查看v$lock和v$session查看锁和等待的情况
8.sid为159的scott用户commit,从而解除争用的情况;
9.sys用户查看v$lock,发现159和137对于emp 7900这一行的锁都消失了。

随着第一个scott用户(sid=159的提交),事务锁争用的问题消失了,但是出现了这个实验中我疑惑的一个地方,
从上面的id2可以看出,现在剩下的锁,只有第二个scott用户(sid=137)在修改empno=7902时,所产生的行级锁,和表级锁了。
那么为什么随着sid=159的提交,sid=137所发出的那条修改emp=7900员工的工资的行级锁也跟着消失了呢??

忘大家能够帮助我解惑,以下附上详细的实验步骤:

开启3个sqlplus,可以看到3个session,记录一下scott用户的2个会话的sid

SQL> select sid,username from v$session where username IN ('SCOTT','SYS');

       SID USERNAME
---------- ------------------------------
       137 SCOTT
       157 SYS
       159 SCOTT




试验前查看一下当前scott.emp表中empno为7900和7902的用户工资情况

SQL> select empno,ename,sal from emp where empno in(7900,7902);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             977
      7902 FORD             3026


开始试验,目前有3个用户的session链接同时存在,分别是scott的2个,和sys的1个
scott的2个链接的sid分别是137和159



第一步:
scott用户sid为159的终端,给empno=7900涨一下工资,但是先别提交

update emp set sal=sal+1 where empno=7900;



第二步:
sys用户的终端,查一下scott用户的lock状态,可以看到占用了一把表级锁,一把行级锁

SQL> select * from v$lock where sid IN (select sid from v$session where username='SCOTT');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
391C3F90 391C3FA8        159 TM      51148          0          3          0          6          0
392023DC 392024F8        159 TX     589841        545          6          0          6          0



第三步:
scott用户sid为137的终端,给empno=7902的涨一下工资,同样也不要提交

update emp set sal=sal+1 where empno=7902;




第四步:
sys用户的终端,查一下scott用户的lock状态,可以看到目前一共占用了4把锁,2把表级锁,2把行级锁,其中2把行级锁
是排它的(独占),2把表级锁锁住的是同一个表(通过ID1字段的值可以看出)

SQL> select * from v$lock where sid IN (select sid from v$session where username='SCOTT');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
391C3F90 391C3FA8        159 TM      51148          0          3          0       3371          0
391C403C 391C4054        137 TM      51148          0          3          0          7          0
392023DC 392024F8        159 TX     589841        545          6          0       3371          0
3922B6A8 3922B7C4        137 TX     131091        572          6          0          7          0



第五步:
scott用户sid为137的终端,给empno=7900的涨一下工资,这时候会出现等待事件,等待sid为159的scott用户释放行级锁(锁住了empno=7900

的行目前),此时sid为137的客户端不会显示 “1 row updated.”的提示,而是会一直等待。

update emp set sal=sal+1 where empno=7900;



第六步:
sys用户的终端,查看一下lock的状态,会发现目前一共有5把锁,2把表级锁,3把行级锁

SQL> select * from v$lock where sid IN (select sid from v$session where username='SCOTT');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3A7B9D88 3A7B9D9C        137 TX     589841        545          0          6        159          0
391C3F90 391C3FA8        159 TM      51148          0          3          0       3821          0
391C403C 391C4054        137 TM      51148          0          3          0        457          0
392023DC 392024F8        159 TX     589841        545          6          0       3821          1
3922B6A8 3922B7C4        137 TX     131091        572          6          0        457          0


进一步检查视图v$session可以发现,sid为137的用户,现在出现了lockwait,锁等待,等待事件为:行级锁(事务锁)争用,
这是因为最先修改empno=7900员工工资的sid 159 没有提交或回滚,一直占用着这个数据行
SQL> select sid,username,TERMINAL,type,lockwait,event from v$session where username='SCOTT';

       SID USERNAME         TERMINAL         TYPE       LOCKWAIT      EVENT
---------- ------------------------------ ---------- ---------------------------------------------------
       137 SCOTT            pts/6             USER       3A7B9D9C     enq: TX - row lock contention
       159 SCOTT            pts/3             USER                    SQL*Net message from client



第七步:
解除争用
sid=159的scott用户commit;

之后用sys用户检查v$lock视图,看看commit后的锁情况

SQL> select * from v$lock where sid IN (select sid from v$session where username='SCOTT');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
391C403C 391C4054        137 TM      51148          0          3          0       1912          0
3922B6A8 3922B7C4        137 TX     131091        572          6          0       1912          0

如上表所示,随着第一个scott用户(sid=159的提交),事务锁争用的问题消失了,但是出现了这个实验中我疑惑的一个地方,
从上面的id2可以看出,现在剩下的锁,只有第二个scott用户(sid=137)在修改empno=7902时,所产生的行级锁,和表级锁了。
那么为什么随着sid=159的提交,sid=137所发出的那条修改emp=7900员工的工资的行级锁也跟着消失了呢??



标签: 暂无标签
shihaidong0605

写了 2 篇文章,拥有财富 23,被 1 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

游客 | 发表于 2011-3-29 00:02:39
要弄清楚一点TX锁并非是只对一行加锁,可以同时对一个表的多行加同一个TX锁。
回复

使用道具

P6 | 发表于 2011-3-27 21:03:58
本帖最后由 oraunix 于 2011-3-28 13:25 编辑

实验做得很好,说明在分析问题,但是对知识点的分析上还有一点欠缺,相信你能成功。
主要原因是你没有搞清楚一个点:
TX是事务锁,不是行级锁。
在没有等待的情况下,一个事务只有一个锁。
如果出现等待,事务锁会出现排队的情况,因此查询的时候会发现多个。
好好看看我的课件。
回复

使用道具

P3 | 发表于 2011-3-26 20:15:04
select SESSION_ID,LOCK_TYPE,MODE_HELD,MODE_REQUESTED,LOCK_ID1,LOCK_ID2 from dba_locks where SESSION_ID in (137,159);
看看对你分析问题有没有帮助!

MODE_HELD       MODE_REQUESTED
--------------- --------------
None            Exclusive
None            Exclusive
Row-X (SX)      None
Row-X (SX)      None
Row-X (SX)      None
Exclusive       None
Exclusive       None
lmode=0是正在请求的状态!是队列中的会话!
回复

使用道具

hzs
P4 | 发表于 2011-3-25 20:34:01
看看有没有锁等待
回复

使用道具

hzs
P4 | 发表于 2011-3-25 20:33:04
看了两边  但是还是没想明白为什么会少一个行级锁和一个表级锁

你没试试用159 再次修改 7900 看看锁是什么效果
回复

使用道具

P3 | 发表于 2011-3-24 23:01:10
在群里发,也没人来看看,先顶后看。
回复

使用道具

P3 | 发表于 2011-3-24 22:36:48
自己坐个沙发先
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈