database:mysql:locktable
差異處
這裏顯示兩個版本的差異處。
| Both sides previous revision 前次修改 下次修改 | 前次修改 | ||
|
database:mysql:locktable [2014/01/11 09:04] ali88 [Lock Tables 特性與指令] |
database:mysql:locktable [2014/01/11 14:49] (目前版本) ali88 |
||
|---|---|---|---|
| 行 7: | 行 7: | ||
| 在做以下解說之前,先在MySQL的test資料庫建立一個名為QQ的資料表並插入一些資料。(目前在 MySQL 5.1.71 下作業)<code>mysql> create table QQ(id int ,data char(10));</code><code>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');</code> | 在做以下解說之前,先在MySQL的test資料庫建立一個名為QQ的資料表並插入一些資料。(目前在 MySQL 5.1.71 下作業)<code>mysql> create table QQ(id int ,data char(10));</code><code>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');</code> | ||
| ==== Lock tables QQ read ==== | ==== 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 優先權高 | ||
| + | |||
| + | ^Time ^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 優先權提高 | ||
| + | |||
| + | ^Time ^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; | | ||
| + | |||
| + | |||
| + | |||
| ======參考資料====== | ======參考資料====== | ||
| * [[http://pcclass.cc.nthu.edu.tw/nuke/dbms.php?filename=class2-2.html|鎖定資料表 (Lock)]] | * [[http://pcclass.cc.nthu.edu.tw/nuke/dbms.php?filename=class2-2.html|鎖定資料表 (Lock)]] | ||
| * [[http://xyz.cinc.biz/2013/04/mysql-lock-tables.html|MySQL 鎖定資料表 (LOCK TABLES) ]] | * [[http://xyz.cinc.biz/2013/04/mysql-lock-tables.html|MySQL 鎖定資料表 (LOCK TABLES) ]] | ||
database/mysql/locktable.1389431086.txt.gz · 上一次變更: 2014/01/11 09:04 由 ali88
若無特別註明，本 wiki 上的內容都是採用以下授權方式： CC Attribution-Share Alike 3.0 Unported
