database:mysql:transaction
差異處
這裏顯示兩個版本的差異處。
| Both sides previous revision 前次修改 下次修改 | 前次修改 | ||
|
database:mysql:transaction [2014/09/09 15:38] ali88 [MySQL交易的隔離層級] |
database:mysql:transaction [2014/09/09 16:53] (目前版本) ali88 |
||
|---|---|---|---|
| 行 1: | 行 1: | ||
| + | ======MySQL交易 未完成====== | ||
| ======MySQL交易的隔離層級====== | ======MySQL交易的隔離層級====== | ||
| ===MySQL提供四種隔離層級=== | ===MySQL提供四種隔離層級=== | ||
| 行 7: | 行 8: | ||
| ===MySQL隔離層級Demo=== | ===MySQL隔離層級Demo=== | ||
| 在做以下解說之前,先在MySQL的test資料庫建立一個名為QQ的資料表並插入一些資料。(目前在 MySQL 5.1.71 下作業) | 在做以下解說之前,先在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> | + | <code>mysql> create table QQ(id int ,data char(10))engine=innodb;</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> |
| ===Repeatable-Read=== | ===Repeatable-Read=== | ||
| ^Time ^session1 ^session2 ^ | ^Time ^session1 ^session2 ^ | ||
| | |mysql>show variables like '%iso%'; |mysql>show variables like '%iso%'; | | | |mysql>show variables like '%iso%'; |mysql>show variables like '%iso%'; | | ||
| | |mysql>select * from QQ; |mysql>select * from QQ; | | | |mysql>select * from QQ; |mysql>select * from QQ; | | ||
| - | | |mysql>select * from QQ where id=1; (OK) |mysql>select * from QQ where id=2; (OK)| | + | | |mysql>start transaction; |mysql>start transaction; | |
| - | | |mysql>delete from QQ where id=1; (ERROR 1099) | | | + | | | |mysql>update QQ set data='GGGG' where id=7; | |
| - | | | |mysql>delete from QQ where id=2; (在read Queue中 等待)| | + | | | |mysql>select * from QQ;| |
| - | | |mysql>unlock tables ;|mysql>Query OK, 1 row affected (剛在等待的delete from QQ where id=2被執行);| | + | | | |mysql>commit; | |
| + | | |mysql>select * from QQ; | | | ||
| + | | |mysql>commit; | | | ||
| + | | |mysql>select * from QQ; | | | ||
| + | |||
| + | ===Read-Committed=== | ||
| + | |||
| + | ^Time ^session1 ^session2 ^ | ||
| + | | |mysql>set tx_isolation='read-committed'; || | ||
| + | | |mysql>show variables like '%iso%'; |mysql>show variables like '%iso%';| | ||
| + | | |mysql>select * from QQ; |mysql>select * from QQ; | | ||
| + | | |mysql>start transaction; |mysql>start transaction; | | ||
| + | | | |mysql>update QQ set data='IIIII' where id=9; | | ||
| + | | |mysql>select * from QQ; |mysql>select * from QQ;| | ||
| + | | | |mysql>commit; | | ||
| + | | |mysql>select * from QQ; | | | ||
| ======參考資料====== | ======參考資料====== | ||
| * [[http://xyz.cinc.biz/2013/05/mysql-transaction.html|MySQL 交易功能 Transaction 整理 ]] | * [[http://xyz.cinc.biz/2013/05/mysql-transaction.html|MySQL 交易功能 Transaction 整理 ]] | ||
database/mysql/transaction.1410277134.txt.gz · 上一次變更: 2014/09/09 15:38 由 ali88
若無特別註明，本 wiki 上的內容都是採用以下授權方式： CC Attribution-Share Alike 3.0 Unported
