The use of Mysql row-level lock and the prevention scheme of deadlock _mysql

First, the preface

MySQL InnoDB, support transaction and row level lock, can use row lock to handle the business such as user's mentioning. The use of MySQL locks sometimes deadlock, to do a good job of deadlock prevention.

Second, MySQL row-level lock

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

Shared locks:

Noun Explanation: Shared lock is also called read lock, all transactions can only read and write operations, plus the shared lock other transactions can not be added exclusive lock can only add row-level locks.

Usage:

SELECT ' id ' from table WHERE ID in (1,2)  

Data from the result set will be shared locks

Exclusive locks:

Noun explanation: If a certain thing adds exclusive locks to a line, it can only read and write to the transaction, other transactions cannot lock it, other processes can read, cannot write, and wait for its release.

Usage:

SELECT ' id ' from Mk_user WHERE id=1 for UPDATE

III. Application of the example

<?php
  $uid =$_session[' uid '];
  Open transaction
  sql:begin
  //Unlock exclusive locks on row-level locks
  sql: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 present information to the list Sql:insert into user
   values (null, "{$uid}", value);
   Judge Add result
   if (add_cash_result) {
    sql:commit
   }else{
    sql:rollback
   }
  }else{
   sql: rollback;
  }

In fact, the steps are not complex, that is, to open the transaction to determine the results of the true submitted as false on the rollback. There is nothing wrong with a single exclusive lock, and when a table is associated with multiple exclusive locks, be careful to prevent deadlocks.

Four, Dead lock

' ID ' primary key index

' Name ' Index

' Age ' normal field

The root cause of deadlocks is that more than two processes require each other to release resources so that the process waits. The code is because two or more transactions require another resource to be freed.

Deadlock-generated four necessary conditions: mutual exclusion condition, loop conditions, request maintenance, inalienable, indispensable, relative to the destruction of one of the conditions of deadlock will not produce.

For example, the first statement of the following two statements takes precedence over the ' name ' index, because name is not a primary key index and a primary key index is used

The second statement uses the primary key index first, then the name index if two statements are executed at the same time, the first statement executes the name index waiting for the second release of the primary key index, and the second executes the primary key index waiting for the first name index, causing the deadlock.

　　　　Workaround: Transform the first statement to update it against the primary key value

#①
Update mk_user Set name = ' 1 ' where ' name ' = ' idis12 ';
#②
Update mk_user set name= ' where id=12;
After transformation,
update mk_user set name= ' 1 ' where id= (select ID from mk_user where name= ' idis12 ');

The above is a small set to introduce the MySQL row-level lock use and deadlock prevention solutions, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!