ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL 锁问题

MySQL 锁问题

原创 MySQL 作者：coredumped 时间：2015-01-08 20:30:55 0 删除 编辑
  • MyISAM只支持表锁:
分析表锁的情况
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 158   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 72    |
| Table_open_cache_misses    | 12    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

Table_locks_waited 高说明有较严重的表级锁争用


session_1 session_2

mysql> lock table emp write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
+------+-------+----------+
3 rows in set (0.00 sec)

mysql> insert into emp values(111,'dhy',111);
Query OK, 1 row affected (0.01 sec)

 		mysql> select * from emp;
等待
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec) 		mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
+------+-------+----------+
4 rows in set (1 min 39.47 sec)

lock tables 时加上local 选项，作用就是在MyISAM表中并发插入条件下，允许其它用户在表尾并发插入。
lock tables 给表显示加锁时，只能访问加锁的这些表，不能访问未加锁的表。

session_1 session_1
mysql> lock table emp read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
+------+-------+----------+
4 rows in set (0.00 sec) 		mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
+------+-------+----------+
4 rows in set (0.00 sec)
mysql> select * from test;
ERROR 1100 (HY000): Table 'test' was not locked with LOCK TABLES 		mysql> select * from test;
+-----+
| id  |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)
mysql> insert into emp values(111,'dhy',123);
ERROR 1099 (HY000): Table 'emp' was locked with a READ lock and can't be updated 		mysql> insert into emp values(111,'dhy',123);
等待。。。。。。。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec) 		mysql> insert into emp values(111,'dhy',123);
Query OK, 1 row affected (35.66 sec)

lock tables 的时候别名也需要锁定。

并发插入
session_1 session_2
mysql> lock table emp read local;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into emp values(111,'dhy',123);
ERROR 1099 (HY000): Table 'emp' was locked with a READ lock and can't be updated 		mysql> insert into emp values(111,'dhy',124);
Query OK, 1 row affected (0.00 sec)
mysql> update emp set ename = 'core' where store_id='124';
等待.....
mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
| 111  | dhy   |      123 |
| 111  | dhy   |      123 |
+------+-------+----------+
6 rows in set (0.00 sec)
访问不到新插入数据
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec) 		mysql> update emp set ename = 'core' where store_id='124';
Query OK, 1 row affected (39.43 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from emp;
+------+-------+----------+
| id   | ename | store_id |
+------+-------+----------+
| 111  | dhy   |      111 |
| 111  | dhy   |      111 |
| 111  | dhy   |      112 |
| 111  | dhy   |      113 |
| 111  | dhy   |      123 |
| 111  | dhy   |      123 |
| 111  | dhy   |      123 |
| 111  | core  |      124 |
+------+-------+----------+
8 rows in set (0.00 sec)

MyISAM颗粒度
MySQL认为写请求一般比读请求更重要。
可以设置 max_write_lock_count 参数，当一个表读锁达到这个值后，MySQL就暂时降低优先级。


  • InnoDB锁问题
4种隔离级别比较

获取InnoDB行锁争用情况
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

通过information_schema数据库中表了解所等待情况
mysql> select * from information_schema.innodb_locks\G
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits\G
Empty set (0.00 sec)

通过设置InnoDB Monitors 观察锁冲突情况
mysql> create table innodb_monitor ( a int) ;
Query OK, 0 rows affected, 1 warning (0.09 sec)mysql> show  engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
。。。。。。。。。。。。。。。。。

停止监视器
mysql> drop table innodb_monitor;
Query OK, 0 rows affected (0.01 sec)

InnoDB的行所模式及加锁方法
显示给记录集增加共享锁或排它锁
select * from table_name .... lock in share mode --共享锁
select * from table_name .... for update --排它锁
InnoDB 存储引擎的共享锁例子
session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id,first_name, last_name from actor where actor_id = 178;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)

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

mysql> select actor_id,first_name, last_name from actor where actor_id = 178;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)
mysql> select actor_id,first_name, last_name from actor where actor_id = 178 lock in share mode ;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)


