专栏首页GreenLeavesselect for update和select for update wait和select for update nowait的区别

select for update和select for update wait和select for update nowait的区别

CREATE TABLE "TEST6" 
(    
   "ID" VARCHAR2(30), 
   "NAME" VARCHAR2(30), 
   "AGE" NUMBER(2,0), 
   "SEX" VARCHAR2(2), 
   "ENAME" VARCHAR2(30), 
   "ADDTIME" DATE
)
insert into TEST6 (id, name, age, sex, ename, addtime) values ('1', '张三', 18, null, 'zhangsan', to_date('14-03-2017 00:00:09', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('2', '李四', null, '1', 'Lisi', to_date('01-03-2017 02:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('3', '王五', 20, '0', 'wangwu', to_date('09-01-2017 08:55:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('4', '赵六', 23, '0', 'zhaoliu', to_date('03-03-2016 04:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('5', '冯七', 22, null, 'fengqi', to_date('08-03-2017 12:00:01', 'dd-mm-yyyy hh24:mi:ss'));
CREATE TABLE "TEST8" 
(    
   "ID" NUMBER, 
   "ORDERID" NUMBER, 
   "PRODUCTID" NUMBER, 
   "PRICE" NUMBER(10,2), 
   "QUANTITY" NUMBER
) 
insert into TEST8 (id, orderid, productid, price, quantity) values (1, 1, 1, 6, 10);
insert into TEST8 (id, orderid, productid, price, quantity) values (2, 1, 2, 4, 5);
insert into TEST8 (id, orderid, productid, price, quantity) values (3, 1, 3, 10, 2);
insert into TEST8 (id, orderid, productid, price, quantity) values (4, 2, 1, 3, 6);
insert into TEST8 (id, orderid, productid, price, quantity) values (5, 2, 2, 4, 6);

以上是基础数据

在oracle中,如果只进行select语句的话,是不会进行加锁的,也就是oracle会返回当前时刻的结果集,即使这个时候可能有另外一个进程在修改当前结果集的数据,因为没有加锁,所以oracle还是会正常的返回当前时刻的结果集,不会有任何影响。

他们三个共同点:

当使用select for update 或者select for update wait或者.....,那么oralce会给符合where条件的数据行加上一个行级锁

1、select for update

但是如果你的select 语句加了for update,那么就不是上面这回事了,当oracle发现select的当前结果集中的一条或多条正在被修改(注意:当数据被修改时,此时的数据行是被加锁的),那么他就会等到当前当前结果集被修改完毕并且commit之后才进行select操作,并对结果集进行加锁。同样的,如果查询语句发出后,其他会话需要修改结果集中的一条(或几条数据)也许要等到查询结束(commit)之后,才可以执行修改操作。

代码如下:

新建SQL窗口1,(相当于新建一个session会话)

select * from test8 for update

for  update 对整个结果集进行了加锁,意味着在当前session进行commit之前,任何其他的session进行update、delete、insert操作都会进行等待

新建SQL窗口2(相当于新建一个session会话)

update test8 set price=6 where ID=1

显示执行中,等待会话一的查询执行完成

现在我们将会话一的事务提交(commit)

会话二的update语句执行成功

2、select for update nowait

for  update和for update nowait都会对查询到的当前结果集进行加锁,所不同的是,当有另外的会话在修改当前结果集中的数据,select for nowait所进行的查询操作不会进行等待,当发现结果集中的一些数据被加锁,立刻返回 “ORA-00054错误,内容是资源正忙, 但指定以 NOWAIT 方式获取资源”。测试代码如下:

新建一个SQL窗口1(相当于新建一个会话)

update test8 set price=3 where ID=1

更新test8表的一条数据,但是不进行commit操作

然后新建SQL窗口2(相当于新建一个会话)select for update nowait操作

select * from test8 for update nowait

总结分析:

因为会话一,并没有commit所以test8中的ID=1的行被加锁了,所以当会话二进行select for update nowait检索到ID=1的数据行被加锁了,就立刻返回 “ORA-00054错误,内容是资源正忙, 但指定以 NOWAIT 方式获取资源”的错误。

接下来我们对会话一进行commit操作,

在执行会话二的select查询,ok,可以查出来了,并且对当前数据集进行了加锁操作,其他会话想要进行修改操作,必须等到会话二commit之后

3、select for update wait

它也会对查询到的结果集进行加锁,select for update wait与select for update nowait不同的地方是,当有另外的会话对它的查询结果集中的某一行数据进行了加锁,那么它不会像nowait一样,立即返回"ORA-00054错误",而是它支持一个参数,设定等待的时间,当超过了设定的时间,那一行数据还处于加锁的状态,那么它也会返回“ORA-00054错误,内容是资源正忙, 但指定以 NOWAIT 方式获取资源”。测试代码如下:

首先新建SQL窗口1(相当于新建一个会话)执行update 语句,但是不进行commit操作,那么当前数据行将被lock

update test8 set price=3 where ID=1

接着新建SQL窗口2(相当于新建一个会话),在执行select for update wait 6,如果当前查询检索的数据集中,有被加锁了的行数据,那么等待6秒,如果6秒后,其他会话,还没有执行commit释放被加了锁的数据行的话,那么返回“ORA-00054错误,内容是资源正忙, 但指定以 NOWAIT 方式获取资源”。

select * from test8 for update wait 6

执行语句6秒后,报错。

最后对会话一(SQL窗口一)进行commit操作

紧接着执行会话二(SQL窗口二)中的sql语句,此时被加锁的数据行被释放

正常的检索除了数据行,当时当前数据集被加锁,其他会话想操作此数据集,必须等会话二中的事务commit之后,才可以进行修改

4、OF子句

在多表查询中如果需要对多表查询的结果集进行加锁,可以使用OF子句。

如果存在OF子句,那么就对满足OF子句的单表进行加锁,如果不存在OF子句就对整个结果集进行加锁,代码如下:

a、不使用OF子句

select a.ID,a.Name,b.price from test6 a
LEFT JOIN test8 b
ON a.ID=b.ID where b.ID>3
for update

没有进行commit操作,此时对test6和test8中的ID>3的数据行都进行了加锁,测试代码如下:

新建一个会话,执行以下语句:

select * from test6 for update skip locked
select * from test8 for update skip locked

测试结果证明,在没有OF子句的情况下,对多表查询的结果集进行select foe update,oracle会对满足where 条件的所有数据行进行加锁

b、使用OF子句

使用OF子句,那么oracle就会对满足OF子句的表进行加锁,在多表查询中。代码如下:

select a.ID,a.Name,b.price from test6 a
LEFT JOIN test8 b
ON a.ID=b.ID where b.ID>3
for update of a.ID

在不执行commit操作的情况,新建一个会话,执行一下语句:

select * from test6 for update skip locked
select * from test8 for update skip locked

比对测试结果,发现在OF子句的作用下,oracle对同时满足where子句(设置要加锁的数据行)和OF子句(主要设置加锁的表)的数据行进行了加锁。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle给Select结果集加锁,Skip Locked(跳过加锁行获得可以加锁的结果集)

    1、通过select for update或select for update wait或select for update nowait给数据集加锁 具体实现...

    郑小超.
  • SQL学习之空值(Null)检索

    在创建表表,我们可以指定其中的列包不包含值,在一列不包含值时,我们可以称其包含空值null。 确定值是否为null,不能简单的检查是否=null。select语...

    郑小超.
  • Oracle dbms_random随机函数包

    dbms_random是oracle提供的一个随机函数包,以下是它的一些常用的功能: 1、dbms_random.value 作用:生成一个大于等于0,大于等于...

    郑小超.
  • Cypress系列(22)- 可操作类型的命令 之 select()

    https://www.cnblogs.com/poloyy/category/1768839.html

    小菠萝测试笔记
  • SQL注入攻击导致BIGINT溢出错误

    首先, 溢出,通俗的讲就是意外数据的重新写入,就像装满了水的水桶,继续装水就会溢出,而溢出攻击就是,攻击者可以控制溢出的代码,如果程序的对象是内核级别的,如d...

    随心助手
  • Knockout.Js官网学习(options绑定)

    options绑定控制什么样的options在drop-down列表里(例如:<select>)或者 multi-select 列表里 (例如:<select ...

    aehyok
  • 使用联接和子查询来查询数据

    --Chapter 3 使用联接和子查询来查询数据 --内容提要 go /* (一)、使用联接查询数据 1. 内联接 2. 外联接 3...

    赵腰静
  • MySQL从删库到跑路(三)——SQL语言

    SQL是结构化查询语言(Structured Query Language),是用于访问和处理数据库的标准的计算机语言。 SQL语言的功能如下: A、SQL面向...

    良月柒
  • CTF考点总结-sql注入篇

    mysql.user下有所有的用户信息,其中authentication_string为用户密码的hash,如果可以使用可以修改这个值,那么就可以修改任意用户的...

    用户2202688
  • java中子类的继承性

    子类的成员中有一部分是子类自己申明定义的,另一部分是从他的父类继承的。子类继承父类的成员变量作为自己的一个成员变量,就好像他们是在子类中直接

    Hongten

扫码关注云+社区

领取腾讯云代金券