InnoDB: Lock & amp; Transaction, innodblock

InnoDB: Lock & Transaction, innodblock

InnoDB is an Engine that supports transactions. To ensure ACID of transactions, you must use Lock. Just like programming in Java, to ensure the thread security of data, the Lock will inevitably be used. Understanding Lock and Transaction can help with SQL optimization and deadlock analysis.

• 1. Lock
  • 1.1 Shared Lock & Exclusive Lock
  • 1.2 Intention Lock
  • 1.3 Index Record Lock
  • 1.4 Gap Lock
  • 1.5 Next-Key Lock
• 2. Transaction
  • 2.1 possible problems in the transaction process
  • 2.2 ACID
  • 2.3 MVCC
  • 2.4 current lock (2PL) Management
  • 2.5 isolation level
  • 2.6 SQL lock analysis
• 3. DeadLock

 

1. Lock

There are multiple types of locks in InnoDB, which are described below.

1.1 Shared Lock & Exclusive Lock

The shared lock (S) and exclusive lock (X) are row-level locks. That is to say, each row of record has a pair of S and an X lock. A shared Lock is a Read Lock. When a transaction is executed, if you want to Read a row of data, you must first hold the Read Lock (S) for the row of data ). The exclusive Lock is a Write Lock. When a transaction is executed, if you want to Write data (that is, update data, such as update and delete ), the write lock (X) of the corresponding row must be held first ).

In addition, Read Lock can be held by multiple transactions at the same time (actually the thread that executes these transactions), and Write Lock cannot. In terms of design, this is similar to ReadLock WriteLock in java.

That is to say, ReadLock can be held by multiple threads at the same time, and WriteLock can only be held by one thread.

When A thread A holds ReadLock (S), thread B can also hold ReadLock (S), but thread B cannot hold WriteLock (X ). At the same time, if thread A holds ReadLock and wants to hold WriteLock again, it must wait for other threads to release ReadLock without holding WriteLock.

When A thread A holds WriteLock, other threads cannot hold WriteLock or ReadLock, but it can still be read by itself (thread A) without having to hold ReadLock.

 

1.2 Intention Lock

Intention lock: the lock when you want to do something. This is a table lock. There are two types: Intention read lock (IS) and intention write lock (IX ).

To read records of certain rows, you must first hold the IS lock of the table. To modify or delete certain rows, you must first hold the IX lock of the table.

 

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

 

Intention lock has two advantages: 1. It can be quickly locked or not locked, because after the intention lock is enabled, a thread holds a read lock or an intent lock, if another thread wants to hold the write lock, it must first hold the intention write lock, and the intention write lock is easy to know that it cannot be obtained temporarily. If you do not use the intention lock, you must first find this record. After finding the record, you will find that the read lock of this row has been held by other threads and cannot be held by the write lock. In this way, the time for searching is wasted.

2. effectively avoid deadlocks.

 

But also because it is a table lock, the granularity is too large, resulting in low concurrency. When multiple transactions operate on a table at the same time, it becomes a serial operation.

 

1.3 Records Lock (Index Record Lock)

The record Lock is actually the index record lock, that is, the index row lock, not the data row Lock. Index Record locks are divided into S \ X locks, that is, the S and X locks are applied to the index row.

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; Ifc1Yesindex,Add X Lock to the index of c1 = 10 to prevent any other transaction from holding the index Lock of t. c1 = 10. In other transactions, inserting, update, and delete operations cannot obtain the index lock t. c1 = 10.

 

 

1.4 Gap Lock

The gap lock refers to the gap between two indexes. Each gap also has a lock called gap lock. Before the first data entry, there is also a gap after the last data entry, so there is also a gap lock.

Gap Lock can effectively avoid phantom read. FOR example, A transaction A executes SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 for update, and another transaction B needs to insert A row of c1 = 15. In this case, transaction B cannot get the gap lock because gaps locks from 10 to 20 are directly held by transaction. At this time, the transaction B cannot get the Gap because there is no record with c1 = 15.

 

Gap Lock can be explicitly disabled by setting the isolation level to READ_COMMMITED or by setting the system variable Innodb_locks_unsafe_for_binlog. In this case, the scan index is invalid and will only be valid during the foreign-key check. It can also be understood as: if a transaction is at the REPEATABE_READ isolation level, it will (possibly) Gap Lock. This is probably because there is a special case: If a select statement only queries a record from a table with a unique index, gap lock is not used because it is unnecessary.

SELECT * FROM child WHERE id = 100;

Id is an index and unique. In this case, when executing the preceding SQL statement, only one row of records can be found. Instead of holding the gap lock, index record lock is directly held.

 

 

1.5 Next-Key Lock

A next-key lock combines an index lock with its previous gap lock.

The default isolation level of InnoDB is REPEATABLE_READ. In this isolation level, InnoDB uses next-key when using index scan. The Next-key itself does not exist. It only represents the index lock and its previous gap lock.

 

 

2. What are the possible problems with the transaction database of Transaction2.1?

 

