5. The InnoDB row locks

5.1 Introduction to Row Locking

Row lock features: Prefer InnoDB storage engine, high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. There are two major differences between InnoDB and MyISAM: one is transaction support; The second is the use of row-level locking.

5.2 Background

Transactions and their ACID properties A transaction is a logical processing unit consisting of a set of SQL statements. Transactions have the following four properties, referred to simply as transaction ACID properties.

ACID properties	meaning
Atomicity	A transaction is an atomic unit of operation in which all modifications to data either succeed or fail.
Consistent	The data must be in a consistent state at the beginning and completion of a transaction.
Isolation	Database systems provide isolation mechanisms to ensure that transactions run in a “standalone” environment that is not affected by external concurrent operations
Durable	After the transaction completes, changes to the data are permanent.

Problems with concurrent transaction processing

The problem	meaning
Lost Update	When two or more transactions select the same row, the value changed by the original transaction is overwritten by the value changed by the subsequent transaction.
Dirty Reads	When a transaction is accessing data and making changes to the data that have not yet been committed to the database, another transaction also accesses the data and then consumes it.
Non-repeatable Reads	At some point after reading some data, a transaction reads the previously read data again and finds that it is inconsistent with the previously read data.
Phantom Reads	A transaction rereads previously queried data under the same query criteria, only to find that another transaction inserts new data that meets its query criteria.

Transaction isolation level

To solve the transaction concurrency problem mentioned above, the database provides transaction isolation mechanisms to solve this problem. The more stringent the transaction isolation of the database, the less concurrency side effects, but the higher the cost, because transaction isolation is essentially using transactions to “serialize” to a certain extent, which is obviously contradictory to “concurrency.”

The isolation levels of a database are Read uncommitted, Read committed, Repeatable Read, and Serializable. These four levels can solve dirty write, dirty Read, unrepeatable Read, and phantom Read problems one by one.

Isolation level	Lost update	Dirty read	Unrepeatable read	Phantom read
Read uncommitted	x	Square root	Square root	Square root
Read committed	x	x	Square root	Square root
Repeatable Read (default)	x	x	x	Square root
Serializable	x	x	x	x

Note: √ indicates that it may occur, and × indicates that it does not occur. :

Mysql database isolation level is Repeatable read by default.

show variables like 'tx_isolation';
Copy the code

5.3 InnoDB row lock mode

InnoDB implements two types of row locking.

• Shared lock (S) : Also known as read lock or S lock for short, a shared lock means that multiple transactions can share a lock for the same data. All transactions can access data but can only read data but cannot modify it.

• Exclusive lock (X) : also known as write locks, referred to as “X lock, exclusive lock is cannot coexist with other lock, such as a transaction to obtain a data row exclusive lock, and other transactions can’t obtain the bank’s other locks, including Shared and exclusive locks, lock but the transaction to get exclusive lock is to read and modify data.

For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns an exclusive lock (X) to the data set involved;

InnoDB does not place any locks on normal SELECT statements;

A recordset can be displayed with a shared or exclusive lock.

Shared lock (S) :SELECT * FROM table_name WHERE. LOCKINSHARE MODE exclusive lock (X) :SELECT * FROM table_name WHERE.FOR UPDATE
Copy the code

5.4 Case Preparation

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1.'100'.'1');
insert into test_innodb_lock values(3.'3'.'1');
insert into test_innodb_lock values(4.'400'.'0');
insert into test_innodb_lock values(5.'500'.'1');
insert into test_innodb_lock values(6.'600'.'0');
insert into test_innodb_lock values(7.'700'.'0');
insert into test_innodb_lock values(8.'800'.'1');
insert into test_innodb_lock values(9.'900'.'1');
insert into test_innodb_lock values(1.'200'.'0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
Copy the code

5.5 Basic demonstration of row locking

session-1	session-2
Disable the automatic submission function	Disable the automatic submission function
All data can be queried normally	All data can be queried normally
Query data whose ID is 3.	Query data whose ID is 3.
Update data with id 3 without committing;	Update data with ID 3 in wait state
Commit to commit a transaction	The block is unblocked and the update proceeds normally
Above, we operate on the same row. Next, we demonstrate different rows:
Update the data whose ID is 3. Obtain the row lock and perform the update	Since it is not on the same row as session-1, obtain the current row lock and perform the update

5.6 No-Index Row Lock Upgraded to a table lock

If data is not retrieved by index criteria, InnoDB locks all records in the table, which works like a table lock.

To check the current table index: show index from test_innodb_lock;

session-1	session-2
Turn off automatic commit of transactions	Turn off automatic commit of transactions
Execute update statement:	Execute update statement but block:
Commit transaction:	Unblocking and update succeeded:
	To commit:

Since the name field was originally vARCHAR type when the update was performed, we used it as an array type, and there was type conversion, index failure, and finally the row lock became a table lock.

5.7 Harm of clearance lock

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks existing data that meets the condition. InnoDB also locks “gaps” for records whose Key values are within the condition range but do not exist. This locking mechanism is called next-key locking.

Example:

Session-1	Session-2
Turn off automatic transaction commits	Turn off automatic transaction commits
Update the data based on the ID range
	Insert record with ID 2 in blocked state
Commit transaction;
	Unblock and insert:
	Commit transaction:

5.8 InnoDB row lock contention

show status like 'innodb_row_lock%';
Copy the code

Innodb_row_lock_current_waits: Number of locks currently being waited for

Innodb_row_lock_time: total lock time since system startup

Innodb_row_lock_time_avg: average wait time

Innodb_row_lock_time_max: The time spent waiting for the longest time since system startup

Innodb_row_lock_waits: Total number of waits since system startup

When the number of waits is very high and the length of each wait is not small, we need to analyze why there are so many waits in the system, and then make optimization plans according to the analysis results.

5.9 summarize

InnoDB storage engine implements row-level locking, and while InnoDB storage engine may have a higher performance loss than table locking in terms of implementation of locking mechanism, it is far less capable in terms of overall concurrent processing capacity than MyISAM table locking. When system concurrency is high, InnoDB’s overall performance is significantly better than MyISAM’s.

However, InnoDB’s row-level locking also has its vulnerable side. When used incorrectly, InnoDB’s overall performance may not be as good as MyISAM’s, or even worse. Optimization suggestions:

• As much as possible, all data retrieval can be done through the index, avoiding the escalation of non-indexed row locks to table locks.
• Design indexes properly to minimize the scope of locks
• Minimize index conditions and index ranges to avoid gap locking
• Try to control the transaction size to reduce the amount of resources locked and the length of time
• Low-level transaction isolation is possible (but needs to be met at the business level)