• notice
  • Congratulations on the launch of the Sought Tech site

Used and deadlock prevention programs for mysql-level locks

I.Preface

    mysql's InnoDB, supports transactions and row-level locks, and row locks can be used to process user withdrawals and other businesses.When using mysql to lock, sometimes a deadlock will occur, so we must take care of deadlock prevention.

Second, MySQL row-level lock

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

    Share lock:

       Glossary: ​​Shared locks are also called read locks.All transactions can only read and cannot write to them.After the shared lock is added, other transactions can no longer add exclusive locks and can only add row-level locks.

      Usage:

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

Data in the result set will be shared lock

     Exclusive lock:

       Term explanation: If a thing adds an exclusive lock to a row, only this transaction can read and write it, other transactions cannot lock it, other processes can read it, but cannot write it.Need to wait for its release.

      Usage:

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

Three, example application

<?php
  $uid=$_SESSION['uid'];
 //Open the transaction
  sql:begin
 //Open exclusive lock of row-level lock
  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 withdrawal information to the withdrawal form
   sql:insert into user values(null,"{$uid}",value);
  //Judging the result of adding
   if(add_cash_result){
    sql:commit
   }else{
    sql:rollback
   }
  }else{
   sql:rollback;
  }

     In fact, the steps are not complicated, that is, open the transaction to determine if each result is true, then submit it as false and then roll back.There is no problem with a single exclusive lock.When a table is associated with multiple exclusive locks, care should be taken to prevent deadlocks.

Four.Deadlock

    `id`  primary key index

    `name` index index

    `age`  common field

    The root cause of deadlock is that two or more processes require each other to release resources, so that the processes have been waiting.In the code, because two or more transactions require the other to release resources.

     four necessary conditions for deadlock: mutual exclusion condition, loop condition, request retention, inalienability, none of them are indispensable, correspondingly, as long as one of the conditions is destroyed, the deadlock will not occur.

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

     The second statement is to 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 to release the primary key index, and the second executes the primary key index and waits for the first The name index of the article, which caused a deadlock.

    Solution: Modify the first statement to update it according to the primary key value

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

The above is the use of Mysql row-level locks and deadlock prevention solutions introduced by the editor.I hope it will be helpful to you.If you have any questions, please leave me a message.The editor will reply to you in time.of.Thank you very much for your support to the website!

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy atorvastatin 40mg & lt;a href="https://lipiws.top/"& gt;lipitor 10mg generic& lt;/a& gt; lipitor 20mg over the counter

Jmrlsg

2024-03-07

Leave a Reply

+