文档章节

MYSQL的伪行级锁

kenyon_君羊
 kenyon_君羊
发布于 2012/08/03 20:16
字数 1278
阅读 2091
收藏 26

     接触mysql之前一直以为mysql的innodb引擎所支持的行级锁和oracle,postgresql是一样的,是对数据行上加锁。但其实是不一样的,理解不一样,对mysql的锁机制就容易产生误解。innodb的行级锁实际上是基于索引项来锁定的。以下是测试机上的验证测试过程 

一.数据准备

mysql> use test;
Database changed
mysql> show create table t_kenyon \G
*************************** 1. row ***************************
       Table: t_kenyon
Create Table: CREATE TABLE `t_kenyon` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_lock%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_lock_wait_timeout       | 50    | 
| innodb_locks_unsafe_for_binlog | OFF   | 
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)
以上是测试表t_kenyon,设置提交方式为手动提交.

二.过程(开启两个session,分别设置autocommit=off)

1.session 1 update
mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_kenyon;
+------+
| id   |
+------+
|  999 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)
2.session 2 update
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)

mysql> update t_kenyon set id = 88888 where id = 345;
第二个session更新的值是345,但是也一直被阻塞,直到session1被rollback或者commit,如果session1未做回滚或者提交,session2中的该阻塞在超出mysql的锁时间限制时自动回滚,该参数为innodb_lock_wait_timeout,默认值50秒 现象如下
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

加索引后的测试 
3.session 1 update

mysql> create index ind_kenyon on t_kenyon(id);
Query OK, 0 rows affected (28.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_kenyon;
+------+
| id   |
+------+
|   78 |
|   78 |
|  123 |
|  345 |
|  789 |
|  999 |
+------+
6 rows in set (0.00 sec)
4.session 2 update
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|   78 |
|   78 |
|  123 |
|  345 |
|  789 |
+------+
6 rows in set (0.00 sec)

mysql> update t_kenyon set id = 7777 where id = 345;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|   78 |
|   78 |
|  123 |
|  789 |
| 7777 |
+------+
6 rows in set (0.00 sec)
执行计划
mysql> explain select * from t_kenyon where id = 345 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_kenyon
         type: ref
possible_keys: ind_kenyon
          key: ind_kenyon
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)
可以看到加了索引后,不同的数据更新并没有被阻塞,实现了真正意义上行锁
三.行级锁的扩展限制
1.相同索引阻塞
mysql> select * from t_kenyon;
+------+---------+
| id   | name    |
+------+---------+
|    1 | kenyon  |
|  123 | francs  |
|  789 | lighten |
|  345 | mood    |
|   78 | opp     |
|   78 | opp     |
|  789 | james   |
+------+---------+
7 rows in set (0.00 sec)

mysql> explain select * from t_kenyon where id =1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_kenyon
         type: ref
possible_keys: ind_kenyon
          key: ind_kenyon
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> update t_kenyon set name = 'john' where id = 789 and name = 'james';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
2.在另外的会话中同样对id=789的另一条数据进行更新
mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='lighten';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='xxxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这时我们可以看到即使有索引,不同数据的更新也被阻塞了,哪怕没有检索到其他数据,只要用到了同一个索引键值都会被阻塞,如上述name='xxxx'中是没有的,也被阻塞了。 所以可以引申开来,只要是在一个session中>或者<某个值的查询中索引项被扫到了,都将产生锁,并阻塞其他事务对之前锁中内容的任何更新。

3.不同事务对表的不同索引锁定不同行
a.session 1
mysql> select * from t_kenyon;
+------+------------+
| id   | name       |
+------+------------+
|    1 | kenyon     |
|  123 | francs     |
|  345 | mood       |
|   78 | opp        |
|   78 | opp        |
|  789 | lighten    |
|  789 | james      |
|  899 | jiangkaish |
|  902 | song       |
|  907 | hu         |
|  997 | wenjiab    |
+------+------------+
11 rows in set (0.00 sec)

mysql> update t_kenyon set name = 'kenyon_god' where id = 789;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
2.session 2更新另一个
mysql> update t_kenyon set name = 'koko' where name = 'hu';
3.在session 1中查看进程
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+-----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State    | Info                                                |
+----+------+-----------+------+---------+------+----------+-----------------------------------------------------+
|  8 | root | localhost | test | Query   |    0 | NULL     | show processlist                                    |
|  9 | root | localhost | test | Query   |    8 | Updating | update t_kenyon set name = 'koko' where name = 'hu' |
| 10 | root | localhost | NULL | Sleep   |  113 |          | NULL                                                |
+----+------+-----------+------+---------+------+----------+-----------------------------------------------------+
3 rows in set (0.00 sec)
可以看到被锁住了。

4.对name项加索引达到目的
a.会话1
mysql> alter table t_kenyon add index ind(name);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t_kenyon set name = 'kenyon_god' where id = 789;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0
b.会话2
mysql> update t_kenyon set name = 'koko' where name = 'hu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

至此对表的不同行数据也达到了更新的目的。

四.总结
MYSQL的innodb引擎在查询过程中如果有基于索引扫描,可以实现行级锁定,但是该行级锁有相当的限制,查询未使用索引或表中未建索引时会触发表锁,数据量大、查询效果慢的情况下该现象将被放大,严重会导致DB奔溃

