MySQL Lock process detailed (4)-select for Update/lock in share mode affects transactional concurrency

Select for Update/lock in share mode affects transactional concurrencyconcurrency understanding of transactions

Transactional concurrency, a cursory understanding of the number of transactions that can be performed per unit of time, is a common unit of TPS (transactions per second).

That in the case of data volume and business operations, the common increase in transaction concurrency is the main consideration of what are the points?

1. Increase the processing power of the server to shorten the processing time of the transaction.

This not only speeds up the execution time of the transaction, but also reduces the execution time of other transactions waiting for the transaction to execute.

2. Try to control the SQL action statements involved in the transaction to a reasonable extent, in other words, do not let a transaction contain too many or too few operations.

In a busy business situation, if a single transaction operation has too many tables or rows of data, the other transaction may be waiting for the transaction to commit or rollback, which will result in a decrease in the overall TPS. However, it is not realistic if each SQL statement is a transaction. As a result, some businesses themselves require multiple SQL statements to form a transaction (such as the operation of multiple tables for remittances); and secondly, each SQL requires a commit, and if innodb_flush_log_at_trx_commit=1 in MySQL, it causes Redo log refreshes too frequently and is not conducive to an increase in the overall number of transactions (IO Throttling is also an important factor to consider).

3. In the operation, as far as possible to control the size of the lock, you can use a small lock granularity as far as possible with the size of the lock, after the lock resources to remember to immediately release, to avoid the back of the transaction waiting.

However, in some cases, because of business needs, or to ensure the consistency of data, it is necessary to increase the granularity of the lock, this time is the following several cases.

Select for Update Understanding

The purpose of the Select Col from where Where_clause for update is to execute the SELECT query statement with an exclusive lock (X lock) for the corresponding index access entry, meaning that the corresponding lock for this statement is equivalent to the one that was brought by the update. Effect.

So why does this grammar exist? There must be a need for this way of existence!! Take a look at the following case description:

Case 1:

Prerequisites:

MySQL isolation level repeatable-read ,

Transaction 1:

Build tables: CREATE table ' lockt ' (  ' id ' int (one) not null,  ' col1 ' int (one) default NULL,  ' col2 ' int (one) default null,
   
    primary key (' id '),  UNIQUE key ' Col1_ind ' (' col1 '),  key ' Col2_ind ' (' col2 ')) Engine=innodb DEFAULT charset= UTF8 inserting data ..... Mysql> set autocommit=0; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |   | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec)
   

Then another transaction 2 took the following actions:

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |   | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec) mysql> Update lockt set  col2= 144  where col2=14;  Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0mysql> commit; Query OK, 0 rows Affected (0.00 sec)

Result: You can see that transaction 2 changes the col2=14 column to col2=144.

However, when transaction 1 continues to execute, it is not aware that the lockt has changed, see Transaction 1 continue with the following:

Mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |   | |  8 |    8 |   |+----+------+------+6 rows in Set (0.01 sec) mysql> Update lockt set  col2=col2*2  where col2=14;    Query OK, 0 rows Affected (0.00 sec) rows matched:0  changed:0  warnings:0mysql> commit; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |  144 | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec)

results: transaction 1 clearly look at the existence of col2=12 data, but after the update, unexpectedly not only did not change the value of the col2=28 he wanted, but became COL2=144!!!!

This is not allowed in some business situations, because some businesses want me to pass select * from Lockt; The data queried is the most recent data that is actually stored in the database, and no other transactions are allowed to be modified. ( This requirement is very domineering, but I like it.) )

This is a very good situation. Please refer to the following case 2 for specific details:

Case 2:

MySQL condition and case 11 kind.

Transaction 1 Operations:

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Select * from Lockt where col2=20 for update;+----+------+------+| ID | col1 | col2 |+----+------+------+|  8 |    8 |   |+----+------+------+1 row in Set (0.00 sec)

Transaction 2 Operations:

Mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |  144 | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec) mysql> Update lockt set  col2=222  

Note: Transaction 2 is executing update lockt set col2=222 where col2=20; , you will find that the SQL statement is blocked, why do you find this situation?

Since the select * from Lockt of the transaction 1 where col2=20 for update; The statement will lock the entry for the COL2=20 index (in some cases, the index entry for the range is locked and not discussed for the moment.) ), then transaction 2 sees all the data, but when it wants to modify the row data of the col2=20, transaction 1 can only say "impossible and not allowed".

Only after transaction 1 commit or Rollback, transaction 2 will be able to modify this row of col2=20 data.

Summarize:

This is the use of select for update, in order to avoid the data you see is not the most recent data stored in the database and see the data can only be modified by itself, need to use for update to limit.

