Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | About

第 8 章 DCL (Data Control Language)

目录

8.1. 锁
8.1.1. 共享锁
8.1.2. 排他锁
8.1.3. 锁
8.1.3.1. 表的加锁与解锁
8.1.3.2. 禁止查询
8.1.4. 锁等待与超时
8.1.4.1. 超时设置
8.1.4.2. select for update nowait
8.2. 事务处理和锁定语句
8.2.1. 事务隔离级别
8.2.2. 事务所用到的表
8.2.3. 解决更新冲突
8.2.4. SAVEPOINT
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like what rollback segment to use
	

8.1. 锁

锁机制

    1) 共享锁:由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写
    2) 排它锁:由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,典型是mysql事务中的

    锁的范围:
    行锁: 对某行记录加上锁
    表锁: 对整个表加上锁
	

共享锁(share mode), 排他锁(for update)

8.1.1. 共享锁

8.1.2. 排他锁

下面做作一个实验,验证锁的效果

终端一,首先进入事务状态然后运行下面语句

		
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id='3' for update;
+----+--------+---------------------+---------------------+
| id | name   | ctime               | mtime               |
+----+--------+---------------------+---------------------+
|  3 | test   | 0000-00-00 00:00:00 | 2013-01-14 13:05:41 |
+----+--------+---------------------+---------------------+
1 row in set (0.00 sec)
		
		

终端二, 查询表中数据

		
mysql> select * from t1;
+----+--------+---------------------+---------------------+
| id | name   | ctime               | mtime               |
+----+--------+---------------------+---------------------+
|  1 | neo    | 0000-00-00 00:00:00 | 2013-01-14 13:00:00 |
|  2 | zen    | 0000-00-00 00:00:00 | 2013-01-14 13:00:43 |
|  3 | test   | 0000-00-00 00:00:00 | 2013-01-14 13:05:41 |
+----+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
		
		

增加“for update”查询非锁定记录

		
mysql> select * from t1 where id=2 for update;
+----+------+---------------------+---------------------+
| id | name | ctime               | mtime               |
+----+------+---------------------+---------------------+
|  2 | zen  | 0000-00-00 00:00:00 | 2013-01-14 13:00:43 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)
		
		

查询被锁定记录

		
mysql> select * from t1 where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
		
		

查询所有记录,因为记录中包含了id=3那条,所以也不允许查询。

		
mysql> select * from t1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
		
		

测试修改记录

		
mysql> UPDATE `t1` SET `name`='testaa' WHERE  `id`=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
		
		
[提示]提示

在没有出现ERROR 1205 (HY000)的这段时间,只要终端一中执行commit,rollback锁就释放了.终端二中的语句就会运行。

select trx_query from information_schema.innodb_trx; 可以查看被锁的SQL语句

8.1.3. 锁

8.1.3.1. 表的加锁与解锁

LOCK TABLES tablename WRITE;
LOCK TABLES tablename READ;

...
...

UNLOCK TABLES;
			
			
CREATE TABLE `locking` (
	`name` VARCHAR(50) NULL DEFAULT NULL
)
ENGINE=InnoDB
;

mysql> insert into locking values('test');
Query OK, 1 row affected (0.02 sec)

mysql> select * from locking;
+------+
| name |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> UNLOCK TABLES;
			
			
			

mysql> LOCK TABLES locking READ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into locking values('test');
ERROR 1099 (HY000): Table 'locking' was locked with a READ lock and can't be updated


mysql> LOCK TABLE locking WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locking;
+------+
| name |
+------+
| test |
| test |
+------+
2 rows in set (0.00 sec)

mysql> insert into locking values('test');
Query OK, 1 row affected (0.05 sec)

mysql> UNLOCK TABLES;
			
			

8.1.3.2. 禁止查询

			

mysql> LOCK TABLE locking AS myalias READ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locking;
ERROR 1100 (HY000): Table 'locking' was not locked with LOCK TABLES

mysql> select * from locking as myalias;
+------+
| name |
+------+
| test |
| test |
| test |
+------+
3 rows in set (0.00 sec)

			
			

8.1.4. 锁等待与超时

当你开启了事务 begin 忘记,或者各种原因没有commit也没有rollback。悲剧了!

8.1.4.1. 超时设置

begin;
SET SESSION wait_timeout = 60;	
select * from locking for update;
			

60秒内如果没有commit/rollback将自动释放本次事务。

8.1.4.2. select for update nowait

使用 for update 是会遇到一个问题,就是其他用户会漫长的等待,而我们需要程序非阻塞运行,当遇到 for update 的时候应该立即返回此表已被加锁。

mysql 并没有实现 nowait 关键字(类似Oracle的功能),但又一个方法能够达到同样目的。

			
mysql> select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 5.6.24           |
+------------------+
1 row in set (0.05 sec)
			
mysql> select * from locking;
ERROR 1100 (HY000): Table 'locking' was not locked with LOCK TABLES
			
			

此时需要等待很长时间才能提示 “Table 'locking' was not locked with LOCK TABLES”

			
mysql> set session innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from locking for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
			
			

设置 innodb_lock_wait_timeout 参数后,很快就返回

			
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)	
			
			

innodb_lock_wait_timeout 默认值是 50