mysql事务和锁

概览：比如说，在人员管理系统中，要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等。

事务条件：必须满足4个条件（ACID），原子性，一致性，隔离性，持久性。

事务隔离级别：读未提交(级别最低)，读已提交(避免脏读)，可重复读(避免脏读、不可重复读)，串行化(避免脏读、不可重复读、幻读，效率也最低)。

事务控制语句：BEGIN 开始一个事务，ROLLBACK 事务回滚，COMMIT 事务确认，SET AUTOCOMMIT=0 禁止自动提交，SET AUTOCOMMIT=1 开启自动提交。

备注：在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务并发并不进行事务隔离造成的脏读、幻读、不可重复读。mysql支持上面4种隔离级别，默认为可重复读。

MySQL有三种锁的级别：

• 表级锁：多线程共享读锁，默认加排他表锁(另一个线程想要写数据的话，就必须要先取得排他访问)，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

　　　　　　表级锁有两种模式：表共享读锁（Lock table 表名 read，只能执行select操作），表独占写锁（Lock table 表名 write，无法执行select操作）。

　　　　　　如何加表锁：查询更新语句会自动给涉及的所有表加读写锁，过程并不需要用户干预，上面那个是手动操作的。

　　　　　　MyISAM表锁优化建议：

　　　　　　1.查询表级锁争用情况：show status like 'Table%';可以查看表锁定争夺次数，由于锁定级别是不可能改变的，所以尽可能让锁定的时间变短，提高并发操作。如果Table_locks_waited与Table_locks_immediate的比值较大，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。

　　　　　　

　　　　　　2.缩短锁定时间：尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；尽可能的建立足够高效的索引，让数据检索更迅速；尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型。

　　　　　　3.分离能并行的操作：MyISAM引擎读写是互相阻塞的，但MyISAM不是完全的串行化，ConcurrentInsert（并发插入）可以控制其并发插入的行为。

　　　　　　　　a、concurrent_insert为0，不允许并发插入。

　　　　　　　　b、concurrent_insert为1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

　　　　　　　　c、concurrent_insert为2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

 　　　　　　4.合理利用读写优先级：读写互相阻塞，SQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。如果系统是一个以读为主，可以设置此参数，通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。如果以写为主，则不用设置。

　　　　　　5.长时间运行的查询操作，也会使写进程“饿死”，可以通过使用中间表或者夜间执行预统计等措施，减少锁冲突。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

　　　　行级锁有4种模式：共享锁、意向共享锁、排他锁、意向排它锁。

　　　　如何加行锁：意向锁是InnoDB自动加的；UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

　　　　

 　　　　InnoDB行锁优化建议(性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的)：

　　　　　　1.InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

　　　　　　2.即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

　　　　　　3.尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。

　　　　　　4.尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

　　　　死锁解决方案：　

1. 1. 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

   2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

   3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

• 页级锁：开销和加锁时间界于表锁和行锁之间。

 

参考博客：https://blog.csdn.net/yinjinshui/article/details/101676489，https://blog.csdn.net/yinjinshui/article/details/101689128