想知道MySQL里怎么给表上锁，避免数据冲突和操作混乱的那些事儿

想知道MySQL里怎么给表上锁，避免数据冲突和操作混乱的那些事儿，那咱们就从头开始，像聊天一样把它说清楚，你想象一下，你的数据库就像一个有好多抽屉的柜子，每个抽屉就是一个表，里面放着各种数据，如果好几个人（也就是多个程序或者多个用户连接）同时跑来开同一个抽屉，你拿一下我翻一下，没个规矩，那数据很可能就乱套了，比如你刚算好的钱数，被别人一改，结果就错了。“上锁”就像是给抽屉贴个纸条，写着“正在使用，请稍候”，告诉别人先别动,等我用完了你再动。

在MySQL里，尤其是在它最常用的InnoDB引擎下，锁的事情有点复杂，但我们可以抓住几个最核心、最常用的点来理解，根据MySQL官方手册（参考来源：MySQL 8.0 Reference Manual, 15.7.1 InnoDB Locking）和一些常见的数据库管理实践，锁主要可以分为两大类：表级锁和行级锁。

第一，表级锁。 顾名思义，就是把整个表给锁住，这是一种比较“粗暴”但有时又不得不用的方法，当你需要对整个表进行一个大规模的数据整理或者结构修改时，你肯定不希望在这个过程中有别人往表里插入新数据或者修改现有数据，不然就全乱套了,这时候你就可以用表锁。

怎么用呢？有两个主要的语句：

• LOCK TABLES table_name READ; 这叫读锁，我锁上之后，其他人都可以来读这个表，但是谁也不能写（包括修改、删除、插入），就像是我在抽屉上贴了个“只许看，不许摸”的条子。
• LOCK TABLES table_name WRITE; 这叫写锁，这个最厉害，我锁上之后，这个表就归我一个人用了，其他人既不能读也不能写，只能干等着，这就好比我把抽屉整个搬到自己工位上,别人连看都看不了。

用完锁之后，一定要记得用 UNLOCK TABLES; 把锁释放掉，不然别人就一直等着，数据库就好像“卡住”了一样，表锁的优点是简单直接，但缺点也很明显，因为它锁的粒度大，一下子锁住整个表，非常影响数据库同时处理多个请求的能力（也就是并发性能）,所以在高并发的场景下要非常小心地使用。

第二，行级锁。 这是InnoDB引擎的看家本领，也是它比老式的MyISAM引擎强大的地方，行级锁就精细多了，它只锁住你正在操作的那一行或者几行数据，而表里的其他行，别人照样可以随便读写，这就好比我只把我正在看的那一页文件用回形针别起来，抽屉里的其他文件别人依然可以取用,这大大提高了数据库的并发能力。

行级锁通常不是我们直接用一条像LOCK这样的命令去明确指定的,而是隐含在你执行的SQL语句里的。

• 当你执行一个 UPDATE（更新）语句或者 DELETE（删除）语句时，InnoDB会自动给你要修改的那行数据加上一个“排他锁”（X锁），只要这个锁在，其他任何事务都不能再给这行加锁（不管是读锁还是写锁）,也就是别的操作都得等着。
• 当你执行一个 SELECT ... FOR UPDATE 语句时，这表示你查这行数据是为了待会儿要修改它，所以InnoDB也会给这行加上排他锁,防止别人在你修改之前动它。
• 当你执行一个 SELECT ... LOCK IN SHARE MODE 语句时，这会给查询到的行加上一个“共享锁”（S锁），加上共享锁后，其他事务还可以来加共享锁读这行数据，但是不能加排他锁来修改它，这适合那种“我只需要确保我读的时候这行数据不被修改，但我自己也不一定改”的场景。

你看，行级锁是不是灵活多了？但凡事都有两面性，行级锁虽然精细，但管理起来也更复杂，如果使用不当，很容易造成一种叫做“死锁”的局面。

死锁是怎么回事呢？ 咱们举个简单的例子，假设事务A锁住了第1行数据，然后它想去锁第2行；事务B已经锁住了第2行，它又反过来想去锁第1行，这下就尴尬了，事务A等着事务B释放第2行，事务B又等着事务A释放第1行，两个人互相等着，谁也进行不下去，这就“死锁”了，好在InnoDB比较聪明，它有一个死锁检测机制，一旦发现这种互相等待的死循环，它会选择其中一个事务，让它回滚（也就是取消它已经做的所有操作），并报出一个错误，这样，另一个事务就可以继续进行了，被回滚的那个事务呢，通常我们的程序需要捕捉到这个错误,然后重新尝试执行一次。

到底该怎么用锁来避免冲突和混乱呢？ 这里有一些实用的建议（参考来源：常见的数据库设计与应用最佳实践）：

1. 尽量让事务短小精悍： 一个事务里不要包含太多、太耗时的操作，锁是在事务开始时根据语句需求获取，在事务提交或回滚时才释放的，事务时间越短，锁占用的时间就越短,发生冲突和死锁的几率就越低。
2. 访问数据的顺序要一致： 在编写多个可能会操作相同数据的程序时，尽量约定一个相同的顺序去访问表和数据行，比如都先更新A表再更新B表，都先操作id小的行再操作id大的行，这能大大降低死锁的概率，上面那个例子，如果事务A和事务B都约定好先锁第1行再锁第2行，那么事务B在锁第1行时就会等待事务A完成,而不会出现互相等待的局面。
3. 合理使用索引： 如果你的SQL语句的查询条件没有用到索引，InnoDB可能就没办法精准地只锁住需要的行了，它可能会退而求其次，锁住很多不必要的行，甚至升级为锁住整个表（这种情况叫“锁升级”），良好的索引设计有助于行级锁精准定位,减少锁的冲突范围。
4. 如果不是必须，避免显式加锁： 大多数业务场景下，依靠InnoDB默认的行级锁机制（配合合适的事务隔离级别，比如默认的“可重复读”）就已经足够了，不要动不动就去用 LOCK TABLES 或者 SELECT ... FOR UPDATE，除非你非常清楚为什么要这么做以及带来的影响，特别是 LOCK TABLES，它会隐式地提交你当前的事务，而且和事务一起用的时候行为会比较奇怪,所以现在一般不太推荐使用了。

MySQL里的锁，特别是InnoDB的锁，是个强大的工具，用好了能保证数据在多人同时操作时依然准确无误；用不好就会导致性能瓶颈或者死锁，核心思想就是：尽量使用更精细的行级锁，保持事务简短，操作数据时有个先后顺序的规矩,这样就能在很大程度上避免数据冲突和操作混乱的那些烦心事儿了。