1.查询数据库中的锁
select * from v$lock;
select * from v$lock where block=1;
2.查询被锁的对象
select * from v$locked_object;
3.查询阻塞
查被阻塞的会话
select * from v$lock where lmode=0 and type in ('TM','TX');
查阻塞别的会话锁
select * from v$lock where lmode>0 and type in ('TM','TX');
4.查询数据库正在等待锁的进程
select * from v$session where lockwait is not null;
5.查询会话之间锁等待的关系
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;
6.查询锁等待事件
select * from v$session_wait where event='enqueue';
行级锁
行级锁只对用户正在访问的行进行锁定。如果该用户正在修改某行,那么其他用户就可以更新同一表中该行之外的数据。例如:如果用户1正在更新Jobs 表中的第一行,则用户2可以同时修改该表中的第二行。也就是说除了该表中的第一行,其他用户可以修改任意行,但是第一行的数据其他用户只能select。
行级锁是一种排他锁,防止其他事务修改此行,但是不会阻止读取此行的操作。在使用INSERT、UPDATE、DELETE 和SELECT…FOR UPDATE 等 语句时,Oracle 会自动应用行级锁锁定。SELECT...FOR UPDATE 语句允许用户每次选择多行记录进行更新,这些记录会被锁定,且只能由发起查询的用户进行编辑。只有在回滚或提交事务之后,锁定才会释放,其他用户才可以编辑这些记录。
SELECT...FOR UPDATE 语句的语法如下:
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT];
其中:
OF 子句用于指定即将更新的列,即锁定行上的特定列。
WAIT 子句指定等待其他用户释放锁的秒数,防止无限期的等待。
“使用FOR UPDATE WAIT”子句的优点如下:
⒈防止无限期地等待被锁定的行;
⒉允许应用程序中对锁的等待时间进行更多的控制。
⒊对于交互式应用程序非常有用,因为这些用户不能等待不确定
举例:比如一个用户在SQL PLUS下输入这条语句:SQL>SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE;
此时再开启一个SQL PLUS,以相同的用户登陆,执行下面的命令。
SQL<> SELECT * FROM order_master WHERE vencode='V02' FOR UPDATE WAIT 5;
由于要更新的行已经被锁定,上述命令在等待5秒钟之后返回,并给出如下的错误信息:
ERROR 位于第1行;
ORA-30006;资源已被占用;执行操作时出现WAIT超时。
PS:再开启的SQL PLUS 是以相同用户登陆的,用其他用户登陆更是不可能。
表级锁
表级锁被锁定的表,暂时放在内存中,不提交不进去数据库,也就是说,多个用户在同一时间,同时修改同一个表的同一行时,同时点提交,但是还是按随机的先后被提交进数据库,而不是同时被提交,而是先随机存储后,被再次提交的覆盖。
表级锁将保护表数据,在事务处理过程中,表级锁会限制对整个表的访问。可以使用LOCK TABLE 语句显示地锁定表。表级锁用来限制对表执行添加、更新和删除等修改操作。锁定表的语法如下:
LOCK TABLE IN MODE [NOWAIT];
其中:
lock_mode 是锁定的模式。
NOWAIT 关键字用于防止无限期的等待其他用户释放锁。
表级锁的模式包括以下内容:
行共享 (ROW SHARE, RS): 允许其他用户访问和锁定该表,但是禁止排他锁定整个表。行共享锁锁定后,在同一时刻,不同用户可以对同一个表中的被行共享锁锁定后的该行,具备增、删、改、查的功能。
行排他(ROW EXCLUSIVE, RX):与行共享模式相同,同时禁止其他用户在此表上使用共享锁。使用SELECT...FOR UPDATE 语句会在表上自动应用排他锁。被行排他后,其他用户不能同时修改该行,但是可以插入行,可以查询该行,其他用户也不能再在该表上对此行进行排他。
共享(SHARE, S):共享锁将锁定表,仅允许其他用户查询表中的行,但不允许插入、更新或删除行。多个用户可以同时在同一个表中放置共享锁,即允许资源共享,因此得名“共享锁”。例如,如果用户每天都需要在结帐时更新日销售表,则可以在更改该表时使用共享锁以确保数据的一致性。也就是说该表只能查,其他用户想修改表中行的数据,只需要对该表进行共享锁。
共享行排他(SHARE ROW EXCLUSIVE , SPX):执行比共享表更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。共享行排他是除了该行以外的其他行也不能增、删、改。只能在此表中加低级表。要是想在该表中更改其他行的数据,就只有其他用户对该行进行共享行排他锁,也仅仅只能修改被这个用户锁定的行,而其他的行也修改不了。
排他(EXCLUSIVE,E):对表执行最大限制。除了允许其他用户查询该表的记录,排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。这个锁应该叫锁中之王,他锁住了的话,其他用户就只有查询的功能了,就别想在该表中干别的事了。
BTW:在能加很多锁的表中,如果第一个用户对该表锁定时,没有使用“NOWAIT”语句,是需要第一个用户对该表COMMIT或ROLLBACK 命令释放锁定后,其他用户才能对该表进行锁定。如果其他用户违反了该条,就会无期限的等待。。。。
SQL> LOCK TABLE order_mater IN SHARE MODE;
而使用下面的语句就可以很少的预防这种情况的存在:
SQL> LOCK TBALE order_mater IN SHARE MODE NOWAIT;
下面对行级锁和表级锁来个总结,行级锁锁定的只是行,而表级锁是对表的锁定,但是在表级锁锁定表后,还可以在该表中使用行级锁。