【Mysql行锁】SELECT...FOR UPDATE和SELECT ...LOCK IN SHARE MODE

最新推荐文章于 2022-12-18 16:37:12 发布
最新推荐文章于 2022-12-18 16:37:12 发布
阅读量977 收藏
点赞数
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/sdu_yujielei/article/details/21634793
本文详细探讨了MySQL中的行锁机制，包括SELECT...FOR UPDATE创建的独占锁，不允许其他事务进行修改或使用独占锁，以及SELECT...LOCK IN SHARE MODE设置的共享锁，允许并发读取但阻止修改。两者的锁定范围取决于查询的主键条件，无主键条件可能导致锁定整个表。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1 SELECT...FOR UPDATE

锁的类型：读最新的可见数据，在每个它读取的行设置独占锁。

      锁定范围：若SELECT的where条件中含有主键，则锁定搜索结果行；否则，锁定整个表。

      排他性：多个独占锁不共存，也不可以与共享锁共存。故：SELECT...FOR UPDATE锁定的数据，在其他事务中无法通过 SELECT...FOR UPDATE 和 SELECT...LOCK IN SHARE MODE访问。



2 SELECT...LOCK IN SHARE MODE

锁的类型：读最新的可用数据，并设置一个共享锁。

        锁定范围：若SELECT的where条件中含有主键，则锁定搜索结果行；否则，锁定整个表。

     排他性：多个共享锁可以共存，不可以与独占锁共存。故：SELECT...LOCK IN SHARE MODE锁定的数据，可以被SELECT...IN SHARE MODE访问，但无法被SELECT...FOR UPDATE访问。



注：

(1) SELECT操作不受独占锁和共享锁的影响，因此SELECT操作可以访问SELECT...FOR UPDATE 和 SELECT...LOCK IN SHARE MODE锁定的数据。

