Row and table locks in the database

One, the problem of transaction concurrency scheduling

1. Dirty read: A transaction reads the change data that the B transaction has not yet committed, and operates on this data basis. If the B transaction is rolled back, then the data read by a transaction is not legal at all, called Dirty read. In Oracle, dirty reads do not occur due to version control.
2. Non-REPEATABLE READ: A transaction reads the changed (or deleted) data that the B transaction has committed. For example, a transaction reads the data for the first time, then the B transaction changes the data and commits, the a transaction reads the data again, and the data is read differently two times.
3. Phantom READ: A transaction reads the new data that the B transaction has submitted. Note and non-repeatable read differences, here is the new, non-repeatable read is changed (or deleted). The two scenarios are different, and for non-repeatable reads, only row-level locks are required to prevent the record data from being changed or deleted, but a table-level lock must be added to the Phantom read to prevent the addition of a single piece of data in the table.
4. First category missing update: When a transaction is revoked, the data of the committed B transaction is overwritten.
5. The second category loses the update: When a transaction commits, the data of the committed B transaction is overwritten.

Level three lockdown protocol

1. First-level blocking protocol: in transaction T if there is a write operation to the data r, it must be X-locked in this transaction before the initial read of R, until the end of the transaction is released. The end of the transaction consists of a normal end (COMMIT) and an abnormal end (ROLLBACK).
2. Secondary blocking protocol: first-class blocking protocol plus transaction T before reading the data r must be a lock on it, read the rear can release S lock.
3. Level three blocking protocol: first level blocking protocol plus the transaction T must be locked before the data R is read, until the end of the transaction is released.

Visible, level Three lock operation one is worse than one (satisfying the advanced lock must satisfy the low-level lock). But there is a very deadly place where a first-class lock protocol is to be read with an X lock until the end of the transaction. Almost the entire transaction is written and locked, very inefficient. Level three blocking protocol is only a theoretical thing, and the actual database often uses another method to solve the transaction concurrency problem. Ii. level of isolationMySQL uses intent locks (another mechanism) to solve transactional concurrency problems, and in order to differentiate the blocking protocols, a new conceptual isolation level is included: READ UNCOMMITTED, read Committed, Repeatable read, Serializable.　　　　　　　MySQL generally defaults to repeatable Read. To summarize, repeatable read resolves dirty reads and non-repeatable reads, but does not resolve lost modifications. third, the MySQL row lock and table lockThe following is a brief introduction to row and table locks.

• Table-level Lock: Locks the entire table for each operation. The cost is small, lock fast, no deadlock, locking granularity is large, the probability of lock conflict is highest, the concurrency is the lowest;
• Row-level Lock: Locks one row of data per operation. The cost is big, the locking is slow; there will be a deadlock, the lock granularity is minimal, the probability of lock conflict is the lowest, and the concurrency is the highest;
• Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

1, the MyISAM locka little mention of MyISAM, only said and InnoDB different. A. MyISAM only table lock, lock is divided into read lock and write lock. B. No transaction, no need to consider concurrency problem c. Because the granularity of the lock is too large, the number of queries to wait for when the table is written concurrently is high. 2. InnoDB Lock and table lockThere is no specific syntax.　　The row lock for MySQL is reflected by the index. If only the index entry is used in the Where condition, the row lock is added, otherwise the table lock is added. such as primary key index, unique index and clustered index.　　If the where of SQL is a full-table scan, there is no need to add locks. The effect of row and table locks on our programming is to try to use only the index entries in the Where, otherwise it will touch the post lock. 3, lock and UnlockIn InnoDB, Select,insert,update,delete and other statements are automatically unlocked when they are executed.　　The lock of Select is usually released after execution, and the X lock of the modified operation is held to the end of the transaction, which is much more efficient. MySQL also gives users the opportunity to locking, as long as the SQL is added after the lock in SHARE MODE or for UPDATE shared lock (S): SELECT * FROM table_name WHERE ... Lock in SHARE MODE exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE It is important to note that the lock is not released by itself, so the lock is held until the end of the transaction. Iv. Resolve lost changes-optimistic and pessimistic locksLocking is to resolve lost modifications. If there is only one sentence of SQL in a transaction, the database is guaranteed to be concurrency-safe. The feature of missing changes is to read the P data first in a transaction and then write the P data.　　The so-called lost modification, typically a transaction has two operations, the latter one depends on the previous operation, after which the latter operation overrides the write operation of the B transaction. If a transaction reads and writes the same data, a loss modification can occur and some processing is done. The following is a brief introduction to optimistic and pessimistic locks. The concept of pessimistic locking and optimistic locking:Pessimistic lock (Pessimistic Concurrency CONTROL,PCC): It is assumed that concurrency conflicts will occur and that all operations that may violate data integrity are masked.　　Optimistic lock (Optimistic Concurrency CONTROL,OCC): Assuming no concurrency conflicts occur, check for data integrity violations only when the operation is committed.　　Optimistic and pessimistic locks can be used not only in databases, but also in threads.　　The flaw of the pessimistic lock is that whether it is a page lock or a row lock, the lock-up time can be very long, which may limit the access of other users for long periods of time, that is, pessimistic lock concurrency is not good. Optimistic locks do not resolve dirty reads, lock time is shorter than pessimistic lock (only in the execution of SQL when the basic lock guarantee isolation level), optimistic lock can use a larger lock granularity to obtain better concurrent access performance.　　But if the second user reads the object exactly before the first user commits the change, the database will find that the object has changed when he has made his own changes, so that the second user has to reread the object and make changes. It can be seen that optimistic locking is more suitable to solve the situation of very small conflict probability, while pessimistic lock is suitable for solving the situation of intense competition, using row locks, reducing the lock granularity to improve the concurrency processing ability, even if the time of adding locks is longer than the table lock. examples of pessimistic locksHere are just a few examples of pessimistic locks that address lost changes. The feature of missing changes is to read the P data first in a transaction and then write the P data. And the first-class lock protocol can solve the loss of modification, so if you write p in transaction A, we just need to add X lock in a before reading p. example of an optimistic lockOptimistic locking detects concurrency conflicts in two common ways:

1. Use data versions (version). On the P data (usually each row) plus the version field (int), a transaction reads the revision number at the same time as the read data P, detects whether the latest version number is equal to the version number previously taken out before modifying the data, and if so, modifies the version number +1; otherwise either rolls back or executes the transaction again. In addition, all modifications to the data P will have the version number +1. There is a very important point that the version number is used to see if the variable being read is not changed, not for the variable being written, but to prevent the dependent variable from being modified.
2. Use time stamp (TimeStamp). The procedure is similar to 1.

SummaryOptimistic locking is better suited for less concurrent competition, so it's best to have a conflict in 3-5 minutes. When the concurrency is 10 o'clock, you can obviously feel the optimistic lock is slower; it's just a read and write.　　Consider if there are 3 writes in a transaction, if each write is narrow escape, the transaction commit is more difficult, then it is more to consider whether to use optimistic lock. However, when the scale of the distributed database is large to a certain extent, but also said. A pessimistic lock-based distributed lock is unacceptable in performance overhead when the cluster is large enough (from hundreds of to thousands of units). So the current trend is that large-scale distributed databases tend to use optimistic locking to achieve external consistency.

Row and table locks in the database