数据库事务，锁

事务

事务特点ACID

1.原子性：一个事务被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行一部分操作。
2.一致性：数据库总是从一个一致性状态转换到另一个一致性状态。
3.隔离性：通常来说，一个事务所做的修改在最终提交之前，对其他食物是不可见的。这里是“通常来说”，后面有事务隔离级别。
4.持久性：一旦事务提交，则其所做的修改就会永久保存在数据库中。此时即使是系统崩溃，修改的数据也不会丢失。（持久性的安全性与刷新日志级别也存在一定关
系，不同级别对应不同的数据安全级别）。

理解ACID

以银行转账为例

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

set autocommit = 0；//关闭自动提交
start transaction； //开启一个事务
DML操作；          //两个及两个以上的DML操作
....
commit；             //手动提交
1.原子性：要么完全提交（10233276的checking余额减少200，savings 的余额增加200），要么完全回滚（两个表的余额都不发生变化）
2.一致性：体现在200元不会因为数据库运行到第三行之后，第四行之前系统崩溃而不翼而飞，因为事务还没有提交。
3.隔离性：事务A运行到第三行之后，第四行之前，此时事务B去查询checking的余额，它仍然能够看到被减去的200元（账户的钱不变），因为AB是彼此隔离的，
在A提交之前，事务B是看不到数据变化的。

注：事务的隔离性是通过锁实现的。
    事务的原子性，一致性和持久性是通过事务日志实现的。

MySQL锁

锁是MySQL在服务器层和存储引擎层的并发控制。

锁机制

1.共享锁（读锁）：其他事务可以读，但是不能写。
2.排他锁（写锁）：其他事物不能读，也不能写。

MySQL不同的存储引擎支持不同的锁机制。所有的存储引擎都以自己的方式显现了锁机制。
1.MyISAM：采用的是表级锁。
2.InnoDB：支持行级锁，也支持表级锁，默认情况下是行级锁。
默认情况下：表锁和行锁都是自动获得的。
但是在一些情况下，用户需要明确的进行锁表，或者事务控制，以确保事务的完整性，这个就需要事务控制和锁定语句来完成。

锁比较：
1.表级锁：开销小，速度快，不会出现死锁，粒度大，锁冲突概率高，并发度低。
    1）这些引擎总是一次性同时获得所需要的锁，总是按相同的顺序获取表锁来避免死锁。
    2）表级锁更适合于查询为主，并发用户少，只有少量更新数据的应用。
2.行级锁：开销大，速度慢，会出现死锁，粒度小，发生锁冲突的概率低，并发度高。
    1）最大程度支持并发，同时带来了最大的锁开销。
    2）在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，所以发生死锁是可能的。
    3）行级锁适合于有大量索引条件并发更新数据，同时又并发查询的应用。
3.页面锁：锁开销在表级锁和行级锁之间，会出现死锁，粒度介于行级锁和表级锁之间，并发度一般。

MyISAM表锁

表级锁模式

1）表共享读锁：不会阻塞其他用户对同一张表的读请求，但会阻塞对同一张表的写请求。（有人在读时不能写）
2）表独占写锁：会阻塞其他用户对同一张表的读和写请求。（有人在写是不能读也不能写）
注：当一个线程获得一个表的写锁时，只有持有锁的线程能够对表进行更新操作，其他线程的读写操作都会等待，直到锁被释放，默认情况下：写锁比读锁有更高的
优先级：当一个锁被释放时，这个锁会优先给请求写锁队列，然后再给请求读锁队列。这也是MyISAM不适合有大量更新操作和查询操作的应用。因为大量的更新操作
会造成查询操作难以获得锁，从而永远阻塞。
    一些需要长时间运行的查询操作，也会使线程饿死，应用中尽量避免出现这种情况，可以使用中间表，可以对SQL语句进行分解，使每个查询都在短时间完成，
从而减少锁冲突。
解决办法：
    1）指定启动参数low-priority-updates，是MyISAM引擎默认给予渡请求优先权。
    2）执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
    3）指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
    4）MySQL系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MyISAM加表锁方法

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁。
注：在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM并发插入

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用。
1）如果表在数据文件中间没有空闲块，则行始终插入在数据文件末尾。这个时候可并发混合使用Insert和select语句而不需要加锁。
    即：在其他线程进行读操作是的时候，你可以将行插入到表中。
注：文件中的空闲块可能是从表中删除或更新产生的。
2）如果文件中有空闲块，并发插入就会被禁用，但是当所有的空闲块都填充新数据时，重新启动。
3）要控制此行为：可以使用MySQL的系统变量-----concurrent_insert
    3.1）设置为0时，不允许并发插入。
    3.2）设置为1时，没有空闲块允许插入，允许一个线程读，一个线程在表尾插入数据。这是MyISAM默认的设置。
    3.3）设置为2时，无论有没有空闲块，都允许在表尾并发插入数据。

InnoDB行级锁和表级锁

InnoDB锁模式

1）共享锁：允许一个事务去读取一行，阻止其他事务获得相同数据的排它锁。（手动加，加上之后其他事务只能查不能更新，自己可以更新）
2）排他锁：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排它锁。（自动加，加上后不允许再加共享锁，其他会话可以查询，不允许其他事务
获得排它锁，即：更新）
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks）。
这两种意向锁都是表锁：
    1）意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。 
    2）意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

InnoDB加锁方法

1）意向锁是 InnoDB 自动加的， 不需用户干预。 
2）对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)；
3）对于普通 SELECT 语句，InnoDB 不会加任何锁；
4）事务可以通过以下语句显式给记录集加共享锁或排他锁：
    4.1）共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。其他会话仍然可以查询录，并也可以对该记录加 share mode的共享锁。
    但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
    4.2）排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他会话可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
注：锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。 

显示加锁：

1）select...for update：
在执行这个select查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

2）select lock in share mode ：
in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。
select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来
修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

性能影响：
1）select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而
影响数据库的并发使用效率。
2）select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果
不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：
前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据
执行lock in share mode。

InnoDB 行锁实现方式：

1）InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点
意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

2）不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

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

4）由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的
（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

InnoDB使用间隙锁的目的

防止幻读：
    幻读：一个事务使用范围索引，满足条件的行以及空隙都会被锁，如id>5的name修改为xxxx， 则id=7被锁，就算id=7没有数据，此时另外一个事务添加一个id=7的数据，则第一个事务查询出来的数据中Id=7的name并不是xxxx，此时就出现了幻读。

死锁

死锁的产生

1）死锁是指两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环。
2）当事务试图以不同顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也会产生死锁。
3）死锁产生与锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会。

检测死锁

数据库系统实现了各种死锁检测和死锁超时机制，InnoDB能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复

死锁发生后，只有部分或完全回滚其中一个事务，才能打破死锁。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能

死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。 

避免死锁

1）提前申请足够级别的锁，要更新就申请排它锁，而不是申请共享锁。
2）多个事务修改多个表，每个事务中以相同顺序加锁。
3）并发存取多个表，尽量约定以相同顺序去访问表。
4）改变事务隔离级别。
注：使用SHOW INNODB STATUS来查看事务获得所得情况，死锁原因等。

锁性能优化

1）尽量使用较低的隔离级别。

2）精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会。

3）选择合理的事务大小，小事务发生锁冲突的几率也更小。

4）给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。

5）不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。

6）尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。

7）不要申请超过实际需要的锁级别。

8）除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

9）对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。