Do you really understand Mysql transactions and locks?

preface

Please follow my wechat official account [Mflyyou] for continuous updates.

Github.com/zhangpanqin… Collect technical articles and my series of articles, welcome to Star.

In this paper, the content

• Definition and role of transactions, isolation level
• What is MVCC, snapshot read and lock read
• Mysql > select * from mysql. Mysql > select * from mysql. Mysql > select * from mysql. Mysql
• Use scenarios for pessimistic and optimistic locks

Mysql is 8.0.17.

The transaction

Transactions transform the database from one consistent state to another. Transactions can consist of a single SQL or a complex set of SQL.

Transaction characteristics:

• Atomicity

The entire transaction either succeeds or fails. There is no possibility of a transaction in which some modifications succeed and some fail, thereby breaking data consistency.

• Consistency

The integrity of the database is not compromised before and after a transaction. For example, there is a transfer operation, Xiao Ming originally had 100 yuan, someone transferred 50 yuan to Xiao Ming. When the update statement is executed, the transaction is not committed, but the database crashes. When the database is started, xiaoming is still $100.

• Isolation

The isolation of transactions requires that transactions do not affect each other. Often isolation is related to locking.

• “Durability”

After the transaction is committed, the result is persisted. Even if the database crashes, the data can be automatically restored to the original state according to its own log.

When a client links to a database, it operates in a transaction. The default transaction is committed automatically. We can also set up manual commits so that we can execute multiple SQL within a transaction.

Query what transactions are currently in the database
select * from information_schema.INNODB_TRX;
Copy the code

The isolation level of the transaction

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Query the default global isolation level and the isolation level used in the session

SELECT @@global.transaction_isolation,@@session.transaction_isolation;
Copy the code

Setting the Isolation Level

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL {REPEATABLE READ|READ COMMITTED|READ UNCOMMITTED|ERIALIZABLE}

-- Sets the global isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Isolation level of the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Copy the code

Some of the problems that can occur at the isolation level.

We generally do not use read uncommitted and serialization. Mysql’s default isolation level is repeatable reads and Oracle’s is read committed.

Dirty read

One transaction can read uncommitted data from another transaction. Session A inserts data, but transaction A does not commit, but transaction B reads inserted H.

Unrepeatable read

Non-repeatable reads refer to the situation where the same SQL is executed multiple times within a transaction, but the DML operations of other transactions are committed, resulting in inconsistent data sets read multiple times by the current transaction.

The difference between an unrepeatable read and a dirty read is that a dirty read reads uncommitted data from a transaction, while an unrepeatable read reads data after a transaction is committed.

Because session A updates the data with ID =6 at Time=4, session B reads the same data at Time=3 and Time=5 in the same transaction, but the data read at Time=7 is inconsistent with the data read before.

Phantom read

Phantom reading is when a transaction reads a row newly inserted by another transaction (the other transaction committed). This one is easier to understand.

Knock on the blackboard. Here’s the point

T5, T7,t9 all read the same data. There is no phantom problem, this is related to Mysql MVCC, but MVCC does not solve phantom problem.

Mysql has the Multi Version Concurrency Controller feature of MVCC. This means that if a row is being read for an UPDATE or DELETE operation, the read does not block until the UPDATE or DELETE operation on the current row completes, but instead reads a snapshot of the current row. MVCC is implemented using undo log.

With the isolation level being repeatable, t5, T7, and T9 read the same data.

However, when the isolation level is read committed, the results are different.

In the read committed case, T5 and T7 read the same data, while T9 reads the latest data.

MVCC principle

MVCC is related to undo logs, which are stored in shared tablespaces, but starting in 5.6, separate Undo tablespaces can also be used.

In InnoDB storage engine, undo log can be divided into:

• Insert undo log, the INSERT operation is visible only to the current transaction until the transaction commits
• Upate undo log, the undo log generated by UPDATE and DELETE operations

Figure from: How MySQL Works: Understanding MySQL from the Root

Each row in the Mysql database contains two hidden columns

• Trx_id, the transaction ID is assigned to trx_ID in the current row record when the transaction for the modification operation is committed
• Roll_pointer points to the undo log pointer

Transaction ID generation is not generated directly after BEGIN, but after SQL is executed. For example, it is generated after we perform INSERT, UPDATE, or DELETE. The transaction ID is incremental and unique.

Changes to a row in the database place old data into the Undo log. As more and more versions are updated, the roll_pointer link forms a version chain that is used by MVCC.

When the query data is not locked, we can determine whether the current transaction can view the data from the version chain:

• If trx_ID is the same as the id of the current transaction, it indicates that the version is changed by the current transaction. You can not view the version data.
• If trx_ID is inconsistent with the current transaction ID and the transaction corresponding to trx_ID is not committed, you cannot view it
• If trx_id is less than the current transaction ID, the version data can also be accessed.
• If trx_id is greater than the current transaction ID, the version cannot be seen.

If one version of the data is not visible to the current transaction, it follows the version chain to the next version of the data until the last version in the chain. If the last version is also not visible, it means that the record is not visible to the transaction at all and is not included in the query result.

For repeat reads, the transaction ID is generated only when the data is read for the first time in the transaction (SELECT).

For read committed, a transaction ID is generated each time data is read (SELECT) in a transaction.

The savepoint

When we start a transaction, rollback can only rollback one transaction. But if a transaction has too many operations, we just want to roll back to a specific place. You can use the save point function.

