MySQL lock mechanism

Usually the lock is divided into pessimistic lock and optimistic lock, so-called pessimistic lock is always wary of its people to operate, for fear of others and their own resources, so one up on the lock; and optimistic lock is the usual optimist think no one will and their own resources, as long as the rules to do nothing, is their own through the database set a database field to simulate the is a gentleman agreement, optimistic that everyone is a gentleman will abide by. Pessimistic lock read lock and write lock, whether it is read lock or write lock, do not affect the query, query everyone can execute and get results, the difference between the two locks is read lock can be shared lock can be held at the same time, we all hold the number waiting for the right to update data at any time, who deft on hand who first update, But the lock can only be one person at a time with the number, only such as the current user operation is completely completed, the other talent can get the number for update operations, as in the queue, lock also branch-level lock and the entire table lock, row-level lock is only the current ID specified row, and the whole table lock is directly locked on the table, there is The specific syntax in MySQL is as follows:

Read lock (also known as shared lock) is the query when you do not want the data to be modified by others, you can set a read shared mode lock, everyone can read, read data unrestricted, can hold read shared lock, but if one of the people did not commit the transaction, another person cannot modify the currently locked table, Of course, if no other person holds a read lock at the same time, it can modify the data only if it owns it, but if two people hold the read lock at the same time, then one party cannot modify the data at will, and the read shared lock syntax is the SELECT * from table name in the transaction in the share model

Write locks (also called exclusive locks), write lock can not be shared, as long as someone added a write lock, then other people can not do lock operation, even if it is a shared lock, anyway, the current row or table data lock can only one person hold this lock, other people can not be held at the same time, others want to modify the hold write lock, can only wait for a person to commit a transaction , or you have to wait all the time, even if not to get the lock, heat just want to modify the data also have to wait for a person to commit the transaction, but if only the query operation is not restricted

If you have not been able to wait for other people to commit the transaction, then it is possible to update the operation time-out, error, but the data is not a problem, but the update failed, then the code plus exception capture processing, in MySQL SQL Plus pessimistic lock is through the lack keyword, but in hibernate how to add pessimistic lock it? is when you call the Hibernate method by specifying the parameter Lockoptions This enumeration to let hibernate decide how to add the Lock keyword when generating SQL, as follows:

Hibernate is generated row-level locks, there is a where condition limit, only on the current row lock, the other rows are not locked, others can not lock the row data to modify or lock, so fine granularity division can improve the efficiency of MySQL execution, do not need a lock on the whole table, make everyone have to wait , just like a person to the toilet, as long as a pit on the line, but the whole toilet to a single, obviously unreasonable.

=====================================================

Optimistic lock: In fact, optimistic lock is not the database itself provides a lock mechanism, is a human simulation implementation, such as adding a field such as version, its value is the default increment of integers, the default value is 1, as long as a person came in and put the value on the previous basis plus 1, do not wait, But we all have to abide by an agreement is to submit before the database to determine whether version is consistent with its current version, if inconsistent then the data updated, and ensure consistency with the database data submitted, similar to the SVN control version conflict resolution

MySQL lock mechanism