(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

参考

http://blog.csdn.net/xiao7ng/article/details/4916220
http://www.cnblogs.com/huangye-dream/archive/2013/07/06/3174725.html
http://c.biancheng.net/cpp/html/1469.html

推荐阅读更多精彩内容