引題：為何引入事務(wù)？
1>.數(shù)據(jù)完整性
2>.數(shù)據(jù)安全性
3>.充分利用系統(tǒng)資源，提高系統(tǒng)并發(fā)處理的能力
1. 事務(wù)的特征
事務(wù)具有四個(gè)特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個(gè)特性簡(jiǎn)稱ACID特性。
1.1原子性
事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中包括的所有操作要么都做，要么都不做。
1.2 一致性
事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個(gè)一致性的狀態(tài)變到另外一個(gè)一致性狀態(tài)。
1.3 隔離性
一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)其他
事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間互相不干擾。
1.4 持久性
一個(gè)事務(wù)一旦成功提交，對(duì)數(shù)據(jù)庫中數(shù)據(jù)的修改就是持久性的。接下來其他的其他
操作或故障不應(yīng)該對(duì)其執(zhí)行結(jié)果有任何影響。
2.1 SELECT …… LOCK IN SHARE MODE
會(huì)話事務(wù)中查找的數(shù)據(jù)，加上一個(gè)共享鎖。若會(huì)話事務(wù)中查找的數(shù)據(jù)已經(jīng)被其他會(huì)話事務(wù)加上獨(dú)占鎖的話，共享鎖會(huì)等待其結(jié)束再加，若等待時(shí)間過長(zhǎng)就會(huì)顯示事務(wù)需要的鎖等待超時(shí)。
2.2 SELECT ….. FOR UPDATE
會(huì)話事務(wù)中查找的數(shù)據(jù)，加上一個(gè)讀更新瑣，其他會(huì)話事務(wù)將無法再加其他鎖，必須等待其結(jié)束。
2.3 INSERT、UPDATE、DELETE
會(huì)話事務(wù)會(huì)對(duì)DML語句操作的數(shù)據(jù)加上一個(gè)獨(dú)占鎖，其他會(huì)話的事務(wù)都將會(huì)等待其釋放獨(dú)占鎖。
2.4 gap and next key lock（間隙鎖）
InnoDB引擎會(huì)自動(dòng)給會(huì)話事務(wù)中的共享鎖、更新瑣以及獨(dú)占鎖，需要加到一個(gè)區(qū)間值域的時(shí)候，再加上個(gè)間隙鎖（或稱范圍鎖），對(duì)不存在的數(shù)據(jù)也鎖住，防止出現(xiàn)幻寫。
備注：
以上2.1，2.2，2.3，2.4中描述的情況，跟MySQL所設(shè)置的事務(wù)隔離級(jí)別也有關(guān)系。
3.四種事務(wù)隔離模式
3.1 READ UNCOMMITED
SELECT的時(shí)候允許臟讀，即SELECT會(huì)讀取其他事務(wù)修改而還沒有提交的數(shù)據(jù)。
3.2 READ COMMITED
SELECT的時(shí)候無法重復(fù)讀，即同一個(gè)事務(wù)中兩次執(zhí)行同樣的查詢語句，若在第一次與第二次查詢之間時(shí)間段，其他事務(wù)又剛好修改了其查詢的數(shù)據(jù)且提交了，則兩次讀到的數(shù)據(jù)不一致。
3.3 REPEATABLE READ
SELECT的時(shí)候可以重復(fù)讀，即同一個(gè)事務(wù)中兩次執(zhí)行同樣的查詢語句，得到的數(shù)據(jù)始終都是一致的。
3.4 SERIALIZABLE
與可重復(fù)讀的唯一區(qū)別是，默認(rèn)把普通的SELECT語句改成SELECT …. LOCK IN SHARE MODE。即為查詢語句涉及到的數(shù)據(jù)加上共享瑣，阻塞其他事務(wù)修改真實(shí)數(shù)據(jù)。
4. 驗(yàn)證事務(wù)與鎖定示例
接下來，我們將以MySQL中的InnoDB引擎，解釋其如何實(shí)現(xiàn)ACID特性，不同隔離級(jí)別下事務(wù)與事務(wù)之間的影響。示例表結(jié)構(gòu)：
CREATE TABLE `account ` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`VACCOUNT_ID` varchar(32) NOT NULL,
`GMT_CREATE` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID ` (`VACCOUNT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
然后向表account中寫入10W條創(chuàng)建日期分布合理的帳號(hào)數(shù)據(jù)，以方便測(cè)試之用。
|
tx_isolation：SET GLOBAL tx_isolation='read-uncommitted' |
||||
|
ID |
事務(wù)1 |
事務(wù)1輸出 |
事務(wù)2 |
事務(wù)2輸出 |
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
|
|
|
START TRANSACTION; |
|
|
3 |
|
|
UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; |
|
|
4 |
|
|
SELECT VACCOUNT_ID from account where ID =1001; |
uncommitted |
|
5 |
SELECT VACCOUNT_ID from account where ID =1001; |
uncommitted |
|
|
|
6 |
|
|
ROLLBACK; |
|
|
7 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
8 |
COMMIT; |
|
|
|
|
tx_isolation：SET GLOBAL tx_isolation='read-committed' |
||||
|
ID |
事務(wù)1 |
事務(wù)1輸出 |
事務(wù)2 |
事務(wù)2輸出 |
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
3 |
|
|
START TRANSACTION; |
|
|
4 |
|
|
UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; |
|
|
5 |
|
|
SELECT VACCOUNT_ID from account where ID =1001; |
uncommitted |
|
6 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
7 |
|
|
COMMIT; |
|
|
8 |
SELECT VACCOUNT_ID from account where ID =1001; |
uncommitted |
|
|
|
9 |
COMMIT; |
|
|
|
|
tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ' |
||||
|
ID |
事務(wù)1 |
事務(wù)1輸出 |
事務(wù)2 |
事務(wù)2輸出 |
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
3 |
|
|
START TRANSACTION; |
|
|
4 |
|
|
UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; |
|
|
5 |
|
|
SELECT VACCOUNT_ID from account where ID =1001; |
uncommitted |
|
6 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
7 |
|
|
COMMIT; |
|
|
8 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
9 |
COMMIT; |
|
|
|
|
tx_isolation：SET GLOBAL tx_isolation='SERIALIZABLE' |
||||
|
ID |
事務(wù)1 |
事務(wù)1輸出 |
事務(wù)2 |
事務(wù)2輸出 |
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
3 |
|
|
START TRANSACTION; |
|
|
4 |
|
|
UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; |
STATE: Updating |
|
5 |
SELECT VACCOUNT_ID from account where ID =1001; |
caimao101510 |
|
|
|
|
|
|
事務(wù)2超時(shí) |
|
|
6 |
COMMIT; |
|
|
|
|
7 |
START TRANSACTION; |
|
|
|
|
8 |
UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; |
|
|
|
|
9 |
|
|
START TRANSACTION; |
|
|
10 |
|
|
SELECT VACCOUNT_ID from account where ID =1001; |
STATE：statistics |
|
11 |
|
|
事務(wù)2超時(shí) |
|
|
12 |
commit; |
|
|
|
|
tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ' |
||||
|
ID |
事務(wù)1 |
事務(wù)1輸出 |
事務(wù)2 |
事務(wù)2輸出 |
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
select max(ID) FROM account; |
124999 |
|
|
|
3 |
|
|
START TRANSACTION; |
|
|
4 |
UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID >=124999; |
|
|
|
|
5 |
|
|
insert into account(VACCOUNT_ID,gmt_create) values(‘eugene',now()); |
STATE:update |
|
6 |
|
|
事務(wù)2超時(shí) |
|
|
7 |
|
|
START TRANSACTION; |
|
|
8 |
|
|
SELECT * FROM account WHERE ID =124998; |
2007-10-20 13:47 |
|
9 |
|
|
UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID =124998; |
執(zhí)行成功 |
|
10 |
|
|
SELECT * FROM account WHERE ID =124998; |
2007-10-21 13:47 |
|
11 |
COMMIT; |
|
|
|
|
12 |
|
|
COMMIT; |
|
|
|
|
|
|
|
|
1 |
START TRANSACTION; |
|
|
|
|
2 |
UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′; |
|
|
|
|
3 |
|
|
START TRANSACTION; |
|
|
4 |
|
|
SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; |
2009-10-2 13:47 |
|
5 |
SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; |
2009-10-1 13:47 |
|
STATE:update |
|
6 |
|
|
insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_test',now()); |
|
|
7 |
|
|
事務(wù)2超時(shí) |
|
|
8 |
COMMIT; |
|
|
|
|
9 |
|
|
SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; |
2009-10-1 13:47 |
|
無索引條件更新事務(wù) |
||||
|
1 |
START TRANSACTION; |
|
|
|
|
|
UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′ AND gmt_create <'2009-07-10′; |
|
|
|
|
|
|
|
START TRANSACTION; |
|
|
|
|
|
insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_interval',now()); |
|
|
|
|
|
事務(wù)2超時(shí) |
|
|
|
COMMIT; |
