首页 / MYSQL / Mysql 锁总结

Mysql 锁总结

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql 锁总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4174字，纯文字阅读大概需要6分钟。

内容图文

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 锁总结

标签：evel   create   oca   ssl   tab   引擎   str   lock   local   

本文系统来源：https://www.cnblogs.com/alin-qu/p/8320077.html

内容总结

以上是互联网集市为您收集整理的Mysql 锁总结全部内容，希望文章能够帮你解决Mysql 锁总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/497671.html

来源：【匿名】