Oracle中select for update ...一些区别

本是古典何须时尚 2021-06-26 16:06 514阅读 0赞

`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

![在这里插入图片描述][20180924161549552]

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

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

update test8 set price=6 where ID=1

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

现在我们将会话一的事务提交(`commit`)  
![在这里插入图片描述][20180924161640231]

会话二的`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

![在这里插入图片描述][20180924161740292]

总结分析:

因为会话一,并没有`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

![在这里插入图片描述][20180924161950533]

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

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

select * from test6 for update skip locked

![在这里插入图片描述][20180924162056981]

select * from test8 for update skip locked

![在这里插入图片描述][20180924162131901]  
测试结果证明,在没有`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

![在这里插入图片描述][20180924162333744]

select * from test8 for update skip locked

![在这里插入图片描述][20180924162432916]

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

[20180924161549552]: /images/20210626/b773567f9246418789a05c57e7b1312d.png
[20180924161640231]: /images/20210626/dbdeadff899341fbb9d1dea337eb0409.png
[20180924161740292]: /images/20210626/d89a0edbbf8f45cfb6a29d104b103af4.png
[20180924161950533]: /images/20210626/f16a61aaf154486e97a7e93baa1b8098.png
[20180924162056981]: /images/20210626/6f2b3d6dda614860a86d6fcc4e344a2f.png
[20180924162131901]: /images/20210626/5f91412f2db94ddcb86c2a13f656904a.png
[20180924162333744]: /images/20210626/859985c4d5124f43a33be59989ac870c.png
[20180924162432916]: /images/20210626/803dab02043c4e3587752aa918ec639f.png