Dirty reads:(Dirty read) Transaction A reads data that has not been committed by another transaction B (uncommitted data added, deleted, and modified ). In this case, transaction A has A dirty read. Because it is possible that transaction B will not commit this data, then A is the read junk data.

Fuzzy or non-repeatable reads:When a transaction reads data that has previously been read again, it finds that other transactions have modified or deleted some data.

Phantom reads:(Phantom read) when re-executing a query in a transaction, it is found that other committed transactions have inserted new data that meets the query conditions.

 

 

2.2 ACID

Atomicity: a transaction is an atomic operation. modifications to its data are either executed in full or not.

Consistent: continuity and consistency. Make sure that the same query is executed twice in a transaction, and the execution result is the same.

Isolation: Isolation. Transactions provide different isolation levels. Isolation refers to the isolation between value transactions and transactions. The larger the isolation intensity, the fewer problems will occur.

Durable: persistent. After the transaction is completed, data changes are persistent. MySQL InnoDB uses undo to ensure durability.

 

2.3 MVCC

Before speaking about the isolation level, let's take a look at MVCC (Multi-Version-Concurrency-Control ).

Https://en.wikipedia.org/wiki/Multiversion_concurrency_control

The general meaning is: when the database adopts the MVCC scheme to design transactions, it is usually like this:

When a row is modified, the old data is not directly overwritten in the memory before submission. Instead, the old data is marked and a new data version is created. In addition, the data of the old version and the data of the new version are chained. In this way, each modified data has a history chain.

When deleting a row of data, the data will not be deleted from the memory before submission, but it is just a delete mark.

Here we can see that all the changed data has a history chain. That is to say, multiple versions of related Row are retained in the memory. If multiple versions are retained, the concurrency is greatly increased during concurrent reading.

This is also the biggest benefit of MVCC: Read is not locked and read/write is not conflicted. In OLTP applications with more reads and less writes, it is very important to read and write without re-reading. It will greatly increase the concurrency performance of the system. This is why all RDBMS currently support MVCC.

 

During running, different SQL statements use different data reading methods. According to different reading methodsSnapshotReadAndCurrentRead. As mentioned aboveRead is not locked, and read/write does not conflict with each other.SnapshotRead. For the current read (read the latest data), the lock is still required.

Snapshot read: Generally, a simple Select statement like this reads data from snapshot:

Select * from table where ?;

Of course, there are also exceptions. If a transaction is READ_UNCOMMITED, even a simple Select statement will adopt current read.

 

When reading from a temporary table (including a table generated by nested queries), the current read is used.

 

SnapshotHow to establish it?

For different isolation levels, the Snapshot creation method is also different. Here we will not describe it in detail, as described in the isolation level section.

 

The following example uses current read:

Select * from table where? Lock in share mode; // S Lock Select * from table where? For update; // X Lock and Index Lock Insert into table values (...); // X Lock Update table set? Where ?; // X Lock Delete from table where ?; // X Lock

 

 

2.4 Two-Phase Lock (2PL) Management

In MySQL, locks are processed in two phases, namely, locking and releasing.

In 2.3, Insert, update, and delete are all classified into the current read mode. Why?

Let's take a look at the Update execution process:

 

Update is divided into multiple rounds. Each round has a signal exchange process: current read, lock & return, update row, and success. This is also true for Delete.

 

Insert is slightly different because it first needs to find whether the same Key exists.

From this process, we can see that each round carries out a current read and locks until the read ends. After the Update is complete, the lock is not released immediately. Instead, the lock is executed until the transaction is committed. The same applies to insert and delete:

 

 

 

2.5 isolation level

READ UNCOMMITED: As the name suggests, uncommitted data can also be read. In fact, at this isolation level, the Select statement can read the current version of the relevant row (that is, the latest version) during execution, so some uncommitted transactions change the data, it can also be read. Therefore, dirty reads may occur.

At this isolation level, the current read is used, so no Snapshot is created.

 

READ COMMITED: Read committed data rows. Each time the committed data rows are read, the latest Snapshot is refreshed for each Select statement. So he will have the problem of non-repeated reading, which is inevitable. Phantom reading will also happen.

REPEATABLE READ: Repeatable read. To ensure repeatable reads in the same transaction, after a transaction is started, the first SQL statement to adopt the Snapshot method (this select SQL statement may not be in the current transaction) to trigger the creation of Snapshot. This is also the default isolation level of InnoDB.

 

Session A Session B   SET autocommit = 0; SET autocommit = 0; Time | SELECT * FROM t; | Empty set | Insert into t VALUES (1, 2 ); | V SELECT * FROM t; Empty set COMMIT;   SELECT * FROM t; Empty set   COMMIT;   SELECT * FROM t; --------------------- | 1 | 2 | ---------------------

 

SERIALIZABLE: serialization. For transactions at this level, if the client uses an autocommit transaction, the transaction is committed directly. Each SQL statement connected to the client is a separate transaction. If autocommit is not usedREPEATABLE READIsolation level, but it converts all simple Select statements to Select... lock in share mode, that is, convert them to current read.

 

 