Select Lock in Share mode understanding

If you look at the previous select for update, you can well understand the select lock in Share mode, where the share mode clause is used to add a share lock to the found data, which means that other transactions can only These data perform simple select operations and are not capable of DML operations.

What is the real difference between it and the for update on the reference scene?

Lock in Share mode does not have the for update so overbearing, so it sometimes also encounter problems, see case 3

Case 3:

MySQL environment and Case 1 similar

Transaction 1:

Mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |  144 | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec) mysql> Select * from Lockt where col2=20 lock in share mode;+----+----- -+------+| ID | col1 | col2 |+----+------+------+|  8 |    8 |   |+----+------+------+1 row in Set (0.00 sec)

Transaction 2 Then start operation

Mysql> SELECT * FROM lockt;+----+------+------+| ID | col1 | col2 |+----+------+------+|  1 |    1 |    1 | |  2 |    2 |    3 | |  5 |    5 |    5 | |  6 |    6 |    9 | |  7 |    7 |  144 | |  8 |    8 |   |+----+------+------+6 rows in Set (0.00 sec) mysql> Select * from Lockt where col2=20 lock in share mode;+----+----- -+------+| ID | col1 | col2 |+----+------+------+|  8 |    8 |   |+----+------+------+1 row in Set (0.01 sec)

The back of the comparison of the pain of the scene appeared, when the transaction 1 want to update col2=20, he found block live.

mysql> Update lockt set col2=22 where col2=20;

Explanation: Because transaction 1 and transaction 2 have a share lock on the line, transaction 1 thinks that only one person on the S lock, so when the transaction to modify the time to find that can not be modified, in this case, the transaction 1 need to use the FOR UPDATE clause to be constrained, instead of using for share To use.

possible scenarios and impact on performance

Usage scenarios:

1. SELECT * * * * for update usage scenarios

The FOR UPDATE clause is required to make sure that the data you find is up-to-date and that the data is only allowed to be modified by itself.

2. SELECT * * * lock in Share mode usage scenario

To ensure that the data you find is not being modified by other transactions, that is, ensure that the data found is up-to-date, and that no one else is allowed to modify the data. But you do not necessarily have the ability to modify the data ( such as a, a and a have a lock, a changes the data, because B also hold the lock, a cannot be submitted until the timeout ), because it is possible that the other transactions on the data used in share mode S lock.

Performance impact:

The Select FOR UPDATE statement, which is equivalent to an UPDATE statement. In a busy business situation, if the transaction does not have a timely commit or rollback may cause other transactions to wait for a long time, thus affecting the database's concurrent use efficiency.

The select lock in Share mode statement is a feature that gives a shared lock (S-lock) to the data being found, which allows other transactions to also lock on the data, but is not allowed to modify the data. If not timely commit or rollback can also cause a lot of transaction waiting.

The difference between for update and lock in Share mode: The previous one is an exclusive (X) lock, and once a transaction acquires the lock, the other transaction is unable to perform a for update on the data, and the latter is a shared lock, and multiple transactions can simultaneously execute lock on the same data In share mode.

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------

Example experiment

1.lock in Share mode deadlock condition

A	B
SET SESSION TRANSACTION Isolation level repeatable READ;
SET autocommit=0;
BEGIN	BEGIN
SELECT * FROM Test	SELECT * FROM Test
SELECT * FROM Test WHERE a= ' 1 ' LOCK in SHARE MODE;	SELECT * FROM Test WHERE a= ' 1 ' LOCK in SHARE MODE;
UPDATE test SET b=111 WHERE a= ' 1 '
	UPDATE test SET b=222 WHERE a= ' 1 '
	B is locked, the S lock is released, so a succeeds.
	UPDATE test SET b=222 WHERE a= ' 1 '
COMMIT
	A after commit B only update success, because after the deadlock B lost the lock, a only success
SELECT * FROM Test
	SELECT * FROM Test
	COMMIT
SELECT * FROM Test	SELECT * FROM Test

Example 2 for Update lock

A	B
SET SESSION TRANSACTION Isolation level repeatable READ;
SET autocommit=0;
BEGIN	BEGIN
SELECT * FROM Test	SELECT * FROM Test
SELECT * FROM Test WHERE a= ' 1 ' for UPDATE;
	SELECT * FROM Test WHERE a= ' 1 ' for UPDATE;
COMMIT	COMMIT
Forupdate only one person to get the lock, is the X (exclusive) lock

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------

Reference: http://www.cnblogs.com/liushuiwuqing/p/3966898.html

MySQL Lock process detailed (4)-select for Update/lock in share mode affects transactional concurrency