Wetts's blog

Stay Hungry, Stay Foolish.

0%

Oracle-用法-select...for update使用方法.md

发表于 分类于

转自：https://blog.csdn.net/zwl156135995/article/details/51445862

一

作用：

select for update 是为了在查询时，避免其他用户以该表进行插入，修改或删除等操作，造成表的不一致性。

给你举几个例子：

  • select * from t for update 会等待行锁释放之后，返回查询结果。
  • select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
  • select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
  • select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

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

1
2
3
4
　　SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
其中：
　　OF 子句用于指定即将更新的列，即锁定行上的特定列。
　　WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。

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

  1. 防止无限期地等待被锁定的行；
  2. 允许应用程序中对锁的等待时间进行更多的控制。
  3. 对于交互式应用程序非常有用，因为这些用户不能等待不确定
  4. 若使用了 skip locked，则可以越过锁定的行，不会报告由 wait n 引发的‘资源忙’异常报告

补充几点：

分成两类：加锁范围子句和加锁行为子句

  • 加锁范围子句：在 select…for update 之后，可以使用 of 子句选择对 select 的特定数据表进行加锁操作。默认情况下，不使用 of 子句表示在 select 所有的数据表中加锁
  • 加锁行为子句：当我们进行 for update 的操作时，与普通 select 存在很大不同。一般 select 是不需要考虑数据是否被锁定，最多根据多版本一致读的特性读取之前的版本。加入 for update 之后，Oracle 就要求启动一个新事务，尝试对数据进行加锁。如果当前已经被加锁，默认的行为必然是 block 等待。使用 nowait 子句的作用就是避免进行等待，当发现请求加锁资源被锁定未释放的时候，直接报错返回。

在日常中，我们对 for update 的使用还是比较普遍的，特别是在如 pl/sql developer 中手工修改数据。此时只是觉得方便，而对 for update 真正的含义缺乏理解。

For update 是 Oracle 提供的手工提高锁级别和范围的特例语句。Oracle 的锁机制是目前各类型数据库锁机制中比较优秀的。所以，Oracle 认为一般不需要用户和应用直接进行锁的控制和提升。甚至认为死锁这类锁相关问题的出现场景，大都与手工提升锁有关。

所以，Oracle 并不推荐使用 for update 作为日常开发使用。而且，在平时开发和运维中，使用了 for update 却忘记提交，会引起很多锁表故障。那么，什么时候需要使用 for update？就是那些需要业务层面数据独占时，可以考虑使用 for update。场景上，比如火车票订票，在屏幕上显示邮票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用 for update。这是统一的解决方案方案问题，需要前期有所准备。

转自：https://blog.csdn.net/Victor_Cindy1/article/details/78142956

二

问题是：如果多线程之下同时查询一条数据查不到，则去插入，插入的时候也是多线程插入。

1
所以想到用 select for update 来控制不允许多线程插入（其实这种方式并没有解决问题）

先来了解几个名词

  • statement：一个 SQL 语句。
  • session：一个由 ORACLE 用户产生的连接，一个用户能产生多个 SESSION，但相互之间是独立的。
  • transaction：所有的改动都能划分到 transaction 里，一个 transaction 包含一个或多个 SQL。当一个 SESSION 建立的时候就是个 TRANSACTION 开始的时刻，此后 transaction 的开始和结束由 DCL 控制，也就是每个 COMMIT/ROLLBACK 都标示着一个 transaction 的结束。

用法介绍：

select … for update 会 LOCK 相应的 ROW。

只有一个 TRANSACTION 可以 LOCK 相应的行，也就是说如果一个 ROW 已经 LOCKED 了，那就不能被其他 TRANSACTION 所 LOCK 了。

LOCK 由 statement 产生但却由 TRANSACTION 结尾（commit，rollback），也就是说一个 SQL 完成后 LOCK 还会存在，只有在 COMMIT/ROLLBACK 后才会师释放。

使用这个行锁的情况一般是对并发的情况要求比较高的时候，需要锁住某行进行一些更新语句之后进行释放，再让其他 transaction 去操作。很好的利用了这点解决了并发的问题。数据库中锁类型：有两种基本的锁类型，排它锁（Exclusive Locks，即 X 锁）和共享锁（Share Locks，即 S 锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

Oracle 给 Select 结果集加锁，Skip Locked（跳过加锁行获得可以加锁的结果集）

for update 后面还可以跟着 [OF cols] [NOWAIT]

of 的使用主要是针对多表关联的时候，如果不使用of，对两个表涉及到的行都将锁住，使用of可以指定锁定哪个表，

例如：select a.MOBILE,b.NAME from connector a,student b where a.STU_ID=b.ID and a.MOBILE='13937134399' for updata of a.MOBILE

这样的话student表中对应的行是不加锁的，对connector一个表中行加锁，不使用两个表都加锁。

[NOWAIT]的使用是当锁冲突的时候提示的情况：

当有 LOCK 冲突时会提示错误并结束 STATEMENT 而不是在那里等待。返回错误是 “ORA-00054: resource busy and acquire with NOWAIT specified”，如果不使用就会一直等待，直到锁释放之后执行。在页面上调试的时候由于异常处理不好，把数据锁住了没有提交，也没有 rollback，遇到这样的情况的时候可以通过以下方式解决：

—–查看被锁对象的序列号、sid

1
2
3
4
5
6
7
8
9
SELECT o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM v$locked_object l,dba_objects o,v$session s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 
ORDER BY o.object_id,xidusn DESC
/
------利用sid 和序列号删除
alter system kill session '243,10265';
243是sid 10265是序列号

效果图演示

开两个窗口：

  1. 第一个窗口用 select for update 查询
    2

  2. 在另一个窗口用同样的语句查询，会显示一直在查询…说明被 block 了
    3

  3. 点击第一个窗口里的提交事务按钮，另一个窗口可以立刻获取查询结果
    4

继续回到我们开始提出的问题，为什么用 for update 不可以避免多线程插入的问题，因为：select for update 只能针对已经存在的数据进行加排他锁，如果查询的数据是 null，根本就不存在所谓的锁了。其中之一的解决办法是：加上唯一性约束条件。我们就给 OPEN_UID 和 LOGIN_TYPE 一起加唯一性约束 unique。所以数据库会保证只有一个唯一确定的记录，当两个请求同时向数据库插入相同的 OPEN_UID 和 LOGIN_TYPE 时，会采用抢占式插入，谁先插入其他方就不能再插入数据。

就会报唯一约束的异常：

1
2
3
4
5
6
7
8
9
### 
### ### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SLPROD.SYS_C0019132) violated
### The error occurred while setting parameters
### SQL: insert into SL$THIRD_LOGIN (OPEN_UID, REQUEST_SOURCE, LOGIN_TYPE, BIND_AID, NICK_NAME, ACCESS_TOKEN, EXPIRES_IN, UNION_ID)     values (      ?,       ?,       ?,       ?,          ?,          ?,          ?,         ?       )
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SLPROD.SYS_C0019132) violated
; SQL []; ORA-00001: unique constraint (SLPROD.SYS_C0019132) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SLPROD.SYS_C0019132) violated
... 86 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SLPROD.SYS_C0019132) violated