mysql 加锁读Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
来源:互联网 发布:第五元素 知乎 编辑:程序博客网 时间:2024/05/17 09:01
InnoDB
支持两种类型的加锁读来保证读取得安全性:
1.SELECT ... LOCK IN SHARE MODE,当被读取的行加上了共享模式的锁,其他会话能够读取行,但是在事务提交之前不能修改这些行。如果这些行中的任意部分被另外一个没有提交的事务改变,你的查询将等待那个事务提交,并使用提交后的最新值。
2.
SELECT ... FOR UPDATE,对于查询遇到有索引的记录,
SELECT ... FOR UPDATE将对行及相关的索引加锁,等同于你对这些行发出了更新语句,其他事务被阻止更新这些行,被阻止SELECT ... LOCK IN SHARE MODE,被阻止在某些事务隔离级别中读取数据。一致性读忽略对读视图记录加任何锁Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applyingundo logs on an in-memory copy of the record.)。
原文:
14.2.5 Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
If you query data and then insert or update related data within the same transaction, the regularSELECT
statement does not give enough protection. Other transactions can update or delete the same rows you just queried.InnoDB
supports two types of locking reads that offer extra safety:
SELECT ... LOCK IN SHARE MODE
sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.For index records the search encounters,
SELECT ... FOR UPDATE
locks the rows and any associated index entries, the same as if you issued anUPDATE
statement for those rows. Other transactions are blocked from updating those rows, from doingSELECT ... LOCK IN SHARE MODE
, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)
These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these “pointer” values.
All locks set by LOCK IN SHARE MODE
and FOR UPDATE
queries are released when the transaction is committed or rolled back.
Locking of rows for update using SELECT FOR UPDATE
only applies when autocommit is disabled (either by beginning transaction withSTART TRANSACTION
or by setting autocommit
to 0. If autocommit is enabled, the rows matching the specification are not locked.
Usage Examples
Suppose that you want to insert a new row into a table child
, and make sure that the child row has a parent row in tableparent
. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table PARENT
and verify that the parent row exists. Can you safely insert the child row to tableCHILD
? No, because some other session could delete the parent row in the moment between yourSELECT
and your INSERT
, without you being aware of it.
To avoid this potential issue, perform the SELECT
using LOCK IN SHARE MODE
:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
After the LOCK IN SHARE MODE
query returns the parent'Jones'
, you can safely add the child record to the CHILD
table and commit the transaction. Any transaction that tries to read or write to the applicable row in thePARENT
table waits until you are finished, that is, the data in all tables is in a consistent state.
For another example, consider an integer counter field in a table CHILD_CODES
, used to assign a unique identifier to each child added to tableCHILD
. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD
table.
Here, 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 up in deadlock when it attempts to update the counter.
To implement reading and incrementing the counter, first perform a locking read of the counter usingFOR UPDATE
, and then increment the counter. For example:
SELECT counter_field FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE
reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQLUPDATE
would set on the rows.
The preceding description is merely an example of how SELECT ... FOR UPDATE
works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);SELECT LAST_INSERT_ID();
The SELECT
statement merely retrieves the identifier information (specific to the current connection). It does not access any table.
- mysql 加锁读Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
- 14.2.2.3 Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE) 锁定读(SELECT ... FOR
- select ... for update& lock in share mode加锁情况
- SELECT LOCK IN SHARE MODE and FOR UPDATE
- mysql lock in share mode 和 select for update
- mysql lock in share mode 和 select for update
- MySQL的锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
- 【Mysql行锁】SELECT...FOR UPDATE和SELECT ...LOCK IN SHARE MODE
- MySQL 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE区别
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MYSQL select for update/lock in share mode 对事务并发性影响
- SELECT ... FOR UPDATE LOCK IN SHARE MOD
- 深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE
- SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE locks在RR模式下可以看到最新的记录
- HackerRank------JavaScript编程训练
- Android程序反编译的方法
- 高性能WEB开发的性能调优(二)——开发
- Android的ADB工具使用
- Memchae
- mysql 加锁读Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
- 致过去的我
- 【正能量】感谢CSDN颁发准专家勋章
- EL表达式的逻辑运算符
- nyoj 题目10 skiing —— 南阳oj
- KVO/KVC
- Maximum Subarray
- 做互联网服务的一些心得碎语
- unix存储空间分配