参考:http://www.csharpwin.com/dotnetspace/10861r8934.shtml

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(8)

懵懂一时
懵懂一时
学习下
mark35
mark35

引用来自“kenyon”的评论

引用来自“mark35”的评论

mysql就一废柴,玩玩尚可,不堪重用

mysql与ORACLE,PG等比也都有其优势,是一款不错的开源软件,说是废柴有些过了,呵呵。

呵呵,是有点夸张。不过也非开口胡说,现实情况中让人就如此纠结~
kenyon_君羊
kenyon_君羊

引用来自“mark35”的评论

mysql就一废柴,玩玩尚可,不堪重用

mysql与ORACLE,PG等比也都有其优势,是一款不错的开源软件,说是废柴有些过了,呵呵。
mark35
mark35
mysql就一废柴,玩玩尚可,不堪重用
kenyon_君羊
kenyon_君羊

引用来自“大东哥”的评论

引用来自“kenyon”的评论

引用来自“大东哥”的评论

在my.cnf中,加上:
innodb_locks_unsafe_for_binlog=1

设置该值true,可以减少上面的table lock,但是行级锁还是基于index扫描,相同索引键值的不同数据更新仍然要加锁....thanks for your answer

嗯,是这么个状况,而且设这个参数有副作用。

我这边设置的binlog_format=mixed,额,你们相关参数实际是如何设置的?
大东哥
大东哥

引用来自“kenyon”的评论

引用来自“大东哥”的评论

在my.cnf中,加上:
innodb_locks_unsafe_for_binlog=1

设置该值true,可以减少上面的table lock,但是行级锁还是基于index扫描,相同索引键值的不同数据更新仍然要加锁....thanks for your answer

嗯,是这么个状况,而且设这个参数有副作用。
kenyon_君羊
kenyon_君羊

引用来自“大东哥”的评论

在my.cnf中,加上:
innodb_locks_unsafe_for_binlog=1

设置该值true,可以减少上面的table lock,但是行级锁还是基于index扫描,相同索引键值的不同数据更新仍然要加锁....thanks for your answer
大东哥
大东哥
在my.cnf中,加上:
innodb_locks_unsafe_for_binlog=1
MySQL数据库的锁机制

在并发访问情况下,很有可能出现不可重复读等等读现象。为了更好的应对高并发,封锁、时间戳、乐观并发控制(乐观锁)、悲观并发控制(悲观锁)都是并发控制采用的主要技术方式。 锁分类 ①、按操...

Javahih
2018/01/02
0
0
MySQL中的行级锁,表级锁,页级锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 在数据库的锁机制中介绍过,在DBMS中,可以按照锁的粒度把数据库锁分为行级...

Hosee
2016/06/01
469
0
MySQL中的共享锁与排他锁

在MySQL中的行级锁,表级锁,页级锁中介绍过,行级锁是Mysql中锁定粒度最细的一种锁,行级锁能大大减少数据库操作的冲突。行级锁分为共享锁和排他锁两种,本文将详细介绍共享锁及排他锁的概念、...

steven
2016/09/23
32
0
MySQL_S锁_X锁_read lock_write lock

MySQLS锁X锁read lockwrite lock 共享锁和排它锁 MySQL的锁系统:shared lock和exclusive lock(共享锁和排他锁,也叫读锁和写锁,即read lock和write lock) 读锁是共享的,或者说是相互不阻...

秋风醉了
2014/04/02
0
0
MySQL数据库 锁机制简介

MySQL数据库 锁机制简介 数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制...

LYQ1990
2016/04/29
60
0

没有更多内容

加载失败,请刷新页面

加载更多

java框架学习日志-13(Mybatis基本概念和简单的例子)

在mybatis初次学习Mybatis的时候,遇到了很多问题,虽然阿里云的视频有教学,但是视频教学所使用的软件和我自己使用的软件不用,我自己用的数据库是oracle数据库,开发环境是idea。而且视频中...

白话
今天
4
0
Java基础:String、StringBuffer和StringBuilder的区别

1 String String:字符串常量,字符串长度不可变。Java中String是immutable(不可变)的。 String类的包含如下定义: /** The value is used for character storage. */private final cha...

watermelon11
今天
3
0
mogodb服务

部署MongoDB 官网: https://www.mongodb.com/download-center/community 创建mongo数据目录 mkdir /data/mongodb 二进制部署 wget -c https://fastdl.mongodb.org/linux/mongodb-linux-x8......

以谁为师
昨天
5
0
大神教你Debian GNU/Linux 9.7 “Stretch” Live和安装镜像开放下载

Debian项目团队于昨天发布了Debian GNU/Linux 9 "Stretch" 的第7个维护版本更新,重点修复了APT软件管理器中存在的安全漏洞。在敦促每位用户尽快升级系统的同时,Debian团队还发布了Debian ...

linux-tao
昨天
4
0
PHP 相关配置

1. php-fpm的pool 编辑php-fpm配置文件php-fpm.con vim /usr/local/php/etc/php-fpm.conf //在[global]部分增加以下内容 include = etc/php-fpm.d/*.conf # 相当与Nginx的虚拟主机文件 “vho......

Yue_Chen
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部