This is the sixth day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Two basic ways to resolve concurrent transactions

1.1. Various problems that may arise when a transaction is executed concurrently. The situation that a concurrent transaction accesses the same record can be roughly divided into the following three situations

​

Read-read: concurrent transactions that read the same record in succession have no impact on the record and cause no problems, so this is allowed to happen

Write-write conditions: Where concurrent transactions make successive changes to the same record, dirty writes can occur and are not allowed at any isolation level. Therefore, when multiple uncommitted transactions make changes to a record one after another, they need to be queued to execute. This process is realized by locking, which is an in-memory structure. There is no lock before the transaction is executed, that is, there is no lock structure associated with the record.

When a transaction changes a record, it first needs to see whether there is a lock structure associated with the record in memory. If there is no lock structure associated with the record, a lock structure will be generated in memory. For example, if transaction T1 wants to change the record, it needs to generate a lock structure associated with the record \

Is_waiting =true if another transaction T2 wants to change the record before the transaction T1 commits, first check if there is a lock structure associated with the record, then generate a lock structure associated with the record, is_waiting=true

If transaction T1 commits at this time, the lock structure generated by transaction T2 is released, and it is found that transaction T2 is waiting for the lock. Set the lock structure corresponding to transaction T2 is_WAITING to true, and then wake up the thread corresponding to transaction T2

Read – write or write – read situations: that is, one transaction reads and the other modifies

In this case, dirty reads, unrepeatable reads, and phantom reads can occur.

​



1.2. How to solve the problems of dirty read, unrepeatable read and phantom read

Solution a: Read operations use multi-version concurrency control (MVCC). Write operations lock MVCC by generating a ReadView and then using the ReadView to find the record version that matches the condition (the history version is built from undo logs). Query statements can only read the changes that were committed before the ReadView was generated. Changes made by transactions that were not committed before the ReadView was generated or that were opened later are not visible. The write operation is for the latest version of the record. The historical version of the read record does not conflict with the latest version of the change record. That is, the read-write operation does not conflict with the MVCC.

MVCC Read records used by ordinary SELECT statements at Read COMMITTED and Repeatable Read isolation levels Each time a transaction executes a SELECT operation, a ReadView is generated. The existence of ReadView itself ensures that the transaction cannot read the changes made by uncommitted transactions, that is, avoid dirty reads. In Repeatable Read isolation level, a transaction only generates a ReadView for its first SELECT operation, which is reused for all subsequent select operations, thus avoiding the problem of unrepeatable reads and phantom reads

<br /> Solution 2: Read and write operations are locked. < BR /> In some service scenarios, the latest version of a record must be read every time. <br />Copy the code

​

To sum up, in MVCC mode, read-write operations do not conflict with each other, resulting in higher performance. In lock mode, read-write operations need to be executed in a queue, affecting performance. Generally, MVCC is used to solve the problem of concurrent read and write operations.

Shared locks and exclusive locks

Shared locks: S-locks for short. Before a transaction reads a record, it needs to acquire the record S lock. Exclusive locks are also called X locks. When a transaction changes a record, it obtains the X lock of the record.

Assuming that transaction T1 first acquires the S lock on a record, transaction T2 then accesses the record as well:

  • If transaction T2 wants to acquire another record S lock, then transaction T2 also acquires the lock, meaning that both transactions T1 and T2 hold the S lock on the record.
  • If transaction T2 wants to acquire another record’s X lock, the operation is blocked until the S lock is released after transaction T1 commits.

Assuming that transaction T1 first acquises the X lock on a record, either the S lock or the X lock that transaction T2 then attempts to acquire will be blocked until transaction T1 commits.

So S locks are compatible with S locks, S locks are incompatible with X locks, and X locks are incompatible with X locks.

Lock read statements

3.1 Add S lock to read records

SELECT ... LOCK IN SHARE MODE;
Copy the code

​

3.2 Add X lock to read records

SELECT ... FOR UPDATE;
Copy the code

Row and table locks

InnoDB storage engine supports both table locking and row locking. The granularity of table locking is coarse and only a few records need to be locked, but using table locking is equivalent to locking all records in the table, so the performance is poor. The finer granularity of row locking enables more precise concurrency control.