- 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)
+----------------------------+-------+
| 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)
+-------------------------------+-------+
| 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)
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 ***************************Query OK, 0 rows affected, 1 warning (0.09 sec)mysql> show engine innodb status \G
Type: InnoDB
Name:
Status:
=====================================
。。。。。。。。。。。。。。。。。
停止监视器
mysql> drop table innodb_monitor;
Query OK, 0 rows affected (0.01 sec)
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博客 ” ,链接:http://blog.itpub.net/30024909/viewspace-1395173/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30024909/viewspace-1395173/