Oracle 的for update行锁

Oracle 的for update行锁

　SELECT...FOR UPDATE 语句的语法如下： 

　　SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 

其中： 

　　OF 子句用于指定即将更新的列，即锁定行上的特定列。 

　　WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。 

　　“使用FOR UPDATE WAIT”子句的优点如下： 

　　１防止无限期地等待被锁定的行； 

　　２允许应用程序中对锁的等待时间进行更多的控制。 

　　３对于交互式应用程序非常有用，因为这些用户不能等待不确定 

　　４ 若使用了skip locked，则可以越过锁定的行，不会报告由wait n 引发的‘资源忙’异常报告 

以oracle数据库的scott用户下的表emp和dept为例进行实验说明。

实验一：

会话1：

select * from emp for update;

会话2：

select * from emp for update; 只有当会话1提交或rollback时，才能出结果。

实验二：

会话1：

select * from emp for update;

会话2：

update emp set sal=10000 where empno=7934; 同样也只能等待会话1提交或者rollback.

结论1：select * from table_name for update 锁定表的所有行，只能读不能写。

实验三：

会话1：

select * from emp where empno=7934 for update;

会话2：

select * from emp where empno=7934; 查询没有问题， 再次执行如下语句： update emp set sal=10000 where empno=7934; 可以看到等待状态，在会话一中commit之后，update就可以执行了。

结论2：select * from table_name where 条件 for update 只锁定where限定的行。

实验四：

会话1：

select * from emp a,dept b where a.deptno=b.deptno for update;

会话2：

update emp set sal=10000 where empno=7934;

会话1：

select * from emp a,dept b where a.deptno=b.deptno for update;

会话2：

update dept set deptno=10 where dname='SALES';

结论3：两个表关联查询for update锁定了两个表的所有数据。

实验五：

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update;

会话2：

update emp set empno=7789 where DEPTNO=10;

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update;

会话2：

update dept set dname='beijing' where deptno=10;

结论4：两个表关联查询如果有where条件，则两个表中满足where条件的所有行都被锁定。

实验六：

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update of a.deptno;

会话2：

update dept set deptno=10 where dname='SALES';

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update of a.deptno;

会话2：

update dept set dname='beijing' where deptno=10;

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update of b.deptno;

会话2：

update emp set sal=500 where deptno=10;

会话1：

select * from emp a,dept b where a.deptno=b.deptno and a.deptno=10 for update of b.deptno;

会话2：

update dept set dname='beijing' where deptno=10;

结论5：for update 是把所有的表的数据都锁定； for update of column 根据of 后表的条件锁定相对应的表数据。

实验7：

会话1：

--对两张表都进行了整表锁定 select * from emp e,dept d where e.deptno = d.deptno for update;

会话2：

--试图锁定scott用户的dept表 select * from dept for update wait 3;

会话1：

select * from emp a,dept b where a.deptno = b.deptno for update of a.sal ;

会话2：

select * from dept for update wait 3;   select * from emp for update wait 3;

由实验6，7都可以得到如下结论：

结论6：for update of columns 用在多表连接锁定时，可以指定要锁定的是哪几张表，而如果表中的列没有在for update of 后面出现的话，就意味着这张表其实并没有被锁定，其他用户是可以对这些表的数据进行update操作的。

实验8：

会话1：

select * from emp where empno = 7369 for update; 会对表中员工编号为7369的记录进行上锁。

会话2：

update emp set sal=3000 where empno = 7369;

会话1：

select * from emp where empno = 7369 for update of job; 会对表中员工编号为7369的记录进行上锁。

会话2：

update emp set sal=3000 where empno = 7369;

结论7：单独一张表的操作来看，for update和for update of column_name是一样效果的，都是锁定了where限制的行数据。

最后总结：

1、select * from Table1 for update;  --锁定表的所有行，只能读不能写   2、select * from Table1 where pkid = 1 for update;  --只锁定pkid=1的行   3、select * from Table1 a join Table2 b on a.pkid=b.pkid for update;    --锁定两个表的所有行   4、select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update;    --锁定两个表的中满足条件的行   5、select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update of a.pkid;    --只锁定Table1中满足条件的行   select for update of，这个of子句在涉及到多个表时，具有较大作用，如不使用of指定锁定的表的列，则所有表的相关行均被锁定，若在of中指定了需修改的列，则只有与这些列相关的表的行才会被锁定。