别的session 可以查询并加共享锁
mysql> select actor_id,first_name, last_name from actor where actor_id = 178 lock in share mode ;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)
mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
等待......

别的进程更新就会死锁退出
mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
Query OK, 1 row affected (40.24 sec)
Rows matched: 1  Changed: 1  Warnings: 0

InnoDB 存储引擎的排它锁例子
session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id,first_name, last_name from actor where actor_id = 178;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE T  |
+----------+------------+-----------+
1 row in set (0.00 sec) 		mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id,first_name, last_name from actor where actor_id = 178;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)
添加加排它锁
mysql> select actor_id,first_name, last_name from actor where actor_id = 178 for update;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE T  |
+----------+------------+-----------+
1 row in set (0.00 sec) 		别的session 可以查询该记录，但是不能添加排它锁和排它锁，会等待获得锁
mysql> select actor_id,first_name, last_name from actor where actor_id = 178;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE    |
+----------+------------+-----------+
1 row in set (0.00 sec)

mysql> select actor_id,first_name, last_name from actor where actor_id = 178 for update;
.....等待
当前session对锁定的记录进行更新操作，更新后释放锁
mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

其它进程获得锁
mysql> select actor_id,first_name, last_name from actor where actor_id = 178 for update;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      178 | LISA       | MONROE T  |
+----------+------------+-----------+
1 row in set (8.80 sec)

InnoDB行锁实现方式
InnoDB行错是通过给索引项添加锁实现，如果没有索引则通过隐藏的聚簇索引来进行加锁
Record lock:对索引项加锁。
Gap lock:对索引项直接的“间隙”进行加锁
Next-Key lock:前两者集合

InnoDB存储引擎在不使用索引的情况下对全部记录加锁
session_1 session_1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec) 		mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id =2;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_no_index where id  = 1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id = 2 for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
.....等待
在没有索引的情况下，InnoDB会对所有记录加锁。

InnoDB存储引擎在表在有索引使用行锁的例子
session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_with_index where id =1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec) 		mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_with_index where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_with_index where id =1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id = 2 for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

Next-Key锁
select * from emp where empid > 100 for update;
这个语句不仅会对empid = 101的值进行加锁而且还会对大于empid>101的(不存在记录) “间隙”加锁。
session_1 session_2
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec) 		mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp1 where id > 4 for update
    -> ;
+------+
| id   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

插入不存在的数据会出现等待
mysql> insert into  emp1 values(6);
.....等待
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  emp1 values(6);
Query OK, 1 row affected (50.28 sec)

恢复和复制的需要，对InnoDB锁机制的影响
MySQL通过BINLOG记录执行成功的INSERT,UPDATE,DELETE等更新数据的SQL，MySQL5.6支持三种日志格式，基于语句的日志格式SBL，基于行日志格式的RBL和混合格式。
支持四种复制模式:
1.基于SQL语句的复制SBR:最早支持的复制模式。
2.基于行数据的复制RBR:MySQL5.1以后开始支持的复制模式。
3.混合复制模式:对于安全的SQL采用基于SQL语句的复制模式，对于非安全的SQL采用居于行的复制模式。
4.使用全局事物ID(GTIDs)
 CTAS操作给原表加锁例子
session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
Empty set (0.00 sec)
mysql> select * from emp1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec) 		mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
Empty set (0.00 sec)
mysql> select * from emp1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
mysql> insert into emp select * from emp1;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> update emp1 set id = '12' where id = '2';
.....等待
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp1 set id = '12' where id = '2';
Query OK, 0 rows affected (44.37 sec)
Rows matched: 0  Changed: 0  Warnings: 0
MySQL给原表添加了共享锁，为了保证恢复和复制的正确性。

InnoDB表锁需要注意的地方:
1.只有autocommit = 0,innodb_table_locks=1的时候才可以实现
2.lock tables 对InnoDB加锁时候要用commit或者rollback释放锁。


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30024909/viewspace-1395173/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL死锁
下一篇： MySQL日志
请登录后发表评论 登录
全部评论
coredumped

注册时间：2014-11-16

  • 博文量
    25
  • 访问量
    57960

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593