锁

部分总结参考博客

http://b.codejs.cc/articles/2017/10/23/1508749325215.html

http://blog.csdn.net/cug_jiang126com/article/details/50544728

MyISAM 只支持表锁

show create table crm_member; ##查看引擎

alter table crm_member engine = MyISAM; ##更改表引擎

读锁演示

Session 1 读锁其它session可以读不可写

lock table crm_member read;

Session 1 锁等待时间设置

select @@global.lock_wait_timeout; ##查看锁等待时间设置

set @@global.lock_wait_timeout = 20; ##设置锁等待时间为20秒

Session 2 查询数据不影响

MariaDB [member]> select name from crm_member where id = 4;

+------+

| name |

+------+

| 222  |

+------+

1 row in set (0.00 sec)

Session 2 更新数据

MariaDB [member]>

MariaDB [member]> update crm_member set name = '222' where id = 4;

## 进入锁等待

Session 1 查看进程

show processlist;

| 97 | root | localhost           | member | Query   |   120 | Waiting for table level lock | update crm_member set name = '222' where id = 4 |    0.000 |

## 等待一个表级别的锁

Session 1 释放锁

MariaDB [member]> unlock tables;

Query OK, 0 rows affected (0.00 sec)

Session 2 获得锁立即更新数据

MariaDB [member]> update crm_member set name = '222' where id = 4;

Query OK, 0 rows affected (3 min 9.38 sec)

Rows matched: 1  Changed: 0  Warnings: 0

Session 2 锁等待超时

MariaDB [member]> update crm_member set name = '222' where id = 4;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

写锁演示

Session 1 添加写锁

MariaDB [member]> lock table crm_member write;

Query OK, 0 rows affected (0.00 sec)

Session 1 添加写锁

MariaDB [member]> lock table crm_member write;

Query OK, 0 rows affected (0.00 sec)

Session 2 读数据

MariaDB [member]> select name from crm_member where id = 4;

Session 1 查看进程

MariaDB [member]> show processlist;

| 109 | root | localhost           | member | Query   |     2 | Waiting for table metadata lock | select name from crm_member where id = 4 |    0.000 |

## 等待一个元数据的锁

Session 2 读数据锁等待超时

MariaDB [member]> select name from crm_member where id = 4;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB 锁既支持表锁（与MyISAM相同）也支持行锁。

排它锁演示

方式1 Session 1 直接update 其它session不可以读不可写除非快照读

MariaDB [member]> begin;

Query OK, 0 rows affected (0.00 sec)

MariaDB [member]> update crm_member set name = 'aaaaaa' where id = 4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

方式2 Session 1 for-update 其它session不可以读不可写除非快照读

MariaDB [member]> begin;

Query OK, 0 rows affected (0.00 sec)

MariaDB [member]> select name from crm_member where id = 4 for update;

Session 2 方式1 事务方式开始更新操作相同的记录

MariaDB [member]> begin;

Query OK, 0 rows affected (0.00 sec)

MariaDB [member]> update crm_member set name = 'bbb' where id = 4;

## 进入锁等待

Session 2 方式2 普通更新操作

MariaDB [member]> update crm_member set name = 'bbb' where id = 4;

Session 1 查看进程

MariaDB [member]> show processlist;

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

| Id  | User | Host                | db     | Command | Time  | State    | Info                                            | Progress |

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

| 109 | root | localhost           | member | Query   |     2 | updating | update crm_member set name = 'ccc' where id = 4 |    0.000 |

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

## 有一个更新等待

Session 2 锁等待超时

MariaDB [member]> update crm_member set name = 'bbb' where id = 4;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session 1 查看数据已经更改

MariaDB [member]> select name from crm_member where id = 4;

+------+

| name |

+------+

| ccc  |

+------+

Session 2 读取到的数据没有更改只能读取未提交版本的数据

MariaDB [member]> select name from crm_member where id = 4;

+------+

| name |

+------+

| bbb  |

+------+

巴特西

Mysql 锁总结

锁

MyISAM 只支持表锁

读锁演示

写锁演示

InnoDB 锁既支持表锁（与MyISAM相同）也支持行锁。

排它锁演示

最新文章

热门文章

巴特西

Mysql 锁总结

锁

MyISAM 只支持表锁

读锁演示

写锁演示

InnoDB 锁 既支持表锁（与MyISAM相同） 也支持 行锁。

排它锁演示

最新文章

热门文章

InnoDB 锁既支持表锁（与MyISAM相同）也支持行锁。