MySql学习笔记——锁与事务机制

为什么需要锁？

因为数据库要解决并发控制问题。在同一时刻，可能会有多个客户端对Table1.rown进行操作，比如有的在读取该行数据，其他的尝试去删除它。为了保证数据的一致性，数据库就要对这种并发操作进行控制，因此就有了锁的概念。

锁的分类

从对数据操作的类型（读\写）分

• 读锁（共享锁）：针对同一块数据，多个读操作可以同时进行而不会互相影响。

• 写锁（排他锁）：当当前写操作没有完成前，它会阻断其他写锁和读锁。

从锁定的数据范围分

• 表锁

• 行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念

锁粒度（Lock granularity）

• 表锁：

管理锁的开销最小，同时允许的并发量也最小的锁机制。MyIsam存储引擎使用的锁机制。当要写入数据时，把整个表都锁上，此时其他读、写动作一律等待。在MySql中，除了MyIsam存储引擎使用这种锁策略外，MySql本身也使用表锁来执行某些特定动作，比如alter table.

• 行锁：

可以支持最大并发的锁策略。InnoDB和Falcon两张存储引擎都采用这种策略。

MySql是一种开放的架构，你可以实现自己的存储引擎，并实现自己的锁粒度策略，不像Oracle，你没有机会改变锁策略，Oracle采用的是行锁。

事务（Transaction）

从业务角度出发，对数据库的一组操作要求保持4个特征：

• Atomicity：原子性

• Consistency：一致性，

• Isolation：隔离性

• Durability：持久性

为了更好地理解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;

原子性：要么完全提交（10233276的checking余额减少200，savings 的余额增加200），要么完全回滚（两个表的余额都不发生变化）

一致性：这个例子的一致性体现在 200元不会因为数据库系统运行到第3行之后，第4行之前时崩溃而不翼而飞，因为事物还没有提交。

隔离性：允许在一个事务中的操作语句会与其他事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询checking余额时，它仍然能够看到在事务A中被减去的200元，因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变。

持久性：这个很好理解。

事务跟锁一样都会需要大量工作，因此你可以根据你自己的需要来决定是否需要事务支持，从而选择不同的存储引擎。

隔离级别（Isolation Level）

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

• Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

• Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

     这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

     脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

     不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

     幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

总结

• 1 ）全部的表类型都可以使用锁，但是只有 InnoDB 和 BDB 才有内置的事务功能。

• 2 ）使用 begin 开始事务，使用 commit 结束事务，中间可以使用 rollback 回滚事务。

• 3 ）在默认情况下， InnoDB 表支持一致读。

SQL 标准中定义了 4 个隔离级别： read uncommited ， read commited ， repeatable read ， serializable 。

read uncommited 即脏读，一个事务修改了一行，另一个事务也可以读到该行。

如果第一个事务执行了回滚，那么第二个事务读取的就是从来没有正式出现过的值。 ?

read commited 即一致读，试图通过只读取提交的值的方式来解决脏读的问题，

但是这又引起了不可重复读取的问题。

一个事务执行一个查询，读取了大量的数据行。在它结束读取之前，另一个事务可能完成了对数据行的更改。当第一个事务试图再次执行同一个查询，服务器就会返回不同的结果。

repeatable read 即可重复读，在一个事务对数据行执行读取或写入操作时锁定了这些数据行。

但是这种方式又引发了幻想读的问题。

因为只能锁定读取或写入的行，不能阻止另一个事务插入数据，后期执行同样的查询会产生更多的结果。

serializable 模式中，事务被强制为依次执行。这是 SQL 标准建议的默认行为。

• 4 ）如果多个事务更新了同一行，就可以通过回滚其中一个事务来解除死锁。

• 5 ） MySQL 允许利用 set transaction 来设置隔离级别。

• 6 ）事务只用于 insert 和 update 语句来更新数据表，不能用于对表结构的更改。执行一条更改表结构或 begin 则会立即提交当前的事务。

• 7 ）所有表类型都支持表级锁，但是 MyISAM 只支持表级锁。

• 8 ）有两种类型的表级锁：读锁和写锁。

读锁是共享锁，支持并发读，写操作被锁。

写锁是独占锁，上锁期间其他线程不能读表或写表。

• 8 ）如果要支持并发读写，建议采用 InnoDB 表，因为它是采用行级锁，可以获得更多的更新性能。

• 9 ）很多时候，可以通过经验来评估什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。当前 MySQL 已经支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁了， BDB 表支持页级锁， InnoDB 表支持行级锁。

• 10 ） MySQL 的表级锁都是写锁优先，而且是采用排队机制，这样不会出现死锁的情况。对于 InnoDB 和 BDB 存储引擎来说，是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁， BDB 会在执行 SQL 语句时捕获页锁的，而不是在事务的开始就这么做。

2. 不同锁的优缺点及选择

行级锁的优点及选择 ：

• 1 ）在很多线程请求不同记录时减少冲突锁。
• 2 ）事务回滚时减少改变数据。
• 3 ）使长时间对单独的一行记录加锁成为可能。

行级锁的缺点 ：

• 1 ）比页级锁和表级锁消耗更多的内存。
• 2 ）当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。
• 3 ）当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。
• 4 ）使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。
• 5 ）可以用应用程序级锁来代替行级锁，例如 MySQL 中的 GET_LOCK() 和 RELEASE_LOCK() 。但它们是劝告锁（原文： These are advisory locks ），因此只能用于安全可信的应用程序中。
• 6 ）对于 InnoDB 和 BDB 表， MySQL 只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES ，因为 InnoDB 自动采用行级锁， BDB 用页级锁来保证事务的隔离。

表锁的优点及选择：

• 1 ）很多操作都是读表。
• 2 ）在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value；
DELETE FROM tbl_name WHERE unique_key_col=key_value;

• 3 ） SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。
• 4 ）很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

表锁的缺点：

• 1 ）一个客户端提交了一个需要长时间运行的 SELECT 操作。
• 2 ）其他客户端对同一个表提交了 UPDATE 操作，这个客户端就要等到 SELECT 完成了才能开始执行。
• 3 ）其他客户端也对同一个表提交了 SELECT 请求。由于 UPDATE 的优先级高于 SELECT ，所以 SELECT 就会先等到 UPDATE 完成了之后才开始执行，它也在等待第一个 SELECT 操作。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
　　
　　其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
　　
　　

　　LOCK TABLE inventory WRITE
　　SELECT Quantity FROM inventory
　　WHERE Item='book';
　　...
　　UPDATE inventory SET Quantity=11
　　WHERE Item='book';
　　UNLOCK  TABLES

　　
　　
　 　这里，我们用一个SELECT语句取出初始数据，通过一些计算，用UPDATE语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句 可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。