文章目录
- 事故解析:
- 避免方法
- 涉及知识
以前怎么接触过大表,所以alter操作我都是一次性执行好几条的,这几条一下子干过去了
结果就是一直在转圈执行,因为alter产生的是表级排它锁,所以有关这几个表的查询更新操作全部处于阻塞状态,运营那边查询全部失效。
去数据库SHOW FULL PROCESSLIST
,发现State列出现大量“Waiting for table metadata lock”,紧张感拉满了。马上寻找这几个alter语句的进程id,在对应的“Info”列可以查看到对应表的操作,找到正在对该表进行操作的会话,记住“Id”, 执行KILL ID
。必须找对这几个,不然kill的其他的是没用的。还有一种解决方法就是重启你的mysql数据库实例(如果试过或者你胆子大的话)
紧张的要死,脑子都难以思考,遇到这种情况尽量让自己的同事也参与进来,或者给技术经理报告,看看怎么解决
事故解析:
在MySQL中,ALTER TABLE 语句通常会对表进行锁定以执行结构修改操作。具体而言,ALTER TABLE 语句会获取一个排他锁(Exclusive Lock),这意味着其他并发的写操作和读操作都将被阻塞,直到 ALTER TABLE 完成。因此,在执行 ALTER TABLE 期间,其他对该表的写操作(如 INSERT、UPDATE、DELETE)和读操作(如 SELECT)可能会被阻塞,直到 ALTER TABLE 完成。
这里有个注意的点:添加新列或修改列的数据类型通常需要较短的锁定时间,而重命名表或删除列可能需要更长的锁定时间。
避免方法
1.在执行 ALTER TABLE 之前,尽量预估该操作需要的时间,并在低峰期执行,以减少对其他事务的影响。如果你用的是大厂的云服务器,在数据库的实例中找到DBA助手(以华为云为例,其他厂家名字可能不同),可以查看实时的活跃会话有多少个,如果比较少那说明处于低峰期;或者去高级运维信息里查看数据库连总接数,根据数量多少去判断
2.如果对一个表有多个字段要修改或者删除,则分割成几条sql一个个去执行
3.调整事务隔离级别:在某些情况下,将事务隔离级别调整为较低的级别(如读已提交)可能会减少对锁的需求,从而减少对 SELECT 查询的阻塞。(不推荐,拆东墙补西墙罢了)
涉及知识
1.乐观锁不是mysql自带的要自己去实现,悲观锁涉及排它锁和共享锁。读(select)操作产生的是共享锁,其他的读操作可以继续加共享锁(但不能加排它锁,也就是不能增删改),通俗来说就可以一直select不会产生阻塞,而写操作( update/delete/insert)加的是排它锁,只允许自己一个锁,在它执行完其他操作都处于阻塞状态。
2.MySQL中InnoDB加锁机制
select… :不加锁
select… lock in share mode:加共享锁
select… for update:加排它锁
update/delete/insert默认加排它锁
3.在数据库中,"加锁"和"获取锁"通常是指同一个概念,即在执行操作之前,通过某种方式将锁应用于数据或资源,以控制并发访问和维护数据的一致性。
"加锁"是指在执行操作之前,显式地将锁应用于数据或资源。这意味着在进行读取或写入操作之前,需要先获取相应的锁。
"获取锁"是指在执行操作时,尝试获取已经存在的锁。如果锁可用且没有被其他事务持有,那么获取锁的操作会成功。