1、表锁导致的慢查询的问题
扩展知识:
1)、查询 mysql现在已提供什么存储引擎:
show engines;
2)、查询某个表用了什么引擎(参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
3)、切换mysql数据库存储引擎:
ALTER TABLE ADMIN ENGINE = 存储引擎;
问题演示:
1)、创建表:
create table user1(id int,name varchar(10)) ;
insert into user1 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
2)、演示
注:
for update是在数据库中上锁用的,可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候,其他事务可以读取但是不能写入或更新。
分析原因:
本文主要分析MyISAM和InnoDB这两个存储引擎。
MyISAM:默认表类型,它是基于传统的ISAM类型,它是存储记录和文件的标准方法。只支持表级锁,用户在操作MyISAM表时,select,update,delete,insert语句都会给表自动加锁,不是事务安全的,而且不支持外键,如果执行大量的select,insert MyISAM比较适合。
InnoDB:支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。InnoDB支持行级锁和表级锁,默认为行级锁。
**在使用MyISAM存储引擎和不通过索引条件查询的时候的InnoDB存储引擎使用的都是表级锁。**在这种情况下,对MyISAM存储引擎和InnoDB存储引擎执行操作时会产生表锁,将影响其他用户对该表的操作,则会导致其他用户操作串行。在本人执行完操作之前,其他人会一直等待。本人操作结束,其他人会立即执行。
解决方法:
1)、尽量不用MyISAM存储引擎,推荐使用InnoDB存储引擎。
2)、使用MyISAM存储引擎时,减少写操作的时间;
InnoDB行级锁实现
在通过索引条件查询的时候,InnoDB使用行锁。
1)、创建表:
mysql> create table user2(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> alter table user2 add index id(id);
Query OK, 4 rows affected (0.24 sec)
Records: 4 Duplicates: 0 Warnings: 0
2)、演示
正常执行,没有发生等待。
注:
1)、MyISAM 引擎不支持事务,支持事务的引擎只有 InnoDB
2)、InnoDB默认情况下的事务是打开的(set autocommit = 0)就是说每插入一条记录时候,InnoDB类型的表都会把它当作一个单独的事务来处理.所以如果我们插入了10000条记录,而且没有将事务关闭,那么 InnoDB类型的表会把它当作10000个事务来处理。
2、死锁问题分析
死锁是因为两个或多个事务相互等待对方释放锁,导致事务永远无法终止的情况。
注:表锁情况下,不会产生死锁问题。
1)、创建表:
mysql>CREATE TABLE `user3` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
mysql>insert into user3 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
2)、演示
分析原因:
这是一个简单的死锁场景,事务1、事务2彼此等待对方释放锁,InnoDB存储引擎检测到死锁发生,让事务2回滚,这使得事务1不再等待事务B的锁,从而能够继续执行。
那么InnoDB是如何检查出死锁的呢?
我们想到最简单方法是假如一个事务正在等待一个锁,如果等待时间超过了设定的阈值,那么该事务操作失败,这就避免了多个事务彼此长等待的情况。参数innodb_lock_wait_timeout正是用来设置这个锁等待时间的。
如果按照这个方法,解决死锁是需要时间的(即等待超过innodb_lock_wait_timeout设定的阈值),这种方法稍显被动而且影响系统性能,InnoDB存储引擎提供一个更好的算法来解决死锁问题,wait-for graph算法。简单的说,当出现多个事务开始彼此等待时,启用wait-for graph算法,该算法判定为死锁后立即回滚其中一个事务,死锁被解除。该方法的好处是:检查更为主动,等待时间短。
下面是wait-for graph算法的基本原理:
引自:https://www.cnblogs.com/leefreeman/p/8286550.html
总结:
1)、如果我们业务开发中出现死锁如何检查出?刚才已经介绍了通过监控InnoDB状态可以得出,你可以做一个小工具把死锁的记录收集起来,便于事后查看。
2)、如果出现死锁,业务系统应该如何应对?从上文我们可以看到当InnoDB检查出死锁后,对客户端报出一个Deadlock found when trying to get lock; try restarting transaction信息,并且回滚该事务,应用端需要针对该信息,做事务重启的工作,并保存现场日志事后做进一步分析,避免下次死锁的产生。
5、锁等待问题
锁等待是因为一个事务长时间占用锁资源,而其他事务一直等待前个事务释放锁。
1)、演示
从上述可知事务1长时间持有id=3的行锁,事务2产生锁等待,等待时间超过innodb_lock_wait_timeout后操作中断,但事务并没有回滚。如果我们业务开发中遇到锁等待,不仅会影响性能,还会给你的业务流程提出挑战,因为你的业务端需要对锁等待的情况做适应的逻辑处理,是重试操作还是回滚事务。
总结:
1)、请对你的业务系统做锁等待的监控,这有助于你了解当前数据库锁情况,以及为你优化业务程序提供帮助;
2)、业务系统中应该对锁等待超时的情况做合适的逻辑判断。
参考资料:
https://www.cnblogs.com/leefreeman/p/8286550.html
http://book.51cto.com/art/200803/68127.htm