Use of mysql row-level locks and prevention of deadlocks, mysql

Use of mysql row-level locks and prevention of deadlocks, mysql
I. Preface

Mysql InnoDB supports transactions and row-level locks. You can use row locks to process user withdrawal and other services. Sometimes deadlocks occur when mysql locks are used, so we must prevent deadlocks.

Ii. MySQL row-Level Lock

Row-level locks are divided into shared locks and exclusive locks.

　　　　Shared lock:

A shared lock is also called a read lock. All transactions can only perform read operations but cannot write operations. After a shared lock is added, other transactions cannot add exclusive locks and can only apply row-level locks.

Usage: SELECT 'id' FROM table WHERE id in (1, 2) lock in share mode all data in the result set will be added with a SHARE LOCK

　　　　Exclusive lock:

　　　　　　Glossary: if an exclusive lock is applied to a transaction, the transaction can only read and write the transaction. Other transactions cannot lock the transaction, and other processes can read the transaction, you cannot perform write operations. You need to wait for the release.

Usage: SELECT 'id' FROM mk_user WHERE id = 1 FOR UPDATE

Iii. instance applications

<? Php $ uid = $ _ SESSION ['uid']; // enable transaction SQL: begin // The exclusive lock SQL for enabling row-level locks: SELECT 'coin 'FROM user WHERE id = $ uid for update // deduct user account coins $ res = update user set coin = coin-value where id = 1; if ($ res) {// Add the user's withdrawal information to the withdrawal table SQL: insert into user values (null, "{$ uid}", value); // determine the addition result if (add_cash_result) {SQL: commit} else {SQL: rollback ;}

In fact, the steps are not complex, that is, start the transaction to judge whether each result is true and commit as false and then roll back. There is no problem with a single exclusive lock. When a table is associated with multiple exclusive locks, be sure to prevent deadlocks.

Iv. deadlock

'Id' primary key index

'Name' index

'Age' common field

The root cause of the deadlock is that more than two processes require the other party to release resources, so that the process has been waiting. The code is because two or more transactions require the other to release resources.

Four necessary conditions for deadlock: mutex, loop conditions, request persistence, and non-deprivation. They are indispensable. The corresponding deadlock will not occur as long as one of the conditions is damaged.

For example, the first statement of the following two statements will give priority to the 'name' index. Because name is not a primary key index, the primary key index will also be used.

The second statement is to first use the primary key index, and then use the name index. If the two statements are executed simultaneously, the first statement executes the name index and waits for the second statement to release the primary key index, the second row executes the primary key index and waits for the first name index, resulting in a deadlock.

　　　　Solution:Modify the first statement to update it based on the primary key value.
　　　　

# ① Update mk_user set name = '1' where 'name' = 'idis12'; # ② update mk_user set name = '12' where id = 12; // update mk_user set name = '1' where id = (select id from mk_user where name = 'idis12') after transformation ');