database:mysql:locktable
差異處
這裏顯示兩個版本的差異處。
| Both sides previous revision 前次修改 下次修改 | 前次修改 | ||
|
database:mysql:locktable [2014/01/11 09:26] ali88 |
database:mysql:locktable [2014/01/11 14:49] (目前版本) ali88 |
||
|---|---|---|---|
| 行 11: | 行 11: | ||
| ^Time ^session1 ^session2 ^ | ^Time ^session1 ^session2 ^ | ||
| - | | |mysql>lock tables QQ read; |lock table TableName | | + | | |mysql>lock tables QQ read; | | |
| - | | |mysql>select * from QQ where id=1; (OK) |select * from QQ where id=2; (OK)| | + | | |mysql>select * from QQ where id=1; (OK) |mysql>select * from QQ where id=2; (OK)| |
| - | | |mysql>delete QQ where id=1; (error) |delete QQ where id=2; (error)| | + | | |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.1389432365.txt.gz · 上一次變更: 2014/01/11 09:26 由 ali88
若無特別註明，本 wiki 上的內容都是採用以下授權方式： CC Attribution-Share Alike 3.0 Unported
