云主机云服务器
vps云服务器mysql服务锁的机制
2023/12/10 382次锁机制为了解决访问冲突
锁类型
读锁
写锁
锁粒度
表级锁
行级锁
#查询变量
mysql> SHOW OPEN TABLES;
#in_use：正在使用
#Name_locked：处于重命名表期间
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sys | x$io_by_thread_by_latency | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
...
mysql> SHOW STATUS LIKE "table_lock%"; #查看表锁相关运行信息
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 107 | #立即获得表级别锁的次数
| Table_locks_waited | 0 | #不能立即获得表级别锁需要等待的次数
+-----------------------+-------+
2 rows in set (0.00 sec)
#创建练习数据
mysql> INSERT INTO execdb.t1 VALUES ('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM execdb.t1;
+----------+
| name |
+----------+
| zhangsan |
+----------+
1 row in set (0.00 sec)
#测试读锁(两终端测试，注意注释中的tty1和tty2)
mysql> LOCK TABLES execdb.t1 READ; #tty1操作给t1表加读锁
mysql> SELECT * FROM execdb.t1; #tty1可执行读操作
+----------+
| name |
+----------+
| zhangsan |
+----------+
1 rows in set (0.00 sec)
mysql> INSERT INTO execdb.t1 VALUES ('wangwu'); #tty1不可执行写操作，直接报错
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
mysql> SELECT * FROM execdb.t1; #tty2可执行读操作
mysql> INSERT INTO execdb.t1 VALUES('lisi'); #tty2执行写操作会等待
mysql> UNLOCK TABLES; #tty1直接解除锁操作，tty2卡住的写入操作执行
mysql> SELECT * FROM execdb.t1;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
+----------+
2 rows in set (0.00 sec)
#写锁测试(三终端测试，注意注释中的tty1、tty2和tty3)
mysql> LOCK TABLES execdb.t1 WRITE; #tty1操作给t1表加写锁
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM execdb.t1; #tty1可执行读操作
+----------+
| name |
+----------+
| zhangsan |
| lisi |
+----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO execdb.t1 VALUES ('wangwu'); #tty1可执行写操作
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM execdb.t1; #tty2执行读操作等待
mysql> INSERT INTO execdb.t1 VALUES('zhaoliu'); #tty3执行写操作等待
mysql> UNLOCK TABLES; #tty1执行解锁操作，tty2和tty3执行成功
最新发布
相关文章
版权声明
- 声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们996811936@qq.com进行处理。
