| 知乎專欄 ｜ 多維度架構 |
目錄
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
鎖機制
1) 共享鎖：由讀表操作加上的鎖，加鎖後其他用戶只能獲取該表或行的共享鎖，不能獲取排它鎖，也就是說只能讀不能寫
2) 排它鎖：由寫表操作加上的鎖，加鎖後其他用戶不能獲取該表或行的任何鎖，典型是mysql事務中的
鎖的範圍:
行鎖: 對某行記錄加上鎖
表鎖: 對整個表加上鎖
共享鎖(share mode), 排他鎖(for update)
下面做作一個實驗，驗證鎖的效果
終端一,首先進入事務狀態然後運行下面語句
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
![[提示]](/graphics/tip.png) | 提示 |
|---|---|
|
在沒有出現ERROR 1205 (HY000)的這段時間，只要終端一中執行commit,rollback鎖就釋放了.終端二中的語句就會運行。 select trx_query from information_schema.innodb_trx; 可以查看被鎖的SQL語句 |
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;
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)
當你開啟了事務 begin 忘記，或者各種原因沒有commit也沒有rollback。悲劇了！
begin; SET SESSION wait_timeout = 60; select * from locking for update;
60秒內如果沒有commit/rollback將自動釋放本次事務。
使用 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