"MySQL" About pessimistic lock

about the lock in MySQL

In a concurrent environment, there is the possibility of dirty reads (Dirty read), non-repeatable reads (unrepeatable read), Phantom reads (Phantom read), update loss (Lost update), etc., so MySQL introduces a lot of lock concepts

MySQL InnoDB A total of four types of locking data rows: Shared lock (read lock, S lock), exclusive (write lock, x Lock), intent shared lock (is lock), and intent Exclusive (IX Lock), supporting three line locking methods:

• row lock (Record Lock): The lock is added directly above the index record.
• Gap lock: Locks are added to a nonexistent free space, which can be between two index records or the space after the first index record or after the last index.
• Next-keyLock: The combination of row and gap locks is called Next-key lock.

REPEATABLE Read isolation level, and with Next-key Lock Locks the data rows in such a way as to prevent Phantom reads from occurring effectively. Next-key Lock is a combination of row and gap locks, so that when InnoDB scans index records, the selected index record is first added with a row lock . Then add the Gap lock to the gap on both sides of the index record. If a gap is locked by the transaction T1, other transactions cannot be inserted into the record in this gap .

• Under repeatable read level, InnoDB locks the index in Next-key Lock, and under Read committed level, InnoDB locks the index in the same way as Index-record lock.
• If the locked index is not a clustered index, the clustered index pointed to by the locked index and other indexes that point to the same clustered index are also locked.
• SELECT * FROM ... Lock in SHARE mode adds a shared lock to the index; SELECT * FROM ... The for update adds an exclusive lock to the index.
• SELECT * FROM ... Non-blocking reads, (except the serializable level) do not lock the index. At the Read committed level, the most recent, valid version of the record is always queried, and at the repeatable read level, the version at the time of the first query is remembered, and then the query is based on that version. The exception is at the serialization level, when the index is shared with Next-key lock.
• UPDATE ... WHERE vs. Delete ... Where adds an exclusive lock to the index.
• INSERT into ... Add an exclusive lock to an index in the form of Index-record lock

What is a pessimistic lock

Pessimistic lock refers to the data by the outside (including the current system of other transactions, as well as transactions from the external system) is conservative attitude, therefore, in the entire data processing process, will be locked, in the case of pessimistic lock, in order to ensure the isolation of the transaction, the need for consistency lock read. Lock when reading data, other transactions cannot modify this data. Locks are also added when modifying deleted data, and other transactions cannot read the data.

Pessimistic locking (pessimistic locking) embodies a cautious attitude. The process is as follows:

• Try to add an exclusive lock (exclusive locking) to the record before making any changes to it.
• If the lock fails, indicating that the record is being modified, the current query may have to wait or throw an exception
• If the lock is successful, the record can be modified and the transaction will be unlocked after it is completed.
• In the meantime, if there are other actions to modify or add exclusive locks to the record, it will wait for us to unlock or throw the exception directly.

Pessimistic lock is really rigorous, effectively ensure the consistency of data, in C/S application has many mature programs. But his shortcomings are as obvious as their merits.

• Pessimistic locks are suitable for reliable, continuous connections such as C/s applications. For HTTP connections to Web apps, this is not true
• The use of locks means the loss of performance, especially in cases of high concurrency and long lockout duration. Web application performance bottlenecks are more in the database, using pessimistic locks, further tightening the bottleneck
• The unlocking mechanism in the case of abnormal abort is cumbersome to design and implement, and the cost is very high.
• Not rigorous design, may produce inexplicable, not easy to find, the deadlock problem

Auto Commit

MySQL runs with autocommit mode. This means that when a statement is executed that updates (modifies) the table, MySQL immediately updates it to buffer and the record lock is released. So if a transaction is going to execute multiple update (modify) statements, then starting with the 2nd UPDATE statement is executed under the lock-free condition, which results in the transaction being invalidated and data consistency being compromised.

Mysql> SELECT @ @autocommit; +--------------+| @ @autocommit |+--------------+|            1 |+--------------+

Turn off auto-commit

Set autocommit=0;  OR[MYSQLD]  init_connect= ' SET autocommit=0 ';

