MySQL 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE

最新推荐文章于 2025-01-04 13:40:49 发布
最新推荐文章于 2025-01-04 13:40:49 发布
阅读量124 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库
原文链接：https://my.oschina.net/dyyweb/blog/861417
本文详细介绍了MySQL中的共享锁与排他锁的工作原理及其应用场景。解释了SELECT...LOCK IN SHARE MODE和SELECT...FOR UPDATE如何实现数据读写控制，并讨论了行锁与索引之间的关系。

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

为什么80%的码农都做不了架构师？>>>   hot3.png

总结

此处参考：MySQL中的共享锁与排他锁

SELECT … LOCK IN SHARE MODE ：共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。

如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。

SELECT … FOR UPDATE：排他锁(X锁, exclusive locks)。如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。

注：普通 select 语句默认不加锁，而CUD操作默认加排他锁。

 

原文地址：http://blog.youkuaiyun.com/u012099869/article/details/52778728

 

行锁和索引的关系：查询字段未加索引（主键索引、普通索引等）时，使用表锁

注：InnoDB行级锁基于索引实现。

未加索引时，两种行锁情况为（使用表锁）： 
- 事务1获取某行数据共享锁，其他事务可以获取不同行数据的共享锁，不可以获取不同行数据的排他锁 
- 事务1获取某行数据排他锁，其他事务不可以获取不同行数据的共享锁、排他锁

加索引后，两种行锁为（使用行锁）：

  • 事务1获取某行数据共享锁，其他事务可以获取不同行数据的排他锁
  • 事务1获取某行数据排他锁，其他事务可以获取不同行数据的共享锁、排他锁

转载于:https://my.oschina.net/dyyweb/blog/861417

