Let's talk about Mysql two-phase lock and deadlock

This article brings you relevant knowledge about MySQL locks, which mainly introduces MySQL two-stage locks and deadlocks. Let’s take a look at them together. I hope it will be helpful to everyone.

Two-stage lock

Concept

talks about how InnoDB handles row lock locking and lock releasing behavior .

During the use of transactions, when a record is deleted based on the primary key, an exclusive lock will immediately be added, which completes lockingstage.

When the deletion action is completed, the lock will not be released immediately. It will release the lock until the transaction is committed.

Problems caused - blocking

##commit;

Transaction A	Transaction B
begin; update t set k=k 1 where id=1; update t set k=k 1 where id= 2;
	#begin;update t set k=k 2 where id=1;

##According to the two-phase lock protocol,

Transaction B will be ## because of the data of id=1 #Transaction A is locked and blocked because Transaction B needs to get the lock before proceeding to the next step. The above problem may not seem like a big problem, but if it is not only Transaction B, but also

Transaction C

, Transaction D, If you wait a lot and do the same thing as Transaction B, the problem will be big, and more threads will be blocked. [Recommended learning: MySQL video tutorial]How to deal with the above problemsWe should try our best to put statements that may cause blocking at the end of the transaction, such as The statement

id=1

in the above

Transaction A

example has nothing to do with the execution of the second sentence, but it is a statement that can easily cause blocking, because in This row of data also needs to be locked in transaction B (it is frequently used in various transactions, such as the company's collection and payment account balance record, that is, **hot row**), but it is not Get the lock at the beginning of the transaction. Essentially, it shortens the time between lock acquisition and lock release. That is, the time the lock is held is shortened, thereby reducing the blocking caused by the lock.

Deadlock

Concept

Two threads are waiting for each other to release resources. In two transactions A and B.

Transaction A

obtained the lock of
• resource A

  .

  Transaction B
obtained the lock of
• resource B

  .

  Transaction A
goes to get the lock of
• resource B

  .

  Transaction B
goes to get the lock of
• resource A

  . Obviously, in steps 3 and 4, both transactions A and B want to get the lock, but neither can get it because the other party has not released the lock on the resource. This phenomenon is a deadlock.

Problems caused - deadlock

In InnoDB, there is a waiting time configuration for lock acquisition. If this time is exceeded, an error will be thrown. Exception, this time defaults to

50

seconds. Generally speaking, it is unacceptable to have an interface that takes 50 seconds to respond. innodb_lock_wait_timeout. Is it enough to set the configuration time shorter? For example, 1 seconds?

It should not be possible because it may affect your normal business. Perhaps your business causes your transaction execution time to be relatively long, exceeding 1 seconds. If this time is exceeded, an exception will be thrown and your normal business will be affected.

How to deal with the above problem

In InnoDB, there is also a configuration to automatically detect and handle deadlocks. It is enabled by default. In extreme cases, although it can solve the problem, it consumes a lot of

CPU

. The principle is that when a transaction is about to be locked, it will detect whether other concurrent threads have locked this resource. If a thread A is detected, then it will meet again To detect whether the dependencies of

thread A

are locked by other concurrent threads, and so on, and finally determine whether these locks will form a deadlock. It can be seen that the more threads, the greater the detection cost. innodb_deadlock_detect.

Only represents the processing and summary of this problem based on personal current learning: 1. Turn off deadlock detection and shorten the lock-holding time configuration to the estimated highest time, usually It will not exceed

15

seconds. After

15

seconds, a retry mechanism is required. 2. Turn on deadlock detection, control the number of concurrent connections at the application layer, use the connection pool to control the number of Mysql connections, and limit the maximum number of

Mysql

connections at the service layer . The above is a summary of how to reduce the performance impact of row locks.

The above is the detailed content of Let's talk about Mysql two-phase lock and deadlock. For more information, please follow other related articles on the PHP Chinese website!