深入浅出mysql事务处理和锁机制

1.       事务处理和并发性

1.1.        基础知识和相关概念

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

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

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

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

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

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

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

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

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

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

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

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

 

影响到数据的读取,默认的级别是 RR模式.
transaction_isolation   隔离级别(参数)
负责的是,MVCC,读一致性问题
RU  : 读未提交,可脏读,一般部议叙出现
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select *

 

 

 

       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 语句时捕获页锁的，而不是在事务的开始就这么做。

1.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 操作。

 

1.3.        如何避免锁的资源竞争

1 ）让 SELECT 速度尽量快，这可能需要创建一些摘要表。

2 ）启动 mysqld 时使用参数 --low-priority-updates 。这就会让更新操作的优先级低于 SELECT 。

这种情况下，在上面的假设中，第二个 SELECT 就会在 INSERT 之前执行了，而且也无需等待第一个 SELECT 了。

3 ）可以执行 SET LOW_PRIORITY_UPDATES=1 命令，指定所有的更新操作都放到一个指定的链接中去完成。

4 ）用 LOW_PRIORITY 属性来降低 INSERT ， UPDATE ， DELETE 的优先级。

5 ）用 HIGH_PRIORITY 来提高 SELECT 语句的优先级。

6 ）从 MySQL 3.23.7 开始，可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 SELECT 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。

7 ）当 INSERT 和 SELECT 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的 SELECT 和 INSERT 操作。

8 ）当在同一个表上同时有插入和删除操作时， INSERT DELAYED 可能会很有用。

9 ）当 SELECT 和 DELETE 一起使用出现问题时， DELETE 的 LIMIT 参数可能会很有用。

10 ）执行 SELECT 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间。

11 ）可以修改源代码 `mysys/thr_lock.c' ，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。

 

1、什么是事务
事务是一条或多条数据库操作语句的组合，具备ACID，4个特点。

原子性：要不全部成功，要不全部撤销

隔离性：事务之间相互独立，互不干扰

一致性：数据库正确地改变状态后，数据库的一致性约束没有被破坏

持久性：事务的提交结果，将持久保存在数据库中

2、事务并发会产生什么问题

1）第一类丢失更新：在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出， 导致对数据的两个修改都失效了。

例如：

张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100，

随后

事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。

2）脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：
　　张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
　　与此同时，
　　事务B正在读取张三的工资，读取到张三的工资为8000。
　　随后，
　　事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
　　最后，
　　事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

3）不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
　　在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B把张三的工资改为8000，并提交了事务。
　　随后，
　　在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

4）第二类丢失更新：不可重复读的特例。有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。

例如：

在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B，存储1000，把张三的存款改为6000，并提交了事务。
　　随后，
　　在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新。

5）幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
　　目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
　　此时，
　　事务B插入一条工资也为5000的记录。
　　这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

提醒：
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

3、事务隔离级别，解决什么并发问题，以及存在什么并发问题

（1）READ_UNCOMMITTED
　　这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。
　　解决第一类丢失更新的问题，但是会出现脏读、不可重复读、第二类丢失更新的问题，幻读 。
（2）READ_COMMITTED
　　保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。
　　解决第一类丢失更新和脏读的问题，但会出现不可重复读、第二类丢失更新的问题，幻读问题
（3）REPEATABLE_READ
　　保证一个事务相同条件下前后两次获取的数据是一致的

解决第一类丢失更新，脏读、不可重复读、第二类丢失更新的问题，但会出幻读。
（4）SERIALIZABLE
　　事务被处理为顺序执行。
　　解决所有问题

提醒：

Mysql默认的事务隔离级别为repeatable_read

4、InnoDB引擎的锁机制

（之所以以InnoDB为主介绍锁，是因为InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁）

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明：

1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。

2）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

3）InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！。

 

 

 

转载自：http://blog.csdn.net/forever_feng/article/details/4368003