解决锁表问题并优化sql

这两天遇到一个锁表的问题,在寻找解决方案时牵扯到的联合索引以及优化sql,最终使用联合索引解决锁表的问题

首先说一下锁表的概念,在sql执行完毕提交事务之前会对当前行或表上锁,对数据起到一个保护作用,防止数据出现偏差、错误的情况.在当前线程执行完毕之前不会放其他线程进来.当你的业务代码需要的执行时间较久时,如果此时有其他业务进来会导致无法执行代码,产生异常.锁表分为"行锁",“表锁”,顾名思义只锁这一行该条数据,表锁则将整个表锁起,任何数据无法操作(select除外,select不受影响).

导致sql上锁的情况:

1. 无索引,sql需要检索整个数据表来操作时则会锁表(表级锁)

2. where条件为多个key,其中任一key无索引则会锁表(表级锁)

3. 对数据进行update,insert,delete时会行锁,无论是否有索引(独占锁)

4. select为共享锁,不会占用数据(共享锁)

死锁:

1. 当a操作x表并准备访问y表时,b已经在操作y表并正在访问x表时就会导致死锁,因为a必须等待b开锁才能访问y表,而b又必须需要等待a开锁才能访问x表就形成了死锁(死锁)

2. 当a查询到x数据然后修改x数据时,这时候有并发出现,b用户也想修改x数据,由于a从共享锁想要升级成独占锁,而b用户也在这,导致a无法升级成独占锁,而b也需要等待a将共享锁释放,但此时a的事务也没执行完,无法改变,就导致死锁(死锁)

解决方案:

1. 定位锁表原因和问题

2. 加快业务执行速度避免上锁

3. 增加索引避免造成锁表

4. 读写分离(环境问题无法实现)

5. 增加事务锁,利用事务的4个特性’ACID’

6. 隔离级别尽量低

7. 排它锁,尽量避免死锁

介绍完锁表然后来说一下我遇到的问题以及解决方案然还顺带优化了sql(手动滑稽hhh嘚瑟啊)

某天,闲得蛋疼瞎翻翻数据发现…这几天的数据怎么这么少呢???然后查看日志文件发现好多个锁表的报错…随后开始排查原因.

因为代码是设置有事务的,考虑过锁表问题,但没想到还是发生了,排查半天发现在我的业务代码中执行的update操作有五个条件,其中有两竟然没有索引,就导致了业务代码执行很慢,且发生表锁(因为没有索引,sql需要全表检索,导致表锁),而产生报错原因在于长事务，阻塞DDL.

最终解决方案:

1. 对update的5个条件增加联合索引,大大提升执行速度,且避免了表锁!更大几率错开其他线程访问的数据(除非真就那么巧吧hhh)
2. 将业务代码定时至凌晨执行,避开用户使用期间发生锁

联合索引不展开细说,底层是一个b+树,只需要知道能够减少写操作以及磁盘开销(相比于直接建立5个索引),使用联合索引时可以去查看一下"最左前缀原则"有利于你设计sql及优化你的sql(这也算是一个优化sql的方法),联合索引还会进行排序!

(图自网络)

总结:

1. 在多条件查询下联合索引的速度是绝对较快的.且使用联合索引后会对第二个键进行排序,不用再去orderby

2. 注意"索引失效"导致全表检索从而锁表

3. 对业务代码尽量加快速度,加索引,减少占用时间

4. 避免表锁!增加索引,行锁可以一定几率避开其他用户

5. 避开用户使用时段进行执行长业务代码

6. 新建表进行分离(有条件同学可以尝试,我因环境问题该方案无法执行)

转载请署名-flrjcx