The way to avoid this problem is to close autocommit and then commit the transaction by executing a COMMIT statement

$db->begin (); $db->query ("SET autocommit=0"); $db->commit ();

Attention:

1, "Close autocommit" cannot be the default setting, otherwise the query operation performed on the InnoDB table will be locked because no commit or rollback is executed! Therefore, you can only partially shut down the autocommit when needed and turn it on after the operation is complete autocommit

2, the access to the MySQL user can not be greater than the rights to start the MySQL user, otherwise init_connect= ' SET autocommit=0 ' will not function, will not report any errors

If a user has SUPER privilege, Init_connect would not execute, (otherwise if init_connect would a wrong query no one can conn ECT to server).
Note, if Init_connect is a wrong query, the connection was closing without any errors and next command would clause ' lost CO Nnection ' ERROR.

Examples of Use

To use pessimistic locking, we must turn off the auto-commit property of the MySQL database, because MySQL uses the autocommit mode by default, that is, when you perform an update operation, MySQL will immediately submit the result

mysql> Set autocommit = 0; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @ @autocommit; +--------------+| @ @autocommit |+--------------+|            0 |+--------------+1 row in Set (0.00 sec)

Connect A:

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Select age from the users where ID =1 for update;+-----+| Age |+-----+|  |+-----+1 Row in Set (0.00 sec) mysql> Update users set age = where id = 1; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0mysql> commit; Query OK, 0 rows affected (0.01 sec)

Connect B: When a is not submitted for a long time

Mysql> SELECT * from the users where ID =1 for update; ERROR 1205:lock wait timeout exceeded; Try restarting transactionmysql> update users set age = where id = 1; ERROR 1205:lock wait timeout exceeded; Try restarting transaction

Connect B will not execute until connect a is committed, otherwise it will wait

In a transaction, only select ... For UPDATE or lock in SHARE MODE will wait for other transactions to finish before executing, general Select ... is not affected by this

MySQL select...for Update's Row lock and Table lock　

As we mentioned above, using Select...for Update will lock the data, but we need to pay attention to some lock levels, MySQL InnoDB default row-level lock, so only "explicitly" to specify the primary key/index, MySQL will execute row lock ( Lock the selected data only), or MySQL will execute table lock (lock the entire data form)

If a condition cannot be quickly filtered through the index, the storage engine level will lock all records back and then be filtered by the MySQL server layer, but in actual use, MySQL has made some improvements in MySQL server filter conditions, found not satisfied, The Unlock_row method is called to release a record that does not satisfy the condition (which violates the constraint of the two-segment lock protocol). This ensures that only the locks that satisfy the condition record will be held at the end, but the lock operation of each record cannot be omitted. It can be seen that even MySQL is a violation of the norm for efficiency.

This also applies to MySQL's default isolation level RR. So for a large number of tables to do batch modification, if the corresponding index can not be used, MySQL Server filter the data is particularly slow, it will appear that although some rows of data is not modified, but they are still locked up the phenomenon

Pros and cons

Pessimistic concurrency control is actually a conservative strategy of "first fetch lock and then access", which guarantees the security of data processing. However, in terms of efficiency, the mechanism of handling lock-up will make the database incur additional overhead and increase the chance of deadlock;

In addition, in a read-only transaction because there is no conflict, there is no need to use locks, which can only increase the system load, but also reduce the parallelism, if a transaction locks a row of data, other transactions must wait for the transaction to be processed before processing that row of data

The pessimistic flaw is that whether it is a page lock or a row lock, the lock time can be very long, which may be longer than the time limit of other users access, that is, pessimistic lock concurrent access is not good

Optimistic locking holds the probability that other users are trying to change the object you are changing, so the optimistic lock locks the object until you are ready to commit the changes, and does not lock when you read and change the object. It can be seen that optimistic lock and lock time is shorter than pessimistic lock, optimistic lock may get better concurrent access performance with larger lock granularity.

Reference articles

Http://tech.meituan.com/innodb-lock.html
http://hedengcheng.com/?p=771
http://ouyanggod.iteye.com/blog/2166384

"MySQL" About pessimistic lock