Knowledge about locks in MySQL

Database Lock: The reason for database locks is to handle concurrency problems.

Concurrency control generally employs three methods, namely optimistic lock and pessimistic lock, and time stamp.

Optimistic lock that when a user reads data, others will not write their own reading of the data, is not doing any action. Pessimistic lock is just the opposite, feel that they read the database, others may just write their own just read the data, in fact, is holding a more conservative attitude, pessimistic lock is in the reading of data, in order to not let others modify their own reading data, will first read the data of their own lock, only their own data read, To allow others to modify that part of the data, or in turn, is to modify a piece of data, do not allow others to read the data, only to wait for their entire transaction submitted, only to release their locks, to allow other users to access that part of the data. The pessimistic lock is said to add "lock".

Pessimistic locks are divided into:

Read lock/share lock: Shared lock, multiple clients can read one resource simultaneously, complementary interference.
Write lock/Lock: One lock blocks other write and read locks, making sure that only one client changes the write through one data at a time.

a timestamp is a separate column of timestamps in the database table, such as "TimeStamp", each time it is read out , read the field, and when you write it back, add 1 to the field, and before you commit, compared to this field of the database, if it is larger than the value of the database, it is allowed to be saved, otherwise it is not allowed.

table Lock is a less expensive strategy that locks the entire table. Table-level locking is supported for both MyISAM and InnoDB. A user writes to a table, requires a lock first, and blocks all read operations on the table by the user. modifications to the table in the database, such as ALTER TABLE, use the table lock to lock the entire table, so this type of operation should be used sparingly in the database.

Row Lock row locks efficiently support concurrency and, of course, lock overhead is the largest. The MySQL InnoDB engine implements a row lock that, when the user writes the data, locks only the rows of data that need to be manipulated, which is better than the table lock concurrency.

InnoDB Support Transactions,

a transaction is one of the important attributes of a database that differs from the file system. the transaction complies with the acid characteristics. atomicity: atomicity means that database transactions are inseparable units of work. The entire transaction succeeds only if all database operations in the database transaction are executed successfully . If any one of the SQL statements in the transaction fails to execute, the SQL statement that has been executed must also be revoked, and the database state is returned to the state before the transaction was executed.

Consistency: The integrity constraints of the database were not compromised before and after the transaction was executed.

Isolation: The Impact of a thing is not visible to other transactions before it is committed (implemented by locks)

Persistence: Once a transaction is committed, the result is permanent.

Atomicity, consistency, and persistence are accomplished through Redo,undo.

Problems associated with a concurrent transaction:

• Update lost (Lost Update): When two transactions T1 and T2 read into the same data to make modifications concurrent execution, T2 T1 or T1 the T2 to overwrite the results, resulting in data loss update problems, resulting in inconsistent data.
• Dirty Read (Dirty Reads): Transaction T1 updated data r, transaction T2 read the updated data R, transaction T1 for some reason was revoked, restore data R. This way, the data read by T2 is different from the content in the database.
• non-repeatable read (non-repeatable Reads): One transaction reads the same row of data two times, with different results.
• (1) Transaction T1 reads the data r, the transaction T2 reads and updates R, and when the transaction reads R again, the value of two reads is different.
• (2) Two queries were made during the transaction operation, and the result of the second query contained data that did not appear in the first query or the data that was missing the first query, which is called Phantom Reading.

Gap Lock (Next-key Lock) When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the index entry for the existing data that matches the condition, and for records that do not exist in the condition range of the key value, it is called "gap".    InnoDB will also lock this "gap", which is not a so-called gap Lock (Next-key lock). For example, if there are only 101 records in the EMP table, the value of the Empid is,..., 100,101 respectively, the following SQL: SELECT * FROM emp WHERE empid > 100 FOR UPDATEis a retrieval of a range condition, innodb not only locks the records that meet the conditional Empid value of 101, but also locks the "gap" of Empid greater than 101 (which do not exist). InnoDB The purpose of the use of Gap locks, on the one hand is to prevent Phantom reading to meet the requirements of the relevant isolation level, for the above example, if you do not use a gap lock, if other transactions inserted empid greater than 100 of any record, then this transaction if the above statement execution, a phantom reading will occur, on the other hand, is to meet the needs of its recovery and replication.    The effect of its recovery and replication on mechanisms, and the use of gap locks at different isolation levels innodb. Obviously, when using scope conditions to retrieve and lock records, the INNODB locking mechanism blocks concurrent insertions that match the key values within the range, which often results in severe lock waits. Therefore, in the actual development, especially the concurrent insertion of more applications, we want to optimize the business logic, as far as possible to use equality conditions to access the updated data, to avoid the use of scope conditions.dead Lock

Two users lock one resource, and then both sides wait for both parties to release the locked resource, resulting in a lock request loop, resulting in a deadlock. Deadlocks often occur in row-level locks.

How do I resolve a deadlock?

In the transaction management and locking mechanism of INNODB, there is a mechanism dedicated to detecting deadlocks. When detecting a deadlock, InnoDB chooses the smaller of the two transactions that generated the deadlock to complete the rollback.

Knowledge about locks in MySQL