MySQL 行级锁详解：SELECT FOR UPDATE 和 LOCK IN SHARE MODE
鸟人的博客
04-12 1135
MySQL的InnoDB引擎支持行级锁定，主要通过(排他锁/X锁)和(共享锁/S锁)实现。这两种锁定机制为并发事务提供了精细的控制能力。
mysql for update 锁_从 select ... for update来分析mysql的锁
weixin_34348050的博客
01-18 870
一 mysql的悲观锁 - 以行锁做示例每次拿数据的时候都认为别的线程会修改数据，所以每次拿数据的时候都会给数据上锁。上锁之后，当别的线程想要拿数据时，就会阻塞。直到给数据上锁的线程将事务提交或者回滚。传统的关系数据库里面很多用了这种锁机制，比如行锁，表锁，共享锁，排他锁等，都是在做操作之前先上锁。下面的图从网上粘的，用mysql的两个视窗演示一下行锁(左边先执行)(1) 左边的线程，在事务中通过...
mysql 共享锁 ，排他锁
jiangshanmnaa的博客
10-30 557
共享锁 (lock in share mode) 简介 允许不同事务之前共享加锁读取，但不允许其它事务修改或者加入排他锁 如果有修改必须等待一个事务提交完成，才可以执行，容易出现死锁 共享锁事务之间的读取 session1: start transaction; select * from test where id = 1 lock in share mode; session2:...
mysql in share mode_for update 和 lock in share mode
weixin_39758229的博客
01-27 127
共同点：两者都必须在事务中使用不同点：for update 对记录加写锁，此时记录不能被其他线程加读锁或者写锁。lock in share mode对记录加读锁，此时记录能被其他线程加读锁，不能加写锁。但是可以在当前事务内，再加写锁。mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from test wh...
mysql in share mode_数据库中的共享锁「lock in share mode」与排它锁「for update」
weixin_39667041的博客
01-19 441
共享锁(lock in share mode)讲解之前，先来看两个事务：创建一个student表，并插入几条数据：create table `student` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`name` varchar(128) NOT NULL COMMENT '姓名',`sex` tinyint NOT NULL COMMENT '...
MySQL 事务锁 in share mode
weixin_33819479的博客
08-15 148
MySQL In share mode 事务锁是比较常用的共享锁 select ... from lock in share mode; 生成表级共享锁，允许其它线程读取数据但不能修改数据。 --First thread mysql> select * from test lock in share mode; +------+------+ | id | name |...
MySQL中“for update“、“lock in share mode“解析
cxs_123的博客
03-18 1963
前言 共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能改。 排它锁又称为写锁，简称X锁，顾名思义，排它锁就是不能与其它锁并存，如一个事务获取了一个数据行的排它锁，其它事务就不能再获得该行的其它锁，包括共享锁和排它锁，但是获取此数据行排它锁的事务可以对数据进行读取和修改。 其中： 排它锁(X)：select xxx from xxx where xxx for update; 共享锁(S)：select xxx from xxx where x
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=...
mysql lock in share mode 容易死锁
信息系统开发
06-22 3089
JPA+Hibernate 锁 LockModeType.PESSIMISTIC_READ，容易出现死锁的情况，资料记录 情景一 LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends
Mysql两种锁定读的情况分析（ LOCK IN SHARE MODE和FOR UPDATE）
孤海岛主
04-01 1761
1.SELECT ... LOCK IN SHARE MODE;（共享锁:S锁） 使用该语句加锁后，其他事务既可以对记录进行读取又可以对记录加共享锁（也就是在别的事务中再次使用SELECT ... LOCK IN SHARE MODE;）。但是不能够对记录进行修改和加排它锁（X锁）； 2：SELECT ... FOR UPDATE;（排他锁：X锁） 使用该语句加锁后，其他事务可以对记录进行读操作，但是不能对改记录进行任何加锁操作也不能对记录做修改，删除操作。 ...
margin和padding区别和应用场景_MySQL两种读锁的区别和应用场景分析
weixin_39701288的博客
12-04 307
读锁的概念和区别如果在MySQL的事务里查询数据，然后在同一事务中插入或更新相关数据，常规的SELECT语句不能提供足够的保护。其他并行的事务可以更新或删除第一个事务里刚查询的相同行。 InnoDB支持两种类型的读锁，提供了额外的安全性：SELECT ... LOCK IN SHARE MODE在读取到的行上设置共享锁。其他会话可以读取行，也可以继续给行加共享锁，但是在当前事务提交之前其他会话不能...
mysql中FOR UPDATE 和 LOCK IN SHARE MODE锁 的区别，使用场景，示例
小哇
10-15 776
它获取的是共享锁（shared lock），意味着其他事务可以同时读取这些数据，但是不能进行任何写操作，直到所有共享锁被释放。它获取的是排他锁（exclusive lock），意味着其他事务只能等待当前事务结束才能对锁定的数据进行操作。需要注意的是，实际使用时，锁定策略的选择取决于应用程序的具体需求，包括并发控制的需求以及是否允许读取未提交的数据等。，因为窗口1持有的共享锁不允许任何写操作。假设还是两个窗口，窗口1和窗口2。，直到窗口1的事务结束并释放锁。假设有两个窗口，窗口1和窗口2。
MySQL锁机制
bt_xxx的博客
04-26 381
一、MySQL锁概述 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低，又分读锁与写锁。 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高，又分共享锁与排他锁。 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 MySQL的锁机制的特点是不同的存储引擎支持不同的锁机制： ...
MySQL-锁机制
愿你我都能实现自己的梦想
01-04 794
Mysql的锁机制
mysql锁介绍
u012758488的博客
03-09 323
mysql锁介绍
深入理解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 in share mode_mysql共享锁lock in share mode的实际使用场景
weixin_30575925的博客
02-01 1303
看了MySQL的官方文档: 关于锁定对象的部分分两种锁共享锁: SELECT … LOCK IN SHARE MODE排它锁: SELECT … FOR UPDATE其中排他锁这个场景大家都知道, 就是多个session的事务要对同一个表的一/多条数据进行更新操作的时候, 要先锁定再更新来消除并发造成的数据不一致而共享锁的使用场景说的有主-从表的这种情况, 比如想在从表insert一条记录, 需...
[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
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、付费专栏及课程。

余额充值