深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE
cug_jiang126com的专栏
01-19 4万+
概念和区别 SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。 SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排
MySQL 行级锁详解：SELECT FOR UPDATE 和 LOCK IN SHARE MODE
鸟人的博客
04-12 1145
MySQL的InnoDB引擎支持行级锁定，主要通过(排他锁/X锁)和(共享锁/S锁)实现。这两种锁定机制为并发事务提供了精细的控制能力。
MySQL的锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
tianlianchao1982的专栏
06-07 820
SELECT ... FOR UPDATE读最新的可见数据，在每个它读取的行设置独占锁定。 SELECT ... LOCK IN SHARE MODE以共享模式读取数据。共享模式执行一个读意味着我们读最新的可用数 据，并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时，如果最 新的数据属于其它客户端尚未提交的事务，我们等着知道那个事务被提交。
mysql select lock in share mode_MySQL 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE...
weixin_35968523的博客
01-27 710
总结SELECT … LOCK IN SHARE MODE ：共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。SELECT … FOR UPDATE：排他锁(X锁, exclusive locks)。如果事务对数...
[MySQL] 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE
列苗
10-10 1万+
一、译文翻译来自官方文档：Locking ReadsIf you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can upda
mysql for update 锁_从 select ... for update来分析mysql的锁
weixin_34348050的博客
01-18 874
一 mysql的悲观锁 - 以行锁做示例每次拿数据的时候都认为别的线程会修改数据，所以每次拿数据的时候都会给数据上锁。上锁之后，当别的线程想要拿数据时，就会阻塞。直到给数据上锁的线程将事务提交或者回滚。传统的关系数据库里面很多用了这种锁机制，比如行锁，表锁，共享锁，排他锁等，都是在做操作之前先上锁。下面的图从网上粘的，用mysql的两个视窗演示一下行锁(左边先执行)(1) 左边的线程，在事务中通过...
mysql SELECT FOR UPDATE语句使用示例
09-10
LOCK IN SHARE MODE`和`SELECT ... FOR UPDATE`都是在事务中对数据进行锁定的方法。前者允许其他事务读取但不允许修改锁定的数据，可能导致死锁；后者则不仅阻止其他事务修改，也阻止其他事务读取，直到当前事务...
mysql事务select for update及数据的一致性处理讲解
09-09
如果不使用事务和`FOR UPDATE`，可能存在如下风险：在`SELECT`之后到`UPDATE`之前，其他事务可能已经将库存减少到0，但我们的事务仍然错误地更新了数量。为了解决这个问题，可以使用如下事务： 1. `SET AUTOCOMMIT=...
浅谈select for update 和select lock in share mode的区别
weixin_33961829的博客
08-14 229
有些情况下为了保证数据逻辑的一致性，需要对SELECT的操作加锁。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作。 1、 SELECT …… FOR UPDATE 2、 SELECT …… LOCK IN SHARE MODE 其中，SELECT …… FOR UPDATE对读取的记录加一个锁，其他事务不能对已锁定的行加任何锁。而SELEC...
select ... for update 语句的分析
860MHz的专栏
01-30 1796
1. 前言 本文将介绍select … for update语句相关知识，以及对可能导致的死锁和影响进行分析。 如无特殊说明，本文涉及的MySQL版本为8.0.22，事务隔离级别为RR，数据库引擎为INNODB。 2. 实验数据准备 创建一个测试表t_a： CREATE TABLE `test_a` ( `id` int NOT NULL, `a` int NOT NULL, `b` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAU
select for update和select for update wait和select for update nowait的区别
weixin_34265814的博客
03-18 802
CREATE TABLE "TEST6" ( "ID" VARCHAR2(30), "NAME" VARCHAR2(30), "AGE" NUMBER(2,0), "SEX" VARCHAR2(2), "ENAME" VARCHAR2(30), "ADDTIME" DATE ) insert into TEST6
执行 select ... for update 语句，如果查询条件没有索引字段的话，是加行锁还是加表锁？
小林coding
12-18 6793
很有争议的问题
SELECT语句中的for update的用法(锁的运用)
热门推荐
SunWuKong_Hadoop的博客
07-24 4万+
回复1：一般FOR UPDATE用在PL/SQL的游标里，它的作用就是一个行级锁（对游标里所有的记录），如果其他进程要更新这个游标行级锁里的记录，就必须等待当前进程的COMMIT或者回滚。 该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束...
mysql for lock_Mysql加锁过程详解（4）-select for update/lock in share mode 对事务并发性影响...
weixin_32987379的博客
01-20 428
select for update/lock in share mode 对事务并发性影响事务并发性理解事务并发性，粗略的理解就是单位时间内能够执行的事务数量，常见的单位是 TPS( transactions per second).那在数据量和业务操作量一定的情况下，常见的提高事务并发性主要考虑的有哪几点呢？1.提高服务器的处理能力，让事务的处理时间变短。这样不仅加快了这个事务的执行时间，也降低...
深入理解 SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE
bm1998
02-23 1017
概念和区别 SELECT … LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。 SELECT … FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取
SELECT..FOR UPDATE和LOCK IN SHARE MODE替代方案
04stone37
10-11 2079
说明 　　SELECT..FOR UPDATE是互斥锁，LOCK IN SHARE MODE是读写锁，都是为了解决“select+update并发更新的问题”，但是存在行锁升级为表锁的风险，影响并发，不建议使用。 Innodb行锁原理 　　InnoDB行锁是通过给索引上的索引项加锁来实现的，这意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！但是，...
select for update 和 lock in share mode 异同点及其影响
Henry_Lin_Wind的博客
08-21 2175
可能用到的情景和对性能的影响 使用情景： 1. select *** for update 的使用场景 为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。 对主键索引加锁 2. select *** lock in share mode 使用场景 为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最...
select.... for update
最新发布
07-02
<think>我们正在讨论SQL中的SELECT ...FOR UPDATE语句。根据引用，这个语句用于在事务中锁定选中的行，以防止其他事务修改这些行，直到当前事务结束（提交或回滚）。这主要用于需要保证数据一致性的场景，如银行转账或库存更新。 基本语法： STARTTRANSACTION; SELECT...FROM... WHERE... FORUPDATE;--执行更新操作COMMIT;或 ROLLBACK; 注意事项： 1.锁定范围：满足WHERE条件的所有行都会被锁定。若无WHERE条件，则锁定整个表（在InnoDB中，若没有索引可能导致表锁）。2.隔离级别：不同隔离级别会影响行为，通常建议使用REPEATABLE READ或以上。 3.死锁：多个事务相互等待锁可能导致死锁，需注意操作顺序。4.性能：锁定行会降低并发性能，应谨慎使用。5.存储引擎：仅支持行级锁的引擎（如InnoDB）有效，MyISAM不支持。示例（银行转账）：假设有账户表(accounts)： id| account_number |balance我们要从账户123向账户456转账100元。步骤：1.开始事务。2.使用SELECT... FORUPDATE锁定两个账户的行（注意：按固定顺序锁定以避免死锁，例如按id升序）。3.检查账户123余额是否足够。 4.更新账户123余额（减去100）。5.更新账户456余额（加上100）。 6.提交事务。 具体SQL：```sqlSTARTTRANSACTION; --锁定两个账户的行，按id顺序锁定以避免死锁SELECT* FROMaccounts WHEREaccount_numberIN (123,456) ORDERBY idFOR UPDATE; --检查账户123的余额SELECT balanceINTO @balanceFROM accountsWHERE account_number =123; --如果余额足够，则进行转账IF @balance >=100THENUPDATEaccounts SETbalance =balance -100WHERE account_number =123;UPDATE accountsSET balance= balance+100 WHEREaccount_number=456;END IF; COMMIT;``` 注意：在MySQL中，IF语句通常用于存储过程，如果不在存储过程中，可以使用程序逻辑判断。另一个示例（库存更新）： 假设商品库存表(products)： id| product_id| stock扣减商品ID为1001的库存1个： ```sqlSTART TRANSACTION;--锁定商品1001的行SELECT *FROM productsWHERE product_id =1001 FORUPDATE;--检查库存SELECT stockINTO @stockFROM productsWHERE product_id =1001;--如果库存足够，则扣减IF @stock >0THENUPDATE productsSET stock= stock-1 WHEREproduct_id=1001; ENDIF;COMMIT; ```重要提示：使用SELECT ...FOR UPDATE时，应尽量缩小锁定范围（使用精确的WHERE条件）并尽快提交事务，以减少锁竞争。相关问题： 1. SELECT... FORUPDATE在哪些情况下会导致表锁？2.如何避免使用SELECT ...FOR UPDATE时出现的死锁问题？ 3. SELECT... FORUPDATE和 LOCK INSHAREMODE有什么区别？</think>### SELECT ... FOR UPDATE 的用法与示例 `SELECT ... FOR UPDATE` 是 SQL 中用于实现**行级锁**的关键语句，主要在事务中锁定选中的行，防止其他事务修改这些行，确保数据一致性。以下是详细说明： --- #### **核心作用** 1. **独占锁**：锁定查询到的行，禁止其他事务更新或删除（其他事务可读，取决于隔离级别） 2. **事务安全**：保证当前事务完成前，锁定数据不被篡改 3. **适用场景**： - 银行转账（防止并发修改余额） - 库存扣减（避免超卖） - 订单处理（防止重复操作） --- #### **基本语法** ```sql START TRANSACTION; -- 开启事务 SELECT column1, column2 FROM table_name WHERE condition FOR UPDATE; -- 锁定符合条件的行 -- 执行数据更新操作... UPDATE table_name SET column1 = value WHERE condition; COMMIT; -- 提交事务（释放锁） -- 或 ROLLBACK; -- 回滚事务（释放锁） ``` --- #### **关键注意事项** 1. **锁定范围**： - 精确 `WHERE` 条件 → 行级锁 - 无 `WHERE` 条件 → 锁整个表（慎用！） - 无索引字段 → 可能升级为表锁[^3] 2. **存储引擎**： - 仅支持行锁的引擎有效（如 **InnoDB**） - MyISAM 引擎无效（仅支持表锁）[^3] 3. **隔离级别影响**： - `REPEATABLE READ`（默认）：锁定查询到的行 - `READ COMMITTED`：锁定实际更新的行[^1] 4. **死锁风险**： - 多事务循环等待锁会触发死锁 - 解决方案：按固定顺序操作资源（如按 ID 排序） --- #### **实际示例** ##### 场景：银行转账（保证余额一致性） ```sql START TRANSACTION; -- 锁定两个账户（按ID顺序避免死锁） SELECT * FROM accounts WHERE account_number IN (123, 456) ORDER BY id FOR UPDATE; -- 锁住账户123和456 -- 检查余额是否充足 SELECT balance FROM accounts WHERE account_number = 123; -- 执行转账（假设余额充足） UPDATE accounts SET balance = balance - 100 WHERE account_number = 123; UPDATE accounts SET balance = balance + 100 WHERE account_number = 456; COMMIT; ``` > **关键点**：锁定期间其他事务无法修改这两个账户的余额，防止透支[^4]。 --- ##### 场景：库存扣减（防止超卖） ```sql START TRANSACTION; -- 锁定商品ID=1001的行 SELECT stock FROM products WHERE product_id = 1001 FOR UPDATE; -- 检查库存 IF stock > 0 THEN UPDATE products SET stock = stock - 1 WHERE product_id = 1001; END IF; COMMIT; ``` --- #### **常见问题解决方案** 1. **死锁处理**： - 缩短事务执行时间 - 按固定顺序访问资源（如按主键排序） - 设置锁等待超时：`SET innodb_lock_wait_timeout=10;` 2. **性能优化**： - 避免全表扫描（确保 `WHERE` 条件用索引） - 最小化锁定时间（事务尽快提交） > **注意**：过度使用会导致并发性能下降，仅在必要时使用[^3]。 --- #### **与其他锁对比** | **语句** | **锁类型** | **其他事务能否读** | **其他事务能否写** | |------------------------|------------|-------------------|-------------------| | `SELECT ... FOR UPDATE`| 独占锁 | ✅ (MVCC) | ❌ | | `LOCK IN SHARE MODE` | 共享锁 | ✅ | ❌ | ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值