Create a savepoint
SAVEPOINTSavepoint name;-- Rollback to a specific savepoint
ROLLBACK TOSavepoint name;-- Delete a savepoint
RELEASE SAVEPOINTSavepoint name;Copy the code

Start a transaction
begin;

Select * from 1 where id = 1
select * from index_test where id in (1.2);

+----+-------------+
| id | description |
+----+-------------+
|  1 | 1           |
|  2 | h6          |
+----+-------------+

-- Update data with id 1 and save the savepoint
update index_test set description ='t1' where id =1;
savepoint t1;

-- Verify that the data is updated, because it is the same transaction, so it can be viewed
select * from index_test where id in (1.2);
+----+-------------+
| id | description |
+----+-------------+
|  1 | t1           |
|  2 | h6          |
+----+-------------+

Update data with id =2 and save the savepoint
update index_test set description ='t2' where id =2;
savepoint t2;

-- Verify that the data is updated, because it is the same transaction, so it can be viewed
select * from index_test where id in (1.2);
+----+-------------+
| id | description |
+----+-------------+
|  1 | t1          |
|  2 | t2          |
+----+-------------+

Roll back to a specific savepoint
rollback to t1;
select * from index_test where id in (1.2);
+----+-------------+
| id | description |
+----+-------------+
|  1 | t1           |
|  2 | h6          |
+----+-------------+

When you roll back a savepoint, the T2 savepoint is lost. An error occurs when you roll back t2.
rollback to t2;
-- ERROR 1305 (42000): SAVEPOINT t2 does not exist

commit;
Copy the code

The lock

Java uses locks to keep data consistent, and Mysql uses locks to implement transactions.

InnoDB implements two types of row-level locking: shared locking and exclusive locking.

InnoDB also has intentional locks internally, which InnoDB automatically adds. Intent locks are table-level locks.

Shared Lock (S Lock)

-- Manually add s lock
select * from tableName  lock in share mode;
Copy the code

Exclusive Lock (X Lock)

-- Manually add x lock
select * from tableName  for update;
Copy the code

The lock is released after the transaction commits. The S lock is compatible with the S lock, and the X lock is incompatible with any other lock. Incompatible locks need to wait for another lock to be released.

DELTE, UPDATE, and INSERT databases give us exclusive locks by default.

Shared and exclusive locks demo

A Shared lock

Transaction A acquires the S lock with id=1 and transaction B acquires the S lock with ID =1. Two transactions do not need to block and wait for the other transaction to finish.

Exclusive lock

Transaction B blocks and waits for transaction A to commit. After transaction A commits, the update statement of transaction B is completed.

Intent locks

• Intended Shared lock (IS) : a transaction IS about to set a shared lock on each row in a table. The transaction must obtain the IS lock on the table before it can lock the row S.
• Intentional exclusive lock (IX) : a table IX lock must be acquired before a transaction can lock rows in a table with an X lock.

Compatibility of row-level locks and table-level intent locks

The information_schema.innodb_TRx table can be used to determine the status of transactions and information between transactions and locks.

You can also obtain more lock information from performance_schema.datA_LOCKS and performance_schema.datA_lock_WAITS.

The algorithm of the lock

InnoDB storage engine has 3 locking algorithms, respectively

• Record Lock, Lock is the current row
• Gap Lock, which locks a range but does not include the current row
• Next-key Lock. The Lock is the record itself and the gap between the records

Mysql transaction isolation level uses next-key Lock algorithm to Lock repeat reads. The isolation level is locked using the Record Lock algorithm for read committed.

Solve illusory problems

Time	A transaction	Transaction B
1	begin;	begin;
2	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3		SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
4	select * from index_test where id >1 for update;
5		insert into index_test (id,description)values(3,’adfa’);
6	commit
7		The insert statement was successfully executed
8		commit;

Because the isolation level of the transaction is repeat read, the next-key Lock is adopted and the primary Key is locked in the range greater than 1. When transaction B performs the insert operation, it will block waiting for transaction A to complete or the Lock times out, and the insert statement of transaction B will report an error.

Usage scenarios of locks

1. For example, the business of placing an order to buy something.

Xiao Ming buys the commodity with ID =1, while Xiao Zhang also places an order to buy the commodity with ID =1, but the inventory of this commodity is 1 at present.

The rough logic of the implementation is this, but oversold occurs.

Pessimistic lock solves the oversold problem

To solve this problem, add mutex. In this way, only one person can check the goods at a time. An exception will be thrown when transaction B blocks to acquire x lock timeout with id=1.

The above is pessimistic lock to solve the oversold problem, but the exclusive lock is mutually exclusive, only one person can buy goods at a time, the subsequent people are all blocked in transaction B T3 where waiting for the release of the lock, which greatly reduces the concurrency of the program.

Optimistic lock solves oversold problems

Optimistic locking is just like CAS in Java. We add a field version to the table.

Retries can be performed using the Spring-Retry framework with a single comment.

With optimistic locking, 100 users can make changes concurrently, and maybe 30% of them can make a payment at once. The remaining 70 people then retry to buy, which is a lot more concurrency than pessimistic locks.

However, if the TPS of the system is large and only 10% or less people are successful in buying it each time, consider using pessimistic locks.

Please follow my wechat official account [Mflyyou] for continuous updates.

Github.com/zhangpanqin… Collect technical articles and my series of articles, welcome to Star.