深入浅出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即可重复读，在一个事务对数据行执行读取或写入操作时锁定了这些数据行。

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

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

       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'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。