1. By feature classification – read lock and write lock

A read lock is also called a shared lock, and the opposite is a write lock, also known as an exclusive lock.

  • Read lock is shared and will not block each other. Multiple clients read the same data at the same time without interfering with each other.
  • Write locks, which are exclusive, block other write locks and read locks.

Why are there these two locks?

To ensure concurrent security, only one client can modify or write data at a time and prevent other clients from reading the data being modified or written.

2. By granularity classification – table lock and row lock

One of the most important indicators of concurrency control granularity is when we deal with concurrency security in systems and improve concurrency.

In general, the smaller the range of locked data, the higher the concurrency of the system. Ideally, the system locks only one field.

But another problem we have to face is that locking data is a complex process, including locking, unlocking, checking the lock status, etc., will increase the system’s additional overhead.

Therefore, you need to strike a balance between the cost of locking data and the security of the data, while seeking the best performance at the finest granularity possible.

For database systems, the two most common types of locks are table locks and row locks.

In fact, I also found in MySQL 5.7 documentation about InnoDB locks:

  • Intention Locks
  • Record Locks
  • Gap Locks
  • Next-key Locks
  • Insert Intention Locks
  • Auto-increment primary key lock? The Locks (AUTO – INC)
  • Spatial index predicate lock? (Predicate Locks for Spatial Indexes)

I dug up the last two.

2.1. Table locks

Table lock, as the name implies, locks the entire table when data in the table is modified and does not allow other clients to modify or read the table before the modification is complete.

Table lock is the most basic lock strategy in MySQL, and is the least expensive lock strategy. Although the storage engine can manage its own locks, MySQL itself uses a variety of valid table locks for different purposes. For example, the server uses table locks for statements such as ALTER TABLE, ignoring the storage engine’s locking mechanism. — High Performance MySQL

Table locking is a form of locking that both InnoDB and MyISAM support.

Lock and unlock

MySQL lock and unlock can be implemented with the following statements:

- lock
lock table table_name read/write;
- unlock
unlock tables;
Copy the code

It is important to note that locking and unlocking must be done in the same session (connection), which blocks operations on the table by other sessions (connection).

2.2. Row locks

Row locks, also easy to understand, lock a row of data in a table.

The specific implementation of the row lock is done by the storage engine, MySQL Server layer does not care about the implementation details.

InnoDB implements standard row-level locks, with two types of locks: shared (read) and exclusive (write). MySQL 5.7 official Manual

Row locking is characterized by small locking granularity, so concurrency can be greatly improved, but there is a large performance overhead.

There is some ambiguity in the statement that performance costs a lot. My personal understanding is as follows:

When a table lock is used, multiple data can be modified once and then unlocked once. Only exceptions occur during the process.

If a row lock is used, only one data can be modified once and then unlocked once. If a large amount of data needs to be modified, the row lock needs to be unlocked frequently.

So overall, there is a high performance overhead when using row locking.

Use of row locks

In general, InnoDB decides when to use row locks, which means that the storage engine decides whether or not to use row locks in most scenarios.

In InnoDB transactions, row locks are added when needed. — Lin Xiaobin, Talk 45 of MySQL In Action

However, we can still obtain row locks using SQL statements:

select * from t_user where user_id = '1' for update;
Copy the code

At the end of the last article, I mentioned in the transaction commit that if a transaction is not explicitly opened, all operations are executed and committed as a single transaction. So most of the time we don’t need to actively lock.

Two-stage locking

First of all, we still quote a sentence from Teacher Lin Xiaobin:

In InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are not needed, but wait until the end of the transaction. — Lin Xiaobin, Talk 45 of MySQL In Action

Two-phase locking (2PL) is mainly used to ensure consistency and isolation in transactions.

In a transaction, there are two phases: lock and unlock. Unlock at the end of a transaction to ensure that no dirty data is generated.

Suppose there is a scenario of inventory reduction. The two transactions are executed in the following order:

If A transaction performed A deduction inventories, immediately unlock (assuming the data can be submitted at this time), so at this time based on the current reading under the RR isolation level (next article can speak this concept, understanding is immediately visible), the transaction will B finished inventory deduction, on the basis of the transaction is A transaction at this time A exception occurs need rollback, transaction B end of normal, So how do you roll back data?

If the data for transaction A cannot be committed, how can the data for transaction B be updated? Updating data on the original basis will result in data loss (if transaction A ends normally).

Therefore, the best approach is for the transaction to touch the row lock on the data at commit time, and for the rest of the time, block the transaction from updating the inventory.

3. How do deadlocks occur

Let’s look at the definition of deadlocks in High Performance MySQL:

A deadlock is a vicious cycle in which two or more transactions occupy each other’s resources and request to lock each other’s resources.

