oracle中锁的级别和锁表语句

最新推荐文章于 2024-06-16 11:47:28 发布

chushiyunen

最新推荐文章于 2024-06-16 11:47:28 发布

阅读量3.1k

点赞数 1

分类专栏： oracle

本文链接：https://blog.csdn.net/enthan809882/article/details/104959217

版权

oracle 专栏收录该内容

90 篇文章 4 订阅

订阅专栏

oracle的锁级别一直感到比较混乱。把它整理一下。

文章目录

锁模式值列表

值为0-6。其实常见的就是3(行级排他锁)比较多。

值	名称	描述
0	none	-
1	null	-
2	row share 或 share update	-
3	row exclusive
4	share	-
5	share row exclusive	-
6	exclusive	-

锁表语句例子

因为 v$locked_object 的 locked_mode 字段对应的值为0-6，所以这里我用mode1-mode6来代码各个锁。
各个mode的例子语句(nowait可以不加，这里是为了迅速返回结果)：

-- (row share 和 share update 一样，是为了兼容早期版本）
lock table t_user in row share mode nowait;  -- mode2  
lock table t_user in share update mode nowait;  -- mode2  
lock table t_user in row exclusive mode nowait;   -- mode3
lock table t_user in share mode nowait;   -- mode4
lock table t_user in share row exclusive mode nowait;  -- mode5
lock table t_user in exclusive mode nowait ;  -- mode6

还有些语句也可以锁表：

select * from t_user where id ='1' for update; -- 行锁
select * from t_user for update; -- 表锁

wait 和 nowait

wait后面跟秒数，如果超过时间事务未提交则释放锁。
nowait表示不等待，如果获取不到锁会立刻返回错误。通常报错：ora-00054 资源正忙。

select * from t_user for update wait 10 ;  -- 锁定十秒
select * from t_user for update no wait;  -- 如果获取不到锁，会立刻报错

锁模式

0和1略。

row share(share update) 2级锁

ROW SHARE允许并发访问锁定表，但禁止用户锁定整个表以进行独占访问。 ROW SHARE是与的同义词SHARE UPDATE，为了与早期版本的Oracle数据库兼容而提供了。

用2个session分别执行sql进行测试：

session1
lock table t_user in row share mode nowait;  -- mode2
session2
mode2-mode5都可以。
lock table t_user in exclusive mode nowait ;  -- mode6   执行失败，因为2级锁，禁止独占访问。

row exclusive 3级锁

ROW EXCLUSIVE与相同ROW SHARE，但也禁止锁定SHARE模式。ROW EXCLUSIVE在更新，插入或删除时会自动获得锁。

用2个session分别执行sql进行测试：

session1
lock table t_user in row exclusive mode nowait;   -- mode3
session2
mode2-mode3都可以。
lock table t_user in share mode nowait;   -- mode4  执行失败，因为禁止锁定share模式。

share 4级锁

允许并发查询，但禁止更新锁定表。

用2个session分别执行sql进行测试：

session1
lock table t_user in share mode nowait;   -- mode4
session2
mode2可以。
lock table t_user in row exclusive mode nowait;   -- mode3  执行失败，禁止更新锁定表。
update  t_user set u_age='33' where id ='1' ;   update 语句也执行失败

share row exclusive 5级锁

SHARE ROW EXCLUSIVE用于查看整个表，并允许其他人查看表中的行，但禁止其他人将表锁定为SHARE模式或更新行。

用2个session分别执行sql进行测试：

session1
lock table t_user in share row exclusive mode nowait;  -- mode5
session2
mode2可以。
lock table t_user in row exclusive mode nowait;   -- mode3  执行失败，禁止更新锁定表。

exclusive 6级锁

EXCLUSIVE 允许对锁定表进行查询，但禁止对其进行任何其他活动。

用2个session分别执行sql进行测试：

session1
lock table t_user in exclusive mode nowait ;  -- mode6
session2
lock table t_user in row share mode nowait;  -- mode2  执行失败

for update

for update在oracle中用的很多。但是需要很多注意点。

for update之后，要尽快提交

这个不解释，不提交就会一直缩下去。直到session关闭。

尽量使用行锁，不使用表锁

锁表的时候按照，最小影响影响原则。条数越少越好，时间越短越好。
错误的示范：

select * from t_user for update ; -- 整个表都锁住了

最好是这样：

select * from t_user where id='1' for update ;  -- 只锁住id=1 这条记录(前提是id是索引列)

for update的条件列，要尽量使用索引列

索引列是行锁。如果是非索引列，会是表锁。
这个我实测失败了，待完善。

各个锁的例子

1级锁有：Select，有时会在v$locked_object出现。

2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。 (待考证，因为我觉得 select for update 应该是3级锁)

3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

forupdate是2级锁还是三级锁

是2级锁。但是在事务中是三级锁。
从pl/sql中执行forupdate是三级锁，因为pl/sql会自动加一个事务。具有排他性，就变成3级锁了。
如何验证forupdate是2级锁，如下代码：
1、不要加@Transactional
2、用2个请求调如下方法，发现都能打印forupdate。
说明forupdate可以同时执行。但是在事务中不行。

// for update是共享锁么  2级锁
@ResponseBody
@RequestMapping("/demo10")
public String demo10()
{
	try {
		userMapper.forupdateTable("T_USER"); // 锁表
		System.out.println("已forupdate");
		Thread.sleep(10000);
		System.out.println("已提交");
	}catch (InterruptedException e) {
		e.printStackTrace();
	}catch (CannotAcquireLockException e) { // nowait 获取不到锁会抛这个异常
		e.printStackTrace();
		System.out.println("异常了");
	}
	return "demo10";
}