mysql事务及锁机制

mysql服务器逻辑架构

本文是对mysql锁、事务、并发控制的一个总结
在计算机中，锁是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要

1. mysql引擎及其支持的锁

Engines	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

行锁、表锁、页锁 的开销、加锁速度、死锁、粒度及并发性能如下表：

Lock	开销	加锁速度	粒度	死锁	并发性能
行锁	大	慢	小	会出现死锁	高
表锁	小	快	大	不会出现死锁	最低
页锁	介于表锁和行锁之间	介于表锁和行锁之间	介于表锁和行锁之间	会出现死锁	一般

从上述的特点来看，很难笼统的说哪种锁最好，只能根据具体应用的特点来决定选择哪种锁更加合适。仅仅从锁的角度来说的话：

• 表锁更适用于以查询为主，只有少量按索引条件更新数据的应用
• 行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

1.1 MyISAM表锁

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。

• 查询表级锁争用情况。可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979  |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec))
--------------------- 

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL的表级锁有两种模式：

1. 表共享读锁（Table Read Lock）
2. 表独占写锁（Table Write Lock）

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

MyISAM存储引擎的写锁阻塞读例子：

Locks

它们都是标准的行级锁

• 共享锁(Shared Lock)（S）
  共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；
• 排他锁(Exclusive Lock)（X）
  排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。
• Intention Locks（意向锁）
  InnoDB支持多粒度锁(锁粒度可分为行锁和表锁)，允许行锁和表锁共存。例如，一个语句，例如LOCK TABLES…WRITE接受指定表上的独占锁。为了实现多粒度级别的锁定，InnoDB使用了意图锁。