The definition given in High Performance MySQL is relatively easy to understand and extracts the key points:

  • Mutual appropriation of resources
  • Requesting the Other party’s resources

For example, “Dragon Ball” I believe we have seen it. Imagine if the characters in Dragon Ball were peaceful and didn’t use force, only mouth guns.

  • The Monkey King finds five dragon balls
  • Piccolo found two dragon balls
  • Open each other mouth cannon, request each other to hand over the dragon ball
  • Neither of us convinced the other. We talked for ten thousand years

So think of a database transaction

According to the two-phase commit, transaction A and transaction B will release the data row lock only when committing the transaction, so transaction A blocks until “modify B resource”, and transaction B blocks until “modify A resource”, at which point neither transaction can proceed.

How do I solve deadlocks

InnoDB offers two solutions:

  • Deadlock timeout: when the query time reaches the lock waiting timeout setting, the lock is abandoned.
  • Deadlock detection, initiated detection, found after thinking, active rollback of one of the transactions.

Deadlock timeout

In a deadlock timeout scenario, the transaction that is rolled back is the transaction that requested the lock (or lock wait) for a set time.

InnoDB provides the innodb_lock_WAIT_TIMEOUT parameter to set the deadlock wait time.

-- Check the deadlock timeout. The default deadlock is 50 seconds
show variables like 'innodb_lock_wait_timeout'; 
-- Set the deadlock timeout
set innodb_lock_wait_timeout = 30;
Copy the code

In InnoDB, the default deadlock timeout is 50 seconds, which means that in a deadlock scenario, the transaction has to wait 50 seconds before it can continue, which is intolerable.

What if the deadlock timeout is set to a very short time? But what if it’s just normal lock waiting? There are many cases that are not deadlocks, but are actively rolled back due to timeout waiting.

Therefore, it is necessary to calculate a reasonable deadlock timeout time based on business scenarios.

Deadlock detection

** In deadlock detection scenarios, InnoDB’s policy is to roll back transactions that hold the least write locks (exclusive locks). ** Because InnoDB considers it the lowest “cost” to re-execute this transaction.

InnoDB provides a parameter such as Innodb_deadlock_detect to enable deadlock detection. The default is on.

-- Check whether deadlock detection is enabled
show variables like 'innodb_deadlock_detect'; 
-- Disable deadlock detection
set global innodb_deadlock_detect = OFF;
Copy the code

Note that innodb_deadlock_detect is a global parameter.

Is deadlock detection the silver bullet for deadlocks? Not really. Let’s imagine a scenario.

In a scenario of 10,000 concurrent transactions, every time a transaction is blocked, you have to decide whether it’s going to cause a deadlock, so the last thread that comes in has to be checked 1000 times, which is O(n), which is the time of a constant base, which looks nice, but what about the whole picture?

Thread Tn, which concurrently updates line R1, does deadlock detection:

  • Check their own lock, check 1 time;
  • View the lock held by the previous thread, checking (n-1) times.

Check n times in total. Why check the lock held by the previous (n-1) thread?

Since everyone is waiting in line for the lock, Tm can get the lock of R1 before Tn, assuming a situation in this scenario

  • Tn holds lock R2 and waits for R1;
  • Tm obtains R1 before Tn, but requests R2;
  • Tn holds R2 and waits for R1, Tm holds R1 and waits for R2.

Well, just wait for another deadlock to appear, so keep testing.

So Tn checks whether the lock held by its own lock is owned by the previous (n-1) thread.

So the time is (1+2+3+4+….. +n), that is, (n+1)*n)/2, so the time complexity of deadlock detection is O(n2)O(n^2)O(n2).

This is obviously not what we want.

4. Optimize deadlock issues

After learning how InnoDB handles deadlocks, it’s clear that both options have a lot of pitfalls. So there’s no solution to the deadlock?

Optimizes deadlocks on business logic

We have seen the mechanism for locking and unlocking, where locking occurs when data is updated and unlocking occurs before a transaction commits.

A simple idea is to reduce the time you hold locks. The business logic boils down to keeping data updates that are most likely to cause deadlocks as late in the transaction as possible to reduce lock holding time.

Optimization of data design

I remember an interesting story about a warlord who was watching a football match and was very angry when he saw many people fighting for the same ball. He said he would give everyone a ball to save them from fighting.

Isn’t that the problem with deadlocks? There are many threads competing for the same resource, so can’t we each send one resource?

Let’s say we start an event, kill a copy of High Performance MySQL, have 1W copies in stock, and we expect maybe 10W people to attend this event.

So at design time, we create 1000 high Performance MySQL inventory records, each record inventory is 10 copies.

Then 10W people rush in to buy, so we take out the last updated data to deal with the inventory, and on average, 100 people grab 10 books.

Whether it’s deadlock detection or concurrency control, it’s something we can easily handle.