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

本文详细探讨了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锁定的数据。

<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; -- 住账户123456 -- 检查余额是否充足 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、付费专栏及课程。

余额充值