Why does the database need a lock mechanism? What are the lock mechanisms? __ Database

"Why do you want to lock?"

A database is a shared resource that is used by a multi-user, such as a user table T_user, two people in front of the browser login to the same account, the phone number changed. When multiple users have concurrent access to the data, multiple transactions are generated in the database while the same data is being accessed. If the concurrency operation is not controlled, it may read and store incorrect data, destroy the consistency of the database (dirty read, not repeatable reading, phantom reading, etc.), may produce deadlock. To solve this problem, lock is a very important technology, and it is a good scheme to realize concurrency control of database. Simply put, when a transaction executing an SQL statement wants to manipulate the table record, make a request to the database, lock the recordset you are accessing, and no other transactions can update the data before the transaction releases the lock.

"What locks are there?"

Locks include row-level locks, table-level locks, pessimistic locks, optimistic locks

Row-level locks: a lock that prevents another transaction from modifying this line; Oracle automatically applies row-level locks when the following statements are used: INSERT, UPDATE, DELETE, SELECT ... For UPDATE [' columns] [wait n | NOWAIT]; SELECT ... The FOR UPDATE statement allows the user to lock more than one record at a time to update. Use a commit or rollback to release the lock. MySQL's InnoDB storage engine defaults to row-level locks. Features: Unlock large, lock slow, there will be deadlock, locking particle size is minimal, the probability of a lock conflict is the lowest, and the highest degree of concurrency. Suitable for a large number of updates by index of a small number of different data, but also concurrent query applications, such as some online transaction processing systems.

Table-Level Locks: 5 kinds

Row sharing (row SHARE) – prevents exclusive locking of tables, similar to row exclusivity, which distinguishes other transactions from any exclusive locks on this table. (excluding exclusive (exclusive))
Row exclusive (row EXCLUSIVE)-prohibits the use of exclusive and shared locks, other transactions can still concurrently execute queries, inserts, updates, deletes, or locks on data rows in the same table, but cannot have other exclusive locks (they are possible, not found to be helpful).
Shared Lock (SHARE)-lock table, read-only and write-only, multiple users can apply the lock on the same table at the same time, multiple transactions can be locked when the table is not in any DML operations, but only if only one transaction is locked to update the table When a table has been updated or specified to be updated (select for update), no transaction can add this lock.
Shared row Exclusive (SHARE row EXCLUSIVE) – more restrictions than shared locks, no use of shared locks and higher locks, only one transaction can be locked and updated when the table is not under any DML operations, and the book says other transactions can use the Select for Update locks the selected rows of data, but is not validated after the experiment.
Exclusive (EXCLUSIVE) – Restricts the strongest table locks, allowing only other users to query the table's rows. Prohibit modifying and locking tables

Row-level and table-level locks are based on the granularity of the lock, Row Records, tables are resources, and locks are on these resources. If the granularity is smaller (such as row-level locks), you can increase the system's concurrency but require a larger system overhead. Can affect performance, deadlock, because the number of small size of the operation of the lock will increase, if the effect on the table, a large size, small overhead, maintenance of less locks, will not appear deadlock, but concurrency is quite expensive, Because locking the entire table restricts access to other records in this table for other transactions.

Pessimistic Lock:

Pessimistic lock, as its name implies, is pessimistic about the data being modified by the outside world (including other transactions of the system, as well as transactions from external systems), and every time the transaction goes to manipulate the data, it assumes that other transactions will modify the data that needs to be accessed. So before the visit are required to lock, row locks, table locks, read locks, write locks, etc., are locked before doing the operation, therefore, in the whole process of data processing, will be in a locked state. Pessimistic lock implementation, often rely on the database to provide the lock mechanism (and only the database layer to provide the lock mechanism to truly ensure the exclusive access to data, otherwise, even in this system to achieve the lock mechanism, can not guarantee that the external system will not modify the data). A typical pessimistic lock call that relies on a database: SELECT * from account where name= "Erica" for update this SQL statement locks all records that meet the search criteria (name= "Erica") in the list. These records cannot be modified until this transaction is committed (the locks in the transaction are released when the transaction is committed).

Hibernate and Joys and Sorrows lock realization: Based on database lock mechanism

Query q=session.createquery ("select * from T_profit where amount>10000");

Q.setlockmode ("Profit", Lockmode.upgrade);//profit is the alias of the Profit class

List<profit> ps=q.list ();

Implementation of the Sql:select .... from T_profit where amount>10000 the pessimistic lock for Update.hibernate is implemented through a database for update.

Lockmode.none: no lock mechanism;

The pessimistic lock is automatically acquired when Lockmode.write:insert,update record;

Lockmode.read automatically acquire pessimistic lock when reading;

Lockmode.upgrade: Use database for UPDATE clause lock;

LockMode.UPGRADE_NOWAIT:oracle a specific implementation, lock with Oracle for UPDATE NOWAIT clause

Optimistic Lock:

Optimistic lock, and joys and sorrows in contrast, each time a transaction goes to manipulate data, it assumes that the other transaction does not modify the data that needs to be accessed, so it is not required to lock before the visit, only to determine if there is someone else modifying the data during the visit during the update modification operation. It applies to multiple-read application types, it's better to have a conflict when it's less expensive, which eliminates overhead and improves throughput; But if it's true that there's a lot of conflict, it's going to have to be judged every time it's retry, but it lowers performance, and this time it's better to lock up the joys and sorrows. Databases that provide similar write_condition mechanisms are in fact provided optimistic locks.

Most of its implementations are based on data version Versin recording mechanism. As an example:

1. There is a version field in the Profit statement T_profit, the current value is 1, and the Total Asset Balance field (balance) is $10000

2. Operator a reads out the version=1, deducting 2000,10000-2000=8000 from total assets.

3.A has not finished operation, at this time operator B also read out version=1, total assets minus 5000,10000-5000=5000.

4.A operation completed, the version plus 1, modified to 2, total assets minus 2000 after the submission of update database, updated successfully

5.B operation, also add version plus 1, modified to 2, the total assets minus 5000 after submitting the update database, this time found version has been 2, such as B modified after the version of 1, do not meet the optimistic lock policy: "The submitted version must have more than the current version of the record to execute." Therefore, the operation request of B is dismissed, thus avoiding the possibility that B will overwrite the result of a operation with the result of the old data modification of version=1. If there is no optimistic lock, that a minus 2000 after the remaining 8000, but the B operation is with 10000-5000 remaining 5000, if the submission of B success, the total assets balance is 5000, but the actual situation should be 8000-5000=3000. Total asset table records and actual expenditures are inconsistent.

Hibernate to optimistic lock implementation:

<class name= "Com.f.tprofit" table= "T_profit" optimistic-lock= "version" ></class>