mysql 行级锁的使用以及死锁的预防

最新推荐文章于 2023-04-25 22:27:49 发布

明

最新推荐文章于 2023-04-25 22:27:49 发布

阅读量617

点赞数

分类专栏： mysql

mysql 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

一、前言

　　　　mysql的InnoDB,支持事务和行级锁，可以使用行锁来处理用户提现等业务。使用mysql锁的时候有时候会出现死锁，要做好死锁的预防。

　　二、MySQL行级锁

　　　　行级锁又分共享锁和排他锁。

　　　　共享锁：

　　　　　　名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

　　　　　　用法：SELECT `id` FROM table WHERE　id in(1,2) LOCK IN SHARE MODE 结果集的数据都会加共享锁

　　　　排他锁：

　　　　　　名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

　　　　　　用法：SELECT `id` FROM mk_user WHERE id=1 FOR UPDATE

　　三、实例应用

<?php
        $uid=$_SESSION['uid'];
        //开启事务
        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;
        }

　　　　其实步骤不复杂，就是开启事务判断各个结果为真就提交为假就回滚。单个排他锁没有什么问题，当一个表关联到多个排他锁的时候要注意防止发生死锁。

　　四、死锁

　　　　`id` 主键索引

　　　　`name` index 索引

　　　　`age` 普通字段

　　　　死锁产生的根本原因是两个以上的进程都要求对方释放资源，以至于进程都一直等待。在代码上是因为两个或者以上的事务都要求另一个释放资源。

　　　　死锁产生的四个必要条件：互斥条件、环路条件、请求保持、不可剥夺，缺一不可，相对应的只要破坏其中一种条件死锁就不会产生。

　　　　例如下面两条语句第一条语句会优先使用`name`索引，因为name不是主键索引，还会用到主键索引

　　　　第二条语句是首先使用主键索引,再使用name索引如果两条语句同时执行，第一条语句执行了name索引等待第二条释放主键索引，第二条执行了主键索引等待第一条的name索引，这样就造成了死锁。

　　　　解决方法：改造第一条语句使其根据主键值进行更新
　　　　

#①
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' );

MySQL innodb存储引擎使用与oracle相同的行锁机制，对如何查看系统中存在的行锁情况在下面的实验中，将可以看到。下面是测试过程:

session 1:更新记录

mysql> set autocommit=off;

Query OK, 0 rows affected (0.01 sec)

mysql> update t1 set email='test@test.com' where id=0;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

session 2也更新相同的记录,出现等待

mysql> set autocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set email='abc' where id=0;

session 3:查看系统等待事件:

mysql> show status like '%lock%';

+-------------------------------+---------+

| Variable_name | Value |

+-------------------------------+---------+

| Com_lock_tables | 0 |

| Com_unlock_tables | 0 |

| Innodb_row_lock_current_waits | 1 | --这里

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 1 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 14497 |

| Key_blocks_used | 0 |

| Qcache_free_blocks | 1 |

| Qcache_total_blocks | 1 |

| Table_locks_immediate | 2070991 |

| Table_locks_waited | 2 |

+-------------------------------+---------+

14 rows in set (0.01 sec)

session 1:提交记录

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

session 2:update立刻完成

mysql> update t1 set email='abc' where id=0;

Query OK, 4 rows affected (2 min 43.44 sec)--这么长时间完成更新操作

Rows matched: 4 Changed: 4 Warnings: 0

session 3:再次查看系统等待事件

mysql> show status like '%lock%';

+-------------------------------+---------+

| Variable_name | Value |

+-------------------------------+---------+

| Com_lock_tables | 0 |

| Com_unlock_tables | 0 |

| Innodb_row_lock_current_waits | 0 | --这里为0

| Innodb_row_lock_time | 163436 |

| Innodb_row_lock_time_avg | 163436 |

| Innodb_row_lock_time_max | 163436 |

| Innodb_row_lock_waits | 1 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 14497 |

| Key_blocks_used | 0 |

| Qcache_free_blocks | 1 |

| Qcache_total_blocks | 1 |

| Table_locks_immediate | 2070991 |

| Table_locks_waited | 2 |

+-------------------------------+---------+

14 rows in set (0.01 sec)

查询会话session 1,session 2的连接ID

session 1:

mysql> status;

--------------

mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id: 15

session 2:

mysql> status;

--------------

mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id: 13

在上面的session 1尚没有提交的时候,可以执行下列命令，查看一些事务阻塞信息

mysql> show innodb status\G;

------------

TRANSACTIONS

------------

Trx id counter 0 3852351

Purge done for trx's n:o < 0 3852350 undo n:o < 0 0

History list length 11

Total number of lock structs in row lock hash table 7

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, OS thread id 15

MySQL thread id 18, query id 2071119 localhost root

show innodb status

---TRANSACTION 0 3852350, ACTIVE 6 sec, OS thread id 14 starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 320

MySQL thread id 13, query id 2071118 localhost test Updating --这里可以看到等待者

update t1 set email='abc' where id=0 --这里可以看到等待者正在执行的SQL

------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 32782 n bits 1056 index `idx_t1_id` of table `dc_test/t1` trx id 0 3852350 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000000; asc ;; 1: len 6; hex 00000196fe5d; asc ];;

------------------

---TRANSACTION 0 3852348, ACTIVE 391 sec, OS thread id 12

7 lock struct(s), heap size 1024, undo log entries 4

MySQL thread id 15, query id 2071117 localhost test

///---------------------------

set autocommit=off;

rollback;

commit;

参考：

https://www.cnblogs.com/IT--Loding/p/6204093.html

https://blog.csdn.net/lengzijian/article/details/7234909

明

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql 行级锁的使用以及死锁的预防

一、前言　　　　mysql的InnoDB,支持事务和行级锁，可以使用行锁来处理用户提现等业务。使用mysql锁的时候有时候会出现死锁，要做好死锁的预防。　　二、MySQL行级锁　　　　行级锁又分共享锁和排他锁。　　　　共享锁：　　　　　　名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。　　　...
复制链接

扫一扫

专栏目录