The use of Mysql row-level locks and deadlock prevention solutions

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

MySQL's InnoDB supports transactions and row-level locks, and row locks can be used to handle user withdrawals and other services. Deadlocks sometimes occur when using mysql locks. Prevent deadlocks. This article explains it to you through practical applications

1. Foreword

MySQL's InnoDB supports transactions and row-level locks, and row locks can be used to handle user withdrawals and other services. Deadlocks sometimes occur when using mysql locks. Prevent deadlocks.

2. MySQL row-level locks

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

　　Shared lock:

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 handling: shared lock is also called a read lock. All transactions can only perform read operations on it but not write operations. After adding a shared lock, other transactions cannot add exclusive If locked, only row-level locks can be added.

Usage:

SELECT `id` FROM table WHERE　id in(1,2)  LOCK IN SHARE MODE

All data in the result set will be added with shared locks

Exclusive lock:

Explanation of noun: If something adds a row to a certain With an exclusive lock, only this transaction can read and write to it. Other transactions cannot add any locks to it. Other processes can read but cannot perform write operations and need to wait for its release.

Usage:

SELECT `id` FROM mk_user WHERE id=1 FOR UPDATE

3. Example application

<?php
  $uid=$_SESSION[&#39;uid&#39;];
  //开启事务
  sql:begin
  //开启行级锁的排他锁
  sql:SELECT `coin` FROM user WHERE id=$uid FOR UPDATE 
  //扣除用户账户钱币
  $res=update user set coin=coin-value where id=1;
  if($res){
   //将用户的提现信息添加到提现表
   sql:insert into user values(null,"{$uid}",value);
   //判断添加结果
   if(add_cash_result){
    sql:commit
   }else{
    sql:rollback
   }
  }else{
   sql:rollback;
  }

In fact, the steps are not complicated, just start the transaction and judge each result if it is true, then submit it if it is false, and roll back. There is no problem with a single exclusive lock. When a table is associated with multiple exclusive locks, care must be taken to prevent deadlock.

4. Deadlock

　`id` Primary key index

　　`name` index Index

　　`age` Ordinary field

　The fundamental reason for the lock is that more than two processes require each other to release resources, so that the processes keep waiting. In code, it is because two or more transactions require the other to release resources.

There are four necessary conditions for deadlock: mutual exclusion conditions, loop conditions, request retention, and ineligibility. Each of them is indispensable. Correspondingly, as long as one of the conditions is violated, deadlock will not occur.

For example, the first statement of the following two statements will use the `name` index first, because name is not a primary key index, and the primary key index will also be used

The second statement will use the primary key index first , and then use the name index. If two statements are executed at the same time, the first statement executes the name index and waits for the second one to release the primary key index, and the second statement executes the primary key index and waits for the first name index, which causes a deadlock.

Solution: Transform the first statement so that it updates 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' );

The above is the content of the use of Mysql row-level locks and deadlock prevention solutions, and more related content Please pay attention to the PHP Chinese website (www.php.cn)!