mysql 事务 表 锁定_(016)mysql中的表锁定及事务控制

概述

MySQL支持三种锁级别：页级、表级、行级。MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)；BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。在默认情况下，表锁和行锁都是自动获取的，不需要额外的命令。三种锁级别优缺点如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

表锁定

1.语法

#锁定表

LOCK TABLES

tb_name1 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}

tb_name2 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}

...

#释放表锁定

UNLOCK TABLES;

lock tables 可以锁定用于当前线程(会话)的表。如果被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。

unlock tables释放当前线程(会话)获得的任何锁定。

read(读锁/共享锁)：当表不存在 WRITE 写锁时 READ 读锁被执行,这该状态下,当前线程不可以修改(insert,update,delete),其他线程的修改操作进入列队,当当前线程释放锁,其他线程修改被执行。

read local：read local和read之间的区别是，read local允许在锁定被保持时，执行非冲突性INSERT语句(同时插入)。但是，如果您正打算在MySQL外面操作数据库文件，同时您保持锁定，则不能使用read local。对于InnoDB表，read local与read相同。

write(写锁/排它锁)：除了当前用户被允许读取和修改被锁表外，其他用户的所有访问(读/写)被完全阻止。注意的是在当前线程当WRITE被执行的时候,即使之前加了READ没被取消,也会被取消。

low_priority write：降低优先级的write,默认write的优先级高于read.假如当前线程的low_priority write在列队里面,在未执行之前其他线程传送一条read,那么low_priority write继续等待.

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

并发插入：原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行)，MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

2.举例

这是一个获取表锁及释放表锁的例子。其中session1和session2表示两个同时打开的session(连接/线程)，表格中的每一行表示同一时刻两个session的运行情况，后面的例子也是同样的格式。

表锁举例

事务控制

MySQL通过set autocommit、start transaction、commit、rollback等语句支持本地事务。

1.语法

START TRANSACTION |BEGIN [WORK]

COMMIT [WORK][AND [NO] CHAIN] [[NO] RELEASE]

ROLLBACK [WORK][AND [NO] CHAIN] [[NO] RELEASE]

SET AUTOCOMMIT={0|1}

默认情况下，MySQL是自动提交的，如果需要通过明确的Commit和Rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务。

start transaction 或begin语句开始一项新的事务。

commit和rollback用来提交或者回滚事务。

chain和release子句分别用来定义在事务提交或者回滚之后的操作，chain会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接。

set autocommit可以修改当前连接的提交方式，如果设置了set autocommit=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

2.举例

举例一

使用start transaction和commit and chain。

事务控制1-1

事务控制1-2

事务控制1-3

举例二

在锁表期间，用start transaction命令开启新事务，会造成隐含的unlock tables被执行。

事务控制2-1

事务控制2-2

举例三

在事务中可以通过定义savepoint，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的savepoint，满足不同的条件时，回滚不同的savepoint。需要注意的是，如果定义了相同名字的savepoint，则后面定义的savepoint会覆盖之前的定义。对于不再需要使用的savepoint，可以通过release savepoint命令删除savepoint，删除后的savepoint，不能再执行rollback to savepoint命令。

事务控制3-1

事务控制3-2

参考