MYSQL高并发与锁机制

MySQL常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

 

innodb和mysam

目前大家用的mysql中表的engine都是innodb,很少会用mysam了,就是因为在更新数据的时候mysam会锁表,导致并发更新的时候只能一个个排队更新,对效率影响很大,而innodb则是行锁,大家只更新自己的那条,冲突的时候才排队,这样效率会高很多.

 

事务处理中行锁的实例体验：

set autocommit = 0;update fa_admin set nickname = 'cyy' where id = 2;commit;

 

innodb的索引与行级锁

如果更新一个字段,然后在筛选的时候where后面的字段没有加索引,就会把整个表的所有记录都锁定.

在where后面的筛选字段加上索引后就不会锁表只会锁行,即使这个筛选范围字段只是组合索引里的其中一个

这里我经过实际操作发现了个现象,当更新的时候如果以主键为where条件,并且主键类型是varcahr的但是你在条件里写成int的了(都是数字,类型不同),第一个用户没有提交事务,会阻塞后面的用户更新另一条记录.
所以这里要注意,更新的时候一定要注意看清字段的类型写sql

 

索引对表锁的影响：

alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

加索引要避免锁表，需要先确定此时没有慢查询事务未提交，如果这个查询卡了30分钟，那么整个表的所有业务都会卡30分钟，这是很变态的。

所以大表加索引还是在没人用的时间加比较安全，要不就是先创建副本，再将表名改掉。

由此得出一个结论，当一张表数据量很大时，不要轻易添加索引，会导致表被锁死！如果非要添加，那么应该先把数据表进行备份，然后进行空表添加索引。

 

查询范围对mysql锁的影响：

set autocommit=0;update fa_admin set nickname='cyy4' where id>1;

这种情况下，执行insert操作，会存在阻塞，因为新增的id也在更新的范围内；

set autocommit=0;update fa_admin set nickname='cyy4' where id<3;

这种情况下，不存在阻塞；

建议每次行锁的范围越小越好，这样对其他数据的阻塞就越小；

 

高并发下商城秒杀悲观锁的应用技巧：

当select XXXX for update就会锁住这一行记录,别人只能查不能更新,即加了写锁；

demo如下：

set autocommit=0;select * from fa_admin where id = 1 for update;update fa_admin set username = 'cyy6' where id = 1;commit;

 

使用乐观锁控制高并发下商品下单：

方式1：使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现 方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 "version" 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

set autocommit=0;select * from fa_admin where id = 1;update fa_admin set nickname = 'cyy7',version = version + 1 where id = 1 and version = 0;commit;

 

方式2：乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳 （timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

 

读锁与写锁的运转：

读锁：只能读，不能写；select成功，insert失败。

加锁:lock table XX read
释放锁:unlock table
加上读锁后大家都可以查询,对于当前加锁的这个用户来说,也只能查询(读),更新插入(写)的时候就会报错
对于其他用户来说可以查询,但是更新(写)操作会被阻塞，直到加锁的用户释放这个读锁

lock table fa_admin read;select * from fa_admin;insert into fa_admin(username) values('cyy8');unlock tables;

写锁：既不能读，也不能写

在当前会话中可以读写,别的会话不能读也不能写,都会被阻塞,直到当前会话释放这个锁

加锁:lock table XXX write;
解锁:unlock table;

在实际操作写锁的时候发现navicat一个查询标签在加了写锁后会把自己也锁住,这操作骚的一批.

 使用表锁应用高并发下单实例： 首先建个测试表结构如下：

 

 随便整点数据

 

 不建议表锁

lock table goods write;update goods set num = num - 200 where id = 1;-- 写单 发送 扣除余额等 --unlock tables;

原文转载：http://www.shaoqun.com/a/488153.html

krazy：https://www.ikjzd.com/w/1801

巴克莱银行：https://www.ikjzd.com/w/2775

芒果店长：https://www.ikjzd.com/w/1533

MySQL常用存储引擎的锁机制MyISAM和MEMORY采用表级锁(table-levellocking)BDB采用页面锁(page-levellocking)或表级锁，默认为页面锁InnoDB支持行级锁(row-levellocking)和表级锁,默认为行级锁innodb和mysam目前大家用的mysql中表的engine都是innodb,很少会用mysam了,就是因为在更新数据的时候mysam
picitup：https://www.ikjzd.com/w/446
cima是什么：https://www.ikjzd.com/w/1372
红海湾畔不为人知的马宫海滩：http://tour.shaoqun.com/a/60309.html
英国正式脱欧，商标专利何去何从?：https://www.ikjzd.com/home/115110
等飞机不再无聊！盘点10大顶级机场温泉水疗：http://tour.shaoqun.com/a/36885.html