MySQL lock

The original intention of database lock design is to deal with concurrency problems.As a resource shared by multiple users, when concurrent access occurs, the database needs to reasonably control the access rules of the resource.Locks are important data structures used to implement these access rules.

Locks in MySQL can be roughly divided into three categories: global locks, table-level locks, and row locks.

Global lock

Locks the entire database instance.When you need to make the entire library in a read-only state, you can use this command, and then the following statements of other threads will be blocked: data update statements (data addition, deletion and modification), data definition statements (including creating tables, modifying table structures, etc.) and commit statements for update-like transactions.A typical usage scenario for global locks is to make logical backups of the entire library.

• If you back up on the main database, you cannot perform updates during the backup period, and the business basically has to stop;
• If you are backing up on the slave database, the slave database cannot execute the binlog synchronized from the master database during the backup, which will cause the master-slave delay.

Global lock is mainly used in the logical backup process.For libraries that are all InnoDB engines, mysqldump recommends using the --single transaction parameter, which is more friendly to applications.

Table-level lock

There are two types of locks at the surface level: one is a table lock and the other is a meta data lock (MDL).

Table locks are generally used when the database engine does not support row locks.

MDL will not be released until the transaction is committed. When making changes to the table structure, be careful not to cause online queries and updates to be locked.

Row-level lock

The row lock is implemented by each engine itself at the engine layer.But not all engines support row locks. For example, the MyISAM engine does not support row locks.Not supporting row locks means that concurrency control can only use table locks. For tables of this engine, only one update can be executed on the same table at any time, which will affect business concurrency.InnoDB supports row locks, which is one of the important reasons why MyISAM was replaced by InnoDB.

In InnoDB transactions, row locks are added when they are needed, but they are not released immediately when they are not needed, but are not released until the end of the transaction.This is the two-phase locking protocol.

If you need to lock multiple rows in your transaction, put the locks that are most likely to cause lock conflicts and most likely to affect concurrency as far back as possible.

Deadlock and Deadlock Detection

When a deadlock occurs, there are two strategies:

• One strategy is to go straight to waiting until it times out.This timeout can be set by the parameter innodb_lock_wait_timeout.
• Another strategy is to initiate deadlock detection, and after a deadlock is found, actively roll back a transaction in the deadlock chain so that other transactions can continue to execute.Set the parameter innodb_deadlock_detect to on to enable this logic.

In InnoDB, the default value of innodb_lock_wait_timeout is 50s, which means that if the first strategy is adopted, when a deadlock occurs, the first locked thread will not exit after 50s, and then other threads willmay continue.For online services, this wait time is often unacceptable.

Under normal circumstances, we still have to adopt the second strategy, namely: active deadlock detection, and the default value of innodb_deadlock_detect itself is on.Active deadlock detection can
quickly detect and handle deadlocks when they occur, but it also has additional burdens.

Follow the official account and send ztzl to download "China-Taiwan Strategy: China-Taiwan Construction and Digital Business" for free.