Read data consistency and concurrent side effects Isolation level	Read Data Consistency	Dirty read	No repeated read	Phantom read
Uncommitted read (Read uncommitted)	Minimum level, only guaranteed Do not read physically corrupted data	Possible	Possible	Possible
Submitted (Read committed)	Statement level	No	Possible	Possible
Repeatable read (Repeatable read)	Transaction Level	No	No	Possible
Serializable (Serializable)	Highest level, transaction level	No	No	No

 

 

 

2.6 SQL lock analysis

·Select... from... Lock IN SHARE MODE(Also knownLockRead)

By default (REPEATABLE_READ), if index is used in this select SQL statement, shared next-key lock is added to the index record of all matching rows. If the index used in the select statement is a unique index, the shared index record Lock is added to the index record of the matched row.

For lower isolation levels of READ_COMMITED and READ_COMMITED, the shared index record is directly added.

 

·Select... from (Read without locking)

If the transaction executing this SQL statement adopts the SERIALIZABLE level, it is convertedSelect... from... Lock IN SHARE MODEThat is, it will become a lock read.

At other isolation levels, data is read from snapshot without locking.

 

·Select... from... FOR UPDATE

At the REPEATABLE_READ and SERIALIZABLE isolation levels, if index is used in this select SQL statement, exclusive next-key lock will be added to the matched rows. If the index used in the select statement is a unique index, the exclusive index record Lock is added to the index record of the matched row.

For lower isolation levels of READ_COMMITED and READ_COMMITED, the exclusive index record is directly added.

 

·UPDATE... WHERE...

At the REPEATABLE_READ and SERIALIZABLE isolation levels, if index is used in this select SQL statement, exclusive next-key lock will be added to the matched rows. If the index used in the select statement is a unique index, the exclusive index record Lock is added to the index record of the matched row.

 

For lower isolation levels of READ_COMMITED and READ_COMMITED, the exclusive index record is directly added.

 

 

When performing the update operation, if it is on the clustered index record (clustered index), all affected secondary indexes are implicitly locked. For example, if table test has a clustered index (a, B, c), index record is composed of a, B, and c. If you use: update test set d = '1' where a = '1' and B = '2' when updating the SQL statement, the exclusive lock is applied to all index records matched by B.

 

·DELETE... WHERE...

At the REPEATABLE_READ and SERIALIZABLE isolation levels, if index is used in this select SQL statement, exclusive next-key lock will be added to the matched rows. If the index used in the select statement is a unique index, the exclusive index record Lock is added to the index record of the matched row.

 

·INSERT

During the Insert operation, the system first checks whether there are matched indexes. If yes, shared index Lock is added to the matched index.

If no, the exclusive index lock will be added to a row to be inserted (no gap lock is applied to prevent impact on concurrent inserts ).

 

 

 

If the index is not used when executing the preceding SQL statements, a full table scan is triggered. During full table scan, the entire table is not locked.

Someone may ask? Why not apply a lock only to records that meet the conditions? This is determined by the implementation of MySQL. If a condition cannot be quickly filtered by the index, the storage engine locks all records and returns the results, which are then filtered by the MySQL Server layer. Therefore, all records are locked.

Note: in actual implementation, MySQL has some improvements. When the filtering conditions of MySQL Server are not met, the unlock_row method will be called, lock records that do not meet the conditions (Violation of the 2PL constraint ). This ensures that only the locks on the matching records are held at the end, but the locking operation of each record cannot be omitted.

 

Conclusion:If no index exists in the id column, the SQL statement performs a full scan of the clustered index for filtering, because the filtering is performed at the MySQL Server level. Therefore, the X lock is applied to each record, whether or not the conditions are met. However, for the sake of efficiency, MySQL has been optimized. For records that do not meet the conditions, the locks will be placed after judgment, and the final hold will be the locks on the records that meet the conditions, however, the locking/unlocking actions on records that do not meet the conditions will not be omitted. Optimization also violates the 2PL constraint.

 

 

3. DeadLock

When transaction A holds the lock of row1, transaction B holds the lock of row2. Then transaction A wants to hold the Lock of row2, but at the same time, transaction B does not immediately release the lock of row2. In this case, the DeadLock error will be thrown in transaction.

 

 

Related configuration items:

-- Innodb_deadlock_detect: used to control the detection of deadlock. The default value is ON. If you want to disable it, set it to OFF.

-- Innodb_lock_wait_timeout: the time for a transaction to wait for a row to lock. If this time is exceeded, an Error is thrown and rollback is executed:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This configuration item is not used when innodb_deadlock_detect is enabled. It is used when innodb_deadlock_detect is disabled. This is because the Rollback is triggered immediately when a deadlock is detected.

 

When a deadlock occurs, the solution is as follows:

1) Find the relevant SQL

2) Explain SQL Execution Methods (primary key index, unique key scan, range scan, and full table scan)

3) analyze the cause of the deadlock Based on the transaction level and SQL Execution sequence.