mysql analyze table 锁表 mysql表锁怎么实现的

转载

karen 2023-08-07 07:58:43

mysql的锁类型，一般其实就是表锁、行锁和页锁。

一般myisam会加表锁，就是myisam引擎下，执行查询的时候，会默认加个表共享锁，也就是表读锁，这个时候别人只能来查，不能写数据的；然后myisam写的时候，也会加个表独占锁，也就是表写锁，别人不能读也不能写。

这个myisam引擎很少用了，所以细节问题就不需要深入的去了解了，面试的时候来这么一句就ok了。

myisam其实在实际生产中，就是在报表系统里用的是最多的，当年es和kylin没出来的时候，大数据系统计算好的报表数据，都是放mysql的myisam里的，一般就是每天凌晨导入一批数据，那个时候别人不需要查询，没人凌晨来看报表；然后白天也没有写入，就是别人纯查询，建好索引，查询性能还是不错的，单表支撑千万级别数据没问题。

innodb引擎一般用行锁，但是也有表锁。

innodb的行锁有共享锁（S）和排他锁（X），两种，共享锁就是，多个事务都可以加共享锁读同一行数据，但是别的事务不能写这行数据；排他锁就是，就一个事务可以写这行数据，别的事务只能读，不能写。

innodb的表锁，分成意向共享锁，就是说加共享行锁的时候，必须先加这个共享表锁；还有一个意向排他锁，就是说，给某行加排他锁的时候，必须先给表加排他锁。这个表锁，是innodb引擎自动加的，不用你自己去加(主要是为了判断锁是否冲突了，如果冲突了就等待，不冲突就加上共享锁或者排他锁)

insert、update、delete，innodb会自动给那一行加行级排他锁。

select，innodb啥锁都不加，因为innodb默认实现了可重复读，也就是mvcc机制，所以多个事务随便读一个数据，一般不会有冲突，大家就读自己那个快照就可以了，不涉及到什么锁的问题。

但是innodb从来不会自己主动加个共享锁的，除非你用下面的语句自己手动加个锁：

动加共享锁：select * from table where id=1 lock in share mode，那你就给那一行加了个共享锁，其他事务就不能来修改这行数据了。

动加排他锁：select * from table where id=1 for update，那你就给那一行加了个排他锁，意思就是你准备修改，别的事务就别修改了，别的事务的修改会hang住。这个要慎用，一般线上系统不用这个，容易搞出问题来。

对一行数据，如果有人在修改，会加个排他锁，然后你不能修改，你只能等着获取这把锁，但是这个时候你可以随便select，你就是查询你的事务开始之前那行数据的某个版本而已。然后如果你修改某行数据，会同时拿这个表的排他锁，但是呢，如果不同的事务修改不同的行，会拿不同行的行级排他锁，但是大家都会拿一个表的排他锁，ok，实际上innodb的表级排他锁可以随便拿，这个是没冲突的。

所以这个就是mysql innodb存储引擎的默认锁模式。相当于就是一行数据，同一个时刻只能一个人在修改，但是别人修改，你可以随便读，读是读某个版本的，走mvcc机制。

悲观锁和乐观锁

mysql里的悲观锁是走select * from table where id=1 for update，就这个，意思是我很悲观，我担心自己拿不到这把锁，我必须先锁死，然后就我一个人可以干这事儿，别人都干不了了，不能加共享锁，也不能加排他锁。

乐观锁，就是说我觉得应该没啥问题，我修改的时候感觉差不多可以获取到锁，不需要提前搞一把锁，我就先查出来某个数据，select id,name,version from table where id=1，接着再执行各种业务逻辑之后再修改，update table set name=’新值’,version=version+1 where id=1 and version=1，就是说每次修改，比较一下这条数据的当前版本号跟我之前查出来的版本号是不是一样的，如果是一样的就修改然后把版本号加1，否则就不会更新任何一行数据，此时就重新查询后再次更新。

一般悲观锁什么时候用呢？比如你查出来了一条数据，要在内存中修改后再更新到数据库中去，但是如果这个过程中数据被别人更新了，你是不能直接干这个操作的，这个时候，你就得走上面那个操作，查询之后就不让别人更新了，你搞完了再说。

但是真有这种场景，推荐你还是用乐观锁把，悲观锁实现简单一点，但是太有风险了，很容易很容易死锁，比如事务A拿了数据1的锁，事务B拿了数据2的锁，然后事务A又要获取数据2的锁就会等待，事务B又要获取数据1的锁，也会等待，此时尴尬了，死锁，卡死，互相等待，永不释放。