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

"Why do you want to lock?"

A database is a shared resource used by multiple users, such as a user table t_user, and the person in front of two browsers logs in to the same account and changes the phone number. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data, destroy the consistency of the database (dirty read, non-repeatable read, Phantom reading, etc.), may produce deadlock. In order to solve this problem, locking is a very important technology, and it is a good scheme to implement the concurrency control of the database. Simply put, when a transaction that executes an SQL statement wants to manipulate the table record, make a request to the database, lock the recordset you access, and the other transaction cannot update the data until the transaction releases the lock.

"What locks are there?"

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

Row-level Lock: A lock that prevents another transaction from modifying this row; When you use the following statement, Oracle automatically applies row-level locks: INSERT, UPDATE, DELETE, SELECT ... For UPDATE [of columns] [WAIT n | NOWAIT]; SELECT ... The FOR UPDATE statement allows the user to lock multiple records at one time for updates. Use commit or rollback to release the lock. MySQL's InnoDB storage engine is a row-level lock by default. Features: Lock big, locking slow; there will be a deadlock, locking granularity is minimal, the probability of lock collision is the lowest, and the concurrency is the highest. Suitable for applications that have a large number of different data updates by index and concurrent queries, such as some online transaction processing systems.

Table level Lock: 5 kinds

Row SHARE-Disables exclusive locking of tables, similar to row exclusivity, except that other transactions can also add any exclusive lock on this table. (except for exclusive (exclusive))
Row EXCLUSIVE – Prohibit exclusive and shared locks, and other transactions can still execute queries, inserts, updates, deletions, or locks on rows of data in the same data table concurrently, but cannot have other exclusive locks (which are self-capable and do not find any use)
Shared Lock (SHARE)-lock table, read-only and write-only, multiple users can apply this lock on the same table at the same time, multiple transactions can be locked when the table is not in any DML operation, but only if only one transaction is locked can the transaction be updated on the table When a table has been updated or is specified to be updated (select for update), no transaction can be added to this lock.
Shared row Exclusive (SHARE row EXCLUSIVE) – more restrictions than shared locks, prohibit use of shared locks and higher locks, only one transaction can be locked when the table is not in any DML operation, can be updated, and the book says other transactions can use the Select for Update locks the selected data rows, but is not validated after the experiment.
Exclusive (EXCLUSIVE) – Restricts the strongest table locks, allowing only other users to query rows of the table. Prohibit modifying and locking tables

Row-level and table-level locks are distinguished by the granularity of the locks, Row Records, tables are resources, and locks are on these resources. If the granularity is small (such as row-level locks), can increase the concurrency of the system but requires a large system overhead, will affect performance, deadlock, because the size of the operation of the number of locks will increase, if the role on the table, large granularity, low overhead, maintenance of fewer locks, there will be no deadlock, but concurrency is quite expensive, Because locking the entire table restricts access to other records in the table by other transactions.

Pessimistic Lock:

Pessimistic lock, as its name implies, is pessimistic about the data being modified by the outside world (including other transactions in the system, as well as transactions from the External System), and every time the transaction operates the data, it is assumed that other transactions will modify the data that needs to be accessed. So before the visit all require lock, row lock, table lock, read lock, write lock, etc., are locked before doing the operation, therefore, in the whole process of data processing, will be locked state. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no 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 by the outside world before the transaction commits (the locks in the transaction are freed during transaction commits).

Hibernate Joys and Sorrows lock implementation: 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 ();

Execution of Sql:select .... A pessimistic lock from the t_profit where amount>10000 for Update.hibernate is implemented by the database for update.

Lockmode.none: no locking mechanism;

Lockmode.write:insert,update automatically obtain pessimistic lock when recording;

Lockmode.read automatically acquires pessimistic lock when reading;

Lockmode.upgrade: Use the database's for UPDATE clause to lock;

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

Optimistic Lock:

Optimistic lock, and joys and sorrows lock in contrast, each time the transaction goes to manipulate the data, it assumes that the other transaction does not modify the data that needs to be accessed, so it does not require a lock before access, but only when the update is being modified to determine if there are other people modifying the data during the visit. It is suitable for multi-read application types, the conflict really occurs when the time is relatively small, so that the cost of overhead, can improve throughput, but if it is really often to conflict, that every time to judge the retry, but the performance of reducing, this time joys and sorrows lock is better. Databases that provide a similar write_condition mechanism are actually the optimistic locks provided.

Most of its implementations are based on the 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 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, gross position assets minus 2000 after the submission of the update database, updated successfully

5.B operation, also add version plus 1, modified to 2, gross position assets minus 5000 after the submission of the update database, this time found that version is 2, such as the B modified with 1 version, not satisfied with the optimistic locking policy: "Committed version must be greater 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 the a operation with the result of the version=1 old data modification. If there is no optimistic lock, that a minus 2000 after the remaining 8000, but B operation is 10000-5000 remaining 5000, if the submission of B is successful, the total asset balance is 5000, but the actual situation should be 8000-5000=3000. There is a discrepancy between the total asset table record and the actual expenditure.

Hibernate's implementation of optimistic locking:

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

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