“MySQL” lock

This is the fifth day of my participation in Gwen Challenge

One, foreword

Solve dirty read, unrepeatable read, phantom read problem: based onMVCCMechanism (i.e.,undo logVersion chain andReadView)

How can multiple transactions update the same row of data to avoid dirty writes?

Rely on: lock mechanism. With this mechanism, multiple transactions can be serialized when updating the same row of data.

To update a row, the data page must be read from the disk file into the cache page.

That is, the data and associated lock data structures are in memory, and the operations are in memory.

MySQL lock mechanism and Java lock mechanism, the idea is completely similar.

Simulate the transaction update data process

Simulation steps:

1. Transaction A updates the specified data
2. Transaction B updates the same data
3. The transaction A update completes and commits

1. Initial state: Transaction A updates A row of data

Transaction A locks that row, as shown:

Since this row is not locked, transaction A directly locks the operation.

2. Transaction B updates this row

At this time, transaction B also wants to operate on this row of data and finds that there is already a transaction operation and locks the data. Transaction B generates a lock data result and sets its wait state to true, as shown in the figure:

3. Transaction A is updated and committed

Transaction A releases its own lock after updating and committing. Once the lock is released, it wakes up other waiting transactions.

As shown in figure:

Two, lock division

MySQL locks are:

• A Shared lock
• An exclusive lock
• The mutex
• Table level lock

(1) Shared lock and exclusive lock

MySQL > Exclude; Share; Exclude;

When a transaction runs, an exclusive lock (also known as an X lock) is added.

• Other transactions that want to update this row of data place an exclusive lock and wait after the lock.

• Other transactions can read this row without locking it.

  Rely on the MVCC mechanism to solve frequent lock mutex.

Shared lock (S lock) : Locks are added during query

That is, add lock in share mode after the query statement

, such as:
SELECT * FROM table LOCK IN SHARE MODE;
Copy the code

A shared lock cannot be added to an exclusive lock (that is, someone else cannot update it).

For example: In China land belongs to the people, but no one can monopolize a piece of land.

The rules of shared and exclusive locks are as follows:

Mutex: Data that is updated after a query cannot be updated by any other transaction.

, such as:
SELECT * FROM table FOR UPDATE;
Copy the code

(2) Table level lock

TABLE locks are assigned at the TABLE level by default when DDL statements are executed, such as ALTER TABLE operations.

Table lock, syntax:

-- Add table level shared lock
LOCK TABLES xxx READ

Add table level exclusive lock
LOCK TABLES xxx WRITE
Copy the code

Two other cases add table level locks:

• An exclusive lock is added to the row level and an intent exclusive lock is added to the table level if a transaction performs an add, delete, or alter operation on the table
• If a transaction is performing a query in the table, an intent shared lock is added to the table level

The relationship is as follows: