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