MySQL鎖定 Lock Tables

MySQL本身服務提供鎖定的方法是表鎖Tables Lock,對資料庫資源開銷較小。Tables Lock可以作用於不同儲存引擎(MyISAM,Merge,InnoDB,BDB等),但是其中Innodb儲存引擎有支援Rows Lock及交易等較好機制,Tables Lock比較不常作用於Innodb上,另外,許多文件上指出,既然Innodb儲存引擎已經實現Rows Lock很好機制,就沒必要使用Tables Lock。總結一下:

MySQL支援的表鎖Tables Lock是由服務器提供,而不是儲存引擎。常應用於MyISAM儲存引擎(支援Table Lock,不具有事務交易處理)。
InnoDB儲存引擎提供列鎖(行鎖)Rows Lock,可以用於處理事務交易。

Lock Tables 特性與指令

在做以下解說之前,先在MySQL的test資料庫建立一個名為QQ的資料表並插入一些資料。(目前在 MySQL 5.1.71 下作業)

mysql> create table QQ(id int ,data char(10));

mysql> insert into QQ(id,data)values(1,'AA'),(2,'BB'),(3,'CC'),(4,'DD'),(5,'EE'),(6,'FF'),(7,'GG'),(8,'HH'),(9,'II'),(10,'JJ');

Lock tables QQ read

所有session(connection id)可以讀取QQ資料表,但無法更新或寫入(包括建立此 lock tables的session)。

Time	session1	session2
	mysql>lock tables QQ read;
	mysql>select * from QQ where id=1; (OK)	mysql>select * from QQ where id=2; (OK)
	mysql>delete from QQ where id=1; (ERROR 1099)
		mysql>delete from QQ where id=2; (在read Queue中等待)
	mysql>unlock tables ;	mysql>Query OK, 1 row affected (剛在等待的delete from QQ where id=2被執行);

Lock tables QQ write

建立此 lock tables write 的session可以讀取並且也可寫入;其他session不可寫入,也不可讀取。

Time	session1	session2
	mysql>lock tables QQ write;
	mysql>select * from QQ where id=1; (OK)
	mysql>delete from QQ where id=1; (OK)
		mysql>select * from QQ where id=10;(在write Queue中等待)
	mysql>unlock tables;	mysql>Query OK, 1 row affected (剛在等待的select * from QQ where id=10;被執行);

Lock table read/write優先權

Lock tables write 比 Lock tables read 優先權高

session1	session2	session3
mysql>lock tables QQ write;
	mysql>lock tables QQ read;(在write Queue中等待)
		mysql>lock tables QQ write;(在write Queue中等待)
mysql>unlock tables;		mysql>Query OK, 0 rows affected (剛在等待的 lock tables QQ write;被執行)
	mysql>Query OK, 0 rows affected (剛在等待的 lock tables QQ read;被執行)	mysql>unlock tables;
	mysql>unlock tables;

Lock tables QQ LOW_PRIORITY write(此項指令在 MySQL 5.5.x以上式忽已經失效了)

降低write lock 優先權使 read lock 優先權提高

session1	session2	session3
mysql>lock tables QQ write;
	mysql>lock tables QQ read;(在write Queue中等待)
		mysql>lock tables QQ LOW_PRIORITY write;(在write Queue中等待)
mysql>unlock tables;	mysql>Query OK, 0 rows affected (剛在等待的 lock tables QQ read;被執行)
	mysql>unlock tables;	mysql>Query OK, 0 rows affected (剛在等待的 lock tables QQ LOW_PRIORITY write;被執行)
		mysql>unlock tables;

目錄表