[Mysql] pessimistic lock, mysql

[Mysql] pessimistic lock, mysql
Mysql locks

In a concurrent environment, Dirty Read, Unrepeatable Read, Phantom Read, and Lost update may occur, therefore, mysql introduces many lock concepts.

MySQL InnoDB locks data rows in four types: Shared locks (read locks, S locks), exclusive locks (write locks, X locks), and intention shared locks (IS locks) and intention exclusive lock (IX lock), support three row lock methods:

• Record Lock): The lock is directly added to the index record.
• Gap Lock): The lock can be added between two index records or the space before or after the first index record.
• Next-Key LockThe combination of row locks and gap locks is called Next-Key Lock.

By default, InnoDB works inRepeatable readAt the isolation levelNext-Key LockTo effectively preventPhantom read.Next-Key LockIt is a combination of row locks and gap locks. When InnoDB scans index records, it first adds the selected index recordsRecord Lock), And then add the gap between the two sides of the index recordGap Lock).If a gap is locked by transaction T1, other transactions cannot insert records in this gap..

• At the Repeatable read level, InnoDB locks the Index in the Next-Key Lock mode; At the read-committed level, InnoDB locks the Index in the Index-Record Lock mode.
• If the index to be locked is not a clustered index, the clustered index pointed to by the locked index and other indexes pointing to the same clustered index will also be locked.
• SELECT * FROM... lock in share mode adds a shared LOCK to the index; SELECT * FROM... for update adds an exclusive LOCK to the index.
• SELECT * FROM... non-blocking read, (except for the Serializable level) will not lock the index. At the submitted level, the latest and valid versions of the query records are always queried. At the repeatable level, the version of the first query is remembered, and the later query is based on this version. The exception is that at the serialization level, the index will be added with a share Lock in the Next-Key Lock mode.
• UPDATE... WHERE and DELETE... WHERE apply an exclusive lock to the index.
• Insert into... apply an exclusive Lock to the Index using Index-Record Lock.

What is a pessimistic lock?

Pessimistic lock refers to the conservative attitude towards data being modified by the outside world (including other current transactions of the system and transaction processing from the external system). Therefore, during the entire data processing process, when data is locked, consistent read locking is required to ensure transaction isolation. The lock is applied when data is read. Other transactions cannot modify the data. The lock is also required to modify and delete data, and other transactions cannot read the data.

Pessimistic locking reflects a cautious attitude. The process is as follows:

• Try to add exclusive locking to the record before modifying any record)
• If the lock fails, the record is being modified. The current query may have to wait or throw an exception.
• If the lock is successful, you can modify the record and unlock the record after the transaction is completed.
• Other operations that modify or apply exclusive locks to the record will wait for us to unlock the record or directly throw an exception.

Pessimistic locks are indeed rigorous, effectively ensuring data consistency. There are many mature solutions for C/S applications. However, his shortcomings are as obvious as their advantages.

• Pessimistic locks apply to reliable persistent connections such as C/S applications. It is not applicable to HTTP connections of Web applications.
• The use of the lock means performance loss, especially in the case of high concurrency and long lock duration. The performance bottleneck of Web applications is mostly in the database, and pessimistic locks are used to further tighten the bottleneck.
• It is difficult to design and implement the unlock mechanism when an exception is terminated, and the cost is high.
• Under the rigorous design, there may be inexplicable and hard-to-be-discovered deadlocks.

Automatic submission

MySQL runs in autocommit mode. This means that after executing a statement for updating (modifying) The table, MySQL immediately updates the table to the buffer, and the record lock is released. Therefore, if the transaction needs to execute multiple Update (modify) statements, the first update statement is executed without a lock. This will cause the transaction to become invalid and data consistency to be damaged.

mysql> select @@autocommit;+--------------+| @@autocommit |+--------------+|            1 |+--------------+

Disable Automatic submission

set autocommit=0;  or[mysqld]  init_connect='SET autocommit=0' ;

To avoid this problem, close autocommit and execute the commit statement to commit the transaction.

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

Note:

1. You cannot use "Disable autocommit" as the default setting. Otherwise, the query operation performed on the innodb table will always lock the table because no commit or rollback is executed! Therefore, you can only disable autocommit locally when necessary, and enable autocommit after the operation is complete.

2. the permission to connect to mysql cannot be greater than the permission of the user who starts mysql. Otherwise, init_connect = 'set autocommit = 0' will not start or report any error.

If a user has SUPER privilege, init_connect will not execute, (otherwise if init_connect will a wrong query no one can connect to server ).
Note, if init_connect is a wrong query, the connection is closing without any errors and next command will clause 'lost connection' error.

Example

To use a pessimistic lock, we must disable the automatic submission attribute of the mysql database, because MySQL uses the autocommit mode by default. That is to say, after you perform an update operation, MySQL will submit the result immediately.

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:

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select age from users where id =1 for update;+-----+| age |+-----+|  24 |+-----+1 row in set (0.00 sec)mysql> update users set age = 25 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 users where id =1 for update;ERROR 1205 : Lock wait timeout exceeded; try restarting transactionmysql> update users set age = 22 where id =1;ERROR 1205 : Lock wait timeout exceeded; try restarting transaction

Connect B will be executed only after connect a is submitted. Otherwise, it will wait forever.

IN a transaction, only SELECT... for update or lock in share mode will be executed after other transactions are completed. Generally, SELECT... is not affected.

MySQL select... For update: Row Lock and Table Lock　

As we mentioned above, select... For update locks the data, but we need to pay attention to some Lock levels. MySQL InnoDB uses Row-Level Lock by default. Therefore, only the primary key/index is specified explicitly, mySQL will execute Row lock (only Lock selected data), otherwise MySQL will execute Table lock (Lock the entire data form)

If a condition cannot be quickly filtered by indexes, the storage engine locks all records and returns the results. Then, the MySQL Server layer filters the results. However, during actual use, mySQL has made some improvements. When the filtering conditions of MySQL Server are not met, the unlock_row method will be called to release the records that do not meet the conditions (Violation of the constraints of the second lock protocol ). This ensures that only the locks on the matching records are held at the end, but the locking operation of each record cannot be omitted. It can be seen that even MySQL will violate the specifications for efficiency.

This situation also applies to the default isolation level RR of MySQL. Therefore, when you batch modify a table with a large amount of data, if you cannot use the corresponding index, MySQL Server will be particularly slow in data filtering, although some rows of data are not modified, they are locked.

Advantages and disadvantages

Pessimistic concurrency control is actually a Conservative policy of "getting the lock first and then accessing", which guarantees data processing security. However, in terms of efficiency, the locking mechanism can cause additional costs for the database and increase the chance of deadlock;

In addition, because there is no conflict in read-only transaction processing, and there is no need to use the lock, this can only increase the system load; it also reduces the concurrency, if a transaction locks a row of data, other transactions must wait until the Transaction Completes processing to process that row of data.

The pessimistic defect is that whether it is a page lock or a row lock, the lock may take a long time, which may limit the access of other users for a long time, that is, the concurrent access of the pessimistic lock is not good.

Optimistic locks hold that the probability of other users attempting to change the object you are changing is very small, so optimistic locks do not lock the object until you are about to commit the change, it is not locked when you read or change this object. It can be seen that optimistic locks take less time to lock than pessimistic locks. Optimistic locks can achieve better concurrent access performance with a larger lock granularity.

 

References

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