MySQL 使用SELECT ... FOR UPDATE 做事务写入前的确认
以MySQL 的InnoDB 为例,预设的Tansaction isolation level 为REPEATABLE READ,在SELECT 的读取锁定主要分为两种方式:
SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATE
这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时,都必须等待其它事务数据被提交(Commit)后才会执行。而主要的不同在于LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁 。
简单的说,如果SELECT 后面若要UPDATE 同一个表单,最好使用SELECT ... UPDATE。
举个例子: 假设商品表单products 内有一个存放商品数量的quantity ,在订单成立之前必须先确定quantity 商品数量是否足够(quantity>0) ,然后才把数量更新为1。
不安全的做法:
SELECT quantity FROM products WHERE id=3;
UPDATE products SET quantity = 1 WHERE id=3;
为什么不安全呢?
少量的状况下或许不会有问题,但是大量的数据存取「铁定」会出问题。
如果我们需要在quantity>0 的情况下才能扣库存,假设程序在第一行SELECT 读到的quantity 是2 ,看起来数字没有错,但是当MySQL 正准备要UPDATE 的时候,可能已经有人把库存扣成0 了,但是程序却浑然不知,将错就错的UPDATE 下去了。
因此必须透过的事务机制来确保读取及提交的数据都是正确的。
于是我们在MySQL 就可以这样测试: (注1)
SET AUTOCOMMIT=0;
BEGIN WORK;
SELECT quantity FROM products WHERE id=3 FOR UPDATE;
===========================================
此时products 数据中id=3 的数据被锁住(注3),其它事务必须等待此次事务提交后才能执行SELECT * FROM products WHERE id=3 FOR UPDATE (注2)如此可以确保quantity 在别的事务读到的数字是正确的。===========================================
UPDATE products SET quantity = '1' WHERE id=3 ;
COMMIT WORK;
===========================================
提交(Commit)写入数据库,products 解锁。
注1: BEGIN/COMMIT 为事务的起始及结束点,可使用二个以上的MySQL Command 视窗来交互观察锁定的状况。
注2: 在事务进行当中,只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行,一般SELECT ... 则不受此影响。
注3: 由于InnoDB 预设为Row-level Lock,数据列的锁定可参考这篇。
注4: InnoDB 表单尽量不要使用LOCK TABLES 指令,若情非得已要使用,请先看官方对于InnoDB 使用LOCK TABLES 的说明,以免造成系统经常发生死锁。
MySQL SELECT ... FOR UPDATE 的Row Lock 与Table Lock
上面介绍过SELECT ... FOR UPDATE 的用法,不过锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL 才会执行Row lock (只锁住被选取的数据) ,否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
举个例子:
假设有个表单products ,里面有id 跟name 二个栏位,id 是主键。
例1: (明确指定主键,并且有此数据,row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
例2: (明确指定主键,若查无此数据,无lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;
例2: (无主键,table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
例3: (主键不明确,table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
例4: (主键不明确,table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE 仅适用于InnoDB,且必须在事务区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况,可以利用MySQL 的Command Mode ,开二个视窗来做测试。
分享到:
相关推荐
MySQL中的`SELECT FOR UPDATE`语句是在事务处理中用于实现数据锁定的一种机制,它主要用于解决多用户并发操作时的数据一致性问题。在InnoDB存储引擎下,MySQL默认的事务隔离级别是`REPEATABLE READ`,这允许事务在...
sqlalchemy 对于行级锁有两种实现方式,with_lockmode(self, mode): 和 with_for_update(self, read=False, nowait=False, of=None),前者在sqlalchemy 0.9.0 被废弃,用后者代替。所以我们使用with_for_update ! 看...
FOR UPDATE`相同数据导致的,而是由于`SELECT ... FOR UPDATE`与`UPDATE`语句之间的交互引发的。 一个例子是,当一个事务用`SELECT ... FOR UPDATE`锁定了一部分数据(例如ID从31到60的行),而另一个事务在同一...
MySQL中的事务,默认是自动提交的,即autocommit = 1; 但是这样的话,在某些情形中就会出现问题:比如: 如果你想一次性插入了1000条数据,mysql会commit1000次的, ...在SELECT 的读取锁定主要分为两种方式:
SELECT * FROM Table1 WHERE pkid = 1 FOR UPDATE; ``` 2. **FOR UPDATE OF**: - `FOR UPDATE OF`允许开发者指定需要锁定的特定列,而不是锁定所有列。 - 这意味着只有指定的列会被锁定,其他未被指定的列可以...
当执行包含`FOR UPDATE`子句的`SELECT`语句时,Oracle会在所选择的行上放置排他锁(exclusive lock),即X锁,这种锁允许持有者读写数据,但阻止其他事务读写相同的行,直到原事务结束(通常是指提交或回滚)。...
但是奇怪的是执行其他的select语句却是可以执行的。 原因和解决方法 这种只有update无法执行其他语句可以执行的其实是因为记录锁导致的,在oracle中,执行了update或者insert语句后,都会要求commit,如果不commit...
在数据库管理中,UPDATE语句用于修改已存在的数据记录,而SELECT语句则用于查询数据。在某些场景下,我们可能需要从UPDATE语句转换为SELECT语句,以验证更新操作将影响哪些行或者理解更新逻辑。这在调试、数据分析或...
级别2的锁有Select for update、Lock For Update、Lock Row Share select for update操作,当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级独占式锁定,其他对象只能查询这些数据行,不...
在Oracle中,使用`SELECT FOR UPDATE`命令可以在预期要修改的记录上加行排它锁(X),并在表上加行共享锁(RS)。这种方法可以有效地解决读的不可重复性问题。 ##### 4. 锁之间的相互作用 锁之间存在着相互作用。...
要解决这种冲突,我们可以使用 Oracle 的并发控制机制,例如使用 SELECT FOR UPDATE 语句来锁定记录,或者使用乐观锁机制来解决冲突。我们也可以使用 Oracle 的事务隔离级别来控制事务之间的交互。 在实际应用中,...
例如:`BEGIN FOR cr IN (SELECT * FROM t_join_situation WHERE year='2011') LOOP UPDATE t_join_situation SET join_state='1' WHERE current of cr; END LOOP`。这种方式适用于大批量数据的更新,但需要注意游标...
`SELECT FOR UPDATE`是一个特殊语句,它允许在事务中锁定特定行,防止其他用户同时修改这些数据。 数据控制语言(DCL)则关注数据库的安全性和权限管理。`GRANT`和`REVOKE`是DCL的核心,分别用于赋予和撤销用户对...
- **TM锁**(表锁):在执行插入、更新、删除操作或`SELECT FOR UPDATE`时触发,目的是确保这些操作可以正常进行,同时阻止其他用户对表执行数据定义语言(DDL)操作。 - **TX锁**(事务锁/行锁):针对正在被修改的...
Query data会生成一个新窗口并执行`SELECT * FROM 表`,而Edit data则会执行`SELECT * FROM 表 FOR UPDATE`,这用于行级别的锁定,以便安全地编辑数据。 2. **Select for Update**: 在SQL Window中,你可以使用`...
2 SS(Row-S) 行级共享锁,其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share 3 SX(Row-X) 行级排它锁,在提交前不允许做DML操作 Insert、Update、Delete and so on
如果在多表查询中使用 `SELECT FOR UPDATE`,可以通过指定 `OF table.column` 来锁定特定表的行,否则将锁定所有涉及表的相关行。 例如: ```sql SELECT t1.id, t2.info FROM test_table1 t1, test_table2 t2 WHERE...