InnoDB Lock problem

The biggest difference between InnoDB and MyISAM is two points: one is support transaction (TRANSACTION), and the other is row-level lock. There are many differences between row-level locks and table-level locks, and the introduction of transactions poses some new problems. Let's start with a little background, and then discuss InnoDB's locking problem in detail.

Background Knowledge

1. Transaction (Transaction) and its acid properties

A transaction is a logical processing unit consisting of a set of SQL statements that have the following 4 properties, typically referred to as the ACID properties of a transaction.

• Atomicity (atomicity): A transaction is an atomic manipulation unit whose modifications to the data are either all executed or not executed.
• Consistency (consistent): data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of the transaction to preserve the integrity of the data; At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must also be correct.
• Isolation (Isolation): The database system provides a certain isolation mechanism to ensure that transactions are performed in a "stand-alone" environment that is not affected by external concurrency operations. This means that the intermediate state in the transaction process is not visible to the outside, and vice versa.
• Persistence (Durable): After a transaction is complete, its modification to the data is permanent, even if a system failure occurs.
  

Bank Transfer is a typical example of a transaction.

2. Problems caused by concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources, improve the transaction throughput of database system, so as to support more users. However, concurrent transaction processing also brings some problems, including the following situations.

• Update lost (Lost update): When two or more transactions select the same row, and then update the row based on the value originally selected, a missing update problem occurs because each transaction is unaware of the presence of other transactions-the last update overwrites the updates made by other firms. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who last saved its change copy overwrites the changes made by another editor. This problem can be avoided if another editor cannot access the same file until one of the editors finishes and commits the transaction.
• Dirty Read (Dirty Reads): A transaction is modifying a record, before the transaction is completed and submitted, the data of this record is in an inconsistent state, then another transaction to read the same record, if not control, the second transaction read the "dirty" data, and then do further processing, An uncommitted data dependency is generated. This phenomenon is visually called "Dirty reading".
• Non-repeatable read (non-repeatable Reads): A transaction reads the previously read data at some time after reading some data, but finds that the data it has read has changed, or some records have been deleted! This phenomenon is called "non-repeatable reading".
• Phantom Read (Phantom Reads): A transaction re-reads the previously retrieved data in the same query condition, but finds that other transactions have inserted new data that satisfies its query criteria, which is called "Phantom Reading."
  

3. Transaction ISOLATION LEVEL

In the problems associated with concurrency transactions mentioned above, "update loss" is usually completely avoided. However, preventing updates from being lost cannot be solved by the database transaction controller alone, requiring the application to fix the necessary locks for the data to be updated, thus preventing updates from being lost should be the responsibility of the application. "Dirty reading", "non-repeatable reading" and "phantom reading" are all database read consistency problems, which must be solved by the database to provide a certain transaction isolation mechanism. The way the database implements transaction isolation is basically divided into the following two types.

• One is to lock the data before it is read, preventing other transactions from modifying the data.
• The other is to create a consistent data snapshot (Snapshot) of a data request point-in-time by a mechanism without any locks, and use this snapshot to provide a consistent read at a certain level (statement-level or transaction-level). From the user's point of view, it is as if the database can provide multiple versions of the same data, so this technique is called data multi-version concurrency control (multiversion Concurrency Control, abbreviated MVCC or MCC), also often called a multi-version database.
  

The stricter the transaction isolation of the database, the smaller the concurrency side-effect, but the greater the cost, because transaction isolation is essentially the "serialization" of transactions, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation, for example, many applications are not sensitive to "non-repeatable reads" and "phantom reads" and may be more concerned about the ability of data to be accessed concurrently.

In order to resolve the contradiction between "isolation" and "concurrency", Iso/ansi SQL92 defines 4 transaction isolation levels, each with different levels of isolation, different side-effects allowed, and applications can balance "isolation" and "concurrency" by selecting different isolation levels according to their business logic requirements. Table 20-5 provides a good overview of the characteristics of these 4 isolation levels.

Table 20-5                          & nbsp;                  4 Isolation Level comparison

Read data consistency and allowable concurrency side-effect isolation levels	Read Data consistency	Dirty Read	Non-REPEATABLE READ	Phantom reading
Uncommitted read (READ UNCOMMITTED)	Lowest level, only guaranteed not to read physically corrupted data	Is	Is	Is
Submitted degrees (Read committed)	Statement-level	Whether	Is	Is
REPEATABLE READ (Repeatable Read)	Transaction-level	Whether	Whether	Is
Serializable (Serializable)	Highest level, transaction level	Whether	Whether	Whether

The last thing to note is that each specific database does not necessarily fully implement the above 4 isolation levels, for example, Oracle only provides read committed and serializable two standard isolation levels, and also provides its own defined read only isolation level; SQL In addition to supporting the 4 isolation levels defined by the above Iso/ansi SQL92, server supports an isolation level called snapshot, but strictly it is a serializable isolation level implemented with MVCC. MySQL supports all 4 isolation levels, but in a specific implementation there are some features, such as MVCC consistency reading at some isolation levels, but not in some cases, which will be described further in later chapters.

get InnoDB row lock race condition

You can analyze the contention for row locks on the system by examining the Innodb_row_lock state variables:

1. 
   
2. Mysql> Show status like ' innodb_row_lock% ';
   
3. +-------------------------------+-------+
   
4. | variable_name | Value |
   
5. +-------------------------------+-------+
   
6. | Innodb_row_lock_current_waits | 0 |
   
7. | Innodb_row_lock_time | 0 |
   
8. | Innodb_row_lock_time_avg | 0 |
   
9. | Innodb_row_lock_time_max | 0 |
   
10. | Innodb_row_lock_waits | 0 |
    
11. +-------------------------------+-------+
    
12. 5 rows in Set (0.01 sec)

Copy Code

If the lock contention is found to be more serious, such as innodb_row_lock_waits and INNODB_ROW_LOCK_TIME_AVG values are higher, you can also set the INNODB monitors to further observe the lock conflict of the table, data rows, etc. and analyze the reason for lock contention. Here's how:

1. 
   
2. Mysql> CREATE TABLE Innodb_monitor (a INT) Engine=innodb;
   
3. Query OK, 0 rows affected (0.14 sec)

Copy Code

You can then use the following statement to view:

1. 
   
2. mysql> Show innodb status\g;
   
3. *************************** 1. Row ***************************
   
4.   type:innodb
   
5.   name:
   
6. Status:
   
7. ...
   
8. ...
   
9. ------------
   
10. transactions
    
11. ------------
    
12. Trx ID counter 0 117472192
    
13. Purge do for Trx ' s N < 0 117472190 undo n< 0 0
    
14. History list length n
    
15. T Otal number of lock structs in row lock hash table 0
    
16. LIST of transactions for each SESSION:
    
17. -- -transaction 0 117472185, not started, process no 11052, OS thread ID 1158191456
    
18. MySQL thread id 200610, que Ry ID 291197 localhost root
    
19. ---TRANSACTION 0 117472183, not started, process no 11052, OS thread ID 11587239 $
    
20. MySQL thread ID 199285, query id 291199 localhost root
    
21. Show innodb status
    
22. ...

Copy Code
The monitor can stop viewing by issuing the following statement:

1. 
   
2. mysql> DROP TABLE Innodb_monitor;
   
3. Query OK, 0 rows affected (0.05 sec)

Copy Code

After setting up the monitor, in the show INNODB status display, there will be detailed information about the current lock waiting, including the table name, lock type, lock record, etc., so as to facilitate further analysis and problem determination. When the monitor is turned on, the monitored content is logged to the log every 15 seconds by default, and the. err file becomes very large if opened for a long time, so after the user confirms the cause of the problem, remember to delete the monitor table to turn off the monitor, or by using "--console" Option to start the server to close the Write log file.

InnoDB Lock mode and lock method

InnoDB implements the following two types of row locks.

• L Shared Lock (S): Allows one transaction to read a line, preventing other transactions from acquiring an exclusive lock on the same data set.
• L exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.
  

In addition, in order to allow row and table locks to coexist and implement a multi-granularity locking mechanism, INNODB also has two intent locks (Intention Locks) that are used internally, both of which are table locks.

• L Intent Shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.
• L Intent Exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain the IX lock of the table before adding an exclusive lock to the data row.
  

The compatibility of the above lock modes is shown in table 20-6.

Table 20-6 InnoDB row lock mode Compatibility List

Whether the request lock mode is compatible with the current lock mode	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released. The intent lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement, and the transaction can be displayed to the recordset with shared or exclusive locks.

• &iexcl; Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.
• &iexcl; Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.
  

Use SELECT ... In SHARE mode, a shared lock is used primarily to confirm the existence of a row of records when data dependencies are needed, and to ensure that no one is doing an update or delete operation on the record. However, if the current transaction also requires an update to the record, it is most likely to cause deadlocks, and for applications that require an update operation after locking the row records, you should use SELECT ... The for Update method obtains an exclusive lock.

In the example shown in table 20-7, the use of select ... In SHARE mode locks up and then updates the record to see what happens, where the actor table's actor_id field is the primary key. Table 20-7 Shared lock example for INNODB storage engine

Session_1	Session_2
mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178;+------ ----+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178      | lisa       | monroe    |+----------+------------+-----------+1 row in Set (0.00 sec)	mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178;+------ ----+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178      | lisa       | monroe    |+----------+------------+-----------+1 row in Set (0.00 sec)
Current session to actor_id=178 record plus share mode shared lock:mysql> select Actor_id,first_name,last_name from actor where actor_id = 178 Lock in Share mode;+----------+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.01 sec)
	The other session can still query the record, and can also add share mode to the shared lock:mysql> select Actor_id,first_name,last_name from actor where actor_id = 178 lock in Share mode;+----------+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.01 sec)
The current session updates the locked record, waiting for the lock:mysql> update actor Set last_name = ' MONROE T ' where actor_id = 178; wait
	Other sessions also update the record to cause deadlocks to exit:mysql> update actor Set last_name = ' MONROE T ' where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction
After obtaining the lock, you can successfully update the:mysql> update actor Set last_name = ' MONROE T ' where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched:1 changed:1 warnings:0

When using SELECT ... The for update locks up and then updates the record, as shown in table 20-8. Table 20-8 Example of an exclusive lock for InnoDB storage engine

Session_1	Session_2
mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178;+------ ----+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.00 sec)	mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178;+------ ----+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.00 sec)
The current session of the actor_id=178 record plus a for update shared lock:mysql> select Actor_id,first_name,last_name from actor where actor_id = 178 For update;+----------+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.00 sec)
	The other session can query the record, but cannot add a shared lock to the record, and will wait for the lock:mysql> select Actor_id,first_name,last_name from actor where actor_id = 178;+- ---------+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE |+----------+------------+-----------+1 row in Set (0.00 sec) mysql> Select Actor_id,first_name,last_name from Actor where actor_id = 178 for update;
The current session can update the locked record, release the lock:mysql> update actor Set last_name = ' MONROE T ' where actor_id = 178; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> commit; Query OK, 0 rows affected (0.01 sec)
	The other session gets the lock and gets the record submitted by the other session:mysql> select Actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+| actor_id | first_name | Last_Name |+----------+------------+-----------+| 178 | LISA | MONROE T |+----------+------------+-----------+1 row in Set (9.59 sec)

InnoDB Lock problem