mysql事務(wù)管理及python操作

第mysql事務(wù)管理及python操作在用戶操作MySQL過(guò)程中，對(duì)于一般簡(jiǎn)單的業(yè)務(wù)邏輯或中小型程序而言，無(wú)需考慮應(yīng)用MySQL事務(wù)。但在比較復(fù)雜的情況下，往往用戶在執(zhí)行某些數(shù)據(jù)操作過(guò)程中，需要通過(guò)一組SQL語(yǔ)句執(zhí)行多項(xiàng)并行業(yè)務(wù)邏輯或程序，這樣，就必須保證所用命令執(zhí)行的同步性。使執(zhí)行序列中，產(chǎn)生依靠關(guān)系的動(dòng)作能夠同時(shí)操作成功或同時(shí)返回初始狀態(tài)。在此情況下，就需要用戶優(yōu)先考慮使用MySQL事務(wù)處理。

在MySQL中，事務(wù)由單獨(dú)單元的一個(gè)或多個(gè)SQL語(yǔ)句組成。在這個(gè)單元中，每個(gè)MySQL語(yǔ)句是相互依賴的。而整個(gè)單獨(dú)單元作為一個(gè)不可分割的整體，如果單元中某條SQL語(yǔ)句一旦執(zhí)行失敗或產(chǎn)生錯(cuò)誤，整個(gè)單元將會(huì)回滾。所有受到影響的數(shù)據(jù)將返回到事務(wù)開始以前的狀態(tài)；如果單元中的所有SQL語(yǔ)句均執(zhí)行成功，則事務(wù)被順利執(zhí)行。

通過(guò)InnoDB和BDB類型表，MySQL事務(wù)能夠完全滿足事務(wù)安全的ACID測(cè)試，但是并不是所有表類型都支持事務(wù)，如MyISAM類型表就不能支持事務(wù)，只能通過(guò)偽事務(wù)對(duì)表實(shí)現(xiàn)事務(wù)處理。

MySQL事務(wù)的創(chuàng)建與存在周期

創(chuàng)建事務(wù)

創(chuàng)建事務(wù)的一般過(guò)程是：初始化事務(wù)、創(chuàng)建事務(wù)、應(yīng)用SELECT語(yǔ)句查詢數(shù)據(jù)是否被錄入和提交事務(wù)。如果用戶不在操作數(shù)據(jù)庫(kù)完成后執(zhí)行事務(wù)提交，則系統(tǒng)會(huì)默認(rèn)執(zhí)行回滾操作。如果用戶在提交事務(wù)前選擇撤銷事務(wù)，則用戶在撤銷前的所有事務(wù)將被取消，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)回到初始狀態(tài)。

默認(rèn)情況下，在MySQL中創(chuàng)建的數(shù)據(jù)表類型都是MyISAM，但是該類型的數(shù)據(jù)表并不能支持事務(wù)。所以，如果用戶想讓數(shù)據(jù)表支持事務(wù)處理能力，必須將當(dāng)前操作數(shù)據(jù)表的類型設(shè)置為InnoDB或BDB。

在創(chuàng)建事務(wù)的過(guò)程中，用戶需要?jiǎng)?chuàng)建一個(gè)InnoDB或BDB類型的數(shù)據(jù)表，其基本命令結(jié)構(gòu)如下：

CREATETABLEtable_name（field-defintions）TYPE=INNODB/BDB；

其中，table_name為表名，而field_defintions為表內(nèi)定義的字段等屬性，TYPE為數(shù)據(jù)表的類型，既可以是InnoDB類型，同樣也可以是BDB類型。

當(dāng)用戶希望已經(jīng)存在的表支持事務(wù)處理，則可以應(yīng)用ALTERTABLE命令指定數(shù)據(jù)表的類型實(shí)現(xiàn)對(duì)表的類型更改操作，使原本不支持事務(wù)的數(shù)據(jù)表更改為支持事務(wù)處理的類型。其命令如下：

ALTERTABLEtable_nameTYPE=INNODB/BDB；

當(dāng)用戶更改完表的類型后，即可使數(shù)據(jù)表支持事務(wù)處理。

應(yīng)用ALTERTABLE操作可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)中數(shù)據(jù)丟失，因此為了避免非預(yù)期結(jié)果出現(xiàn)，在使用ALTERTABLE命令之前，用戶需要?jiǎng)?chuàng)建一個(gè)表備份。

初始化事務(wù)

初始化MySQL事務(wù)，首先聲明初始化MySQL事務(wù)后所有的SQL語(yǔ)句為一個(gè)單元。在MySQL中，應(yīng)用STARTTRANSACTION命令來(lái)標(biāo)記一個(gè)事務(wù)的開始。初始化事務(wù)的結(jié)構(gòu)如下：

STARTTRANSACTION；

另外，用戶也可以使用BEGIN或者BEGINWORK命令初始化事務(wù)，通常STARTTRANSACTION命令后面跟隨的是組成事務(wù)的SQL語(yǔ)句。

在命令提示符中輸入如下命令：

starttransaction；

如果在用戶輸入以上代碼后，MySQL數(shù)據(jù)庫(kù)沒有給出警告提示或返回錯(cuò)誤信息，則說(shuō)明事務(wù)初始化成功，用戶可以繼續(xù)執(zhí)行下一步操作。

創(chuàng)建事務(wù)

insertintoconnection（emAIl,cellphone,QQ,sid）

values（barrystephen@126.com187034000，3）；

應(yīng)用SELECT語(yǔ)句查看數(shù)據(jù)是否被正確輸入

SELECT*FROMconnectionWHEREsid=3；

ps:在用戶插入新表為InnoDB類型或更改原來(lái)表類型為InnoDB時(shí)，如果在輸入命令提示后，MySQL提示TheInnoDBfeatureisdisabled；youneedInnoDBtohaveitworking警告，則說(shuō)明InnoDB表類型并沒有被開啟，用戶需要找到MySQL文件目錄下的my.ini文件，定位skip_innodb選項(xiàng)位置，將原來(lái)的skip_innodb改為#skip_innodb后保存該文件，重新啟動(dòng)MySQL服務(wù)器，即可令數(shù)據(jù)庫(kù)支持InnoDB類型表。

提交事務(wù)

在用戶沒有提交事務(wù)之前，當(dāng)其他用戶連接MySQL服務(wù)器時(shí)，應(yīng)用SELECT語(yǔ)句查詢結(jié)果，則不會(huì)顯示沒有提交的事務(wù)。當(dāng)且僅當(dāng)用戶成功提交事務(wù)后，其他用戶才可能通過(guò)SELECT語(yǔ)句查詢事務(wù)結(jié)果，由事務(wù)的特性可知，事務(wù)具有孤立性，當(dāng)事務(wù)處在處理過(guò)程中，其實(shí)MySQL并未將結(jié)果寫入磁盤中，這樣一來(lái)，這些正在處理的事務(wù)相對(duì)其他用戶是不可見的。一旦數(shù)據(jù)被正確插入，用戶可以使用COMMIT命令提交事務(wù)。提交事務(wù)的命令結(jié)構(gòu)如下：

COMMIT

一旦當(dāng)前執(zhí)行事務(wù)的用戶提交當(dāng)前事務(wù)，則其他用戶就可以通過(guò)會(huì)話查詢結(jié)果。

撤銷事務(wù)（事務(wù)回滾）

撤銷事務(wù)，又被稱作事務(wù)回滾。即事務(wù)被用戶開啟、用戶輸入的SQL語(yǔ)句被執(zhí)行后，如果用戶想要撤銷剛才的數(shù)據(jù)庫(kù)操作，可使用ROLLBACK命令撤銷數(shù)據(jù)庫(kù)中的所有變化。ROLLBACK命令結(jié)構(gòu)如下：

ROLLBACK

輸入回滾操作后，如何判斷是否執(zhí)行回滾操作了呢？可以通過(guò)SELECT語(yǔ)句查看11.2.2小節(jié)中插入的數(shù)據(jù)是否存在.

如果執(zhí)行一個(gè)回滾操作，則在輸入STARTTRANSACTIONA命令后的所有SQL語(yǔ)句都將執(zhí)行回滾操作。故在執(zhí)行事務(wù)回滾前，用戶需要慎重選擇執(zhí)行回滾操作。如果用戶開啟事務(wù)后，沒有提交事務(wù)，則事務(wù)默認(rèn)為自動(dòng)回滾狀態(tài)，即不保存用戶之前的任何操作。

事務(wù)的存在周期

事務(wù)的周期由用戶在命令提示符中輸入STARTTRANSACTION指令開始，直至用戶輸入COMMIT結(jié)束.

事務(wù)不支持嵌套功能，當(dāng)用戶在未結(jié)束第一個(gè)事務(wù)又重新打開一個(gè)事務(wù)，則前一個(gè)事務(wù)會(huì)自動(dòng)提交，同樣MySQL命令中很多命令都會(huì)隱藏執(zhí)行COMMIT命令。

MySQL行為

在MySQL中，存在兩個(gè)可以控制行為的變量，它們分別是AUTOCOMMIT變量和TRANSACTIONISOLACTIONLEVEL變量。

自動(dòng)提交

在MySQL中，如果不更改其自動(dòng)提交變量，則系統(tǒng)會(huì)自動(dòng)向數(shù)據(jù)庫(kù)提交結(jié)果，用戶在執(zhí)行數(shù)據(jù)庫(kù)操作過(guò)程中，不需要使用STARTTRANSACTION語(yǔ)句開始事務(wù)，應(yīng)用COMMIT或者ROLLBACK提交事務(wù)或執(zhí)行回滾操作。如果用戶希望通過(guò)控制MySQL自動(dòng)提交參數(shù)，可以更改提交模式，這一更改過(guò)程是通過(guò)設(shè)置AUTOCOMMIT變量來(lái)實(shí)現(xiàn)。

下面通過(guò)一個(gè)示例向讀者展示如何關(guān)閉自動(dòng)提交參數(shù)。在命令提示符中輸入以下命令：

SETAUTOCOMMIT=0；

只有當(dāng)用戶輸入COMMIT命令后，MySQL才將數(shù)據(jù)表中的資料提交到數(shù)據(jù)庫(kù)中，如果不提交事務(wù)，而終止MySQL會(huì)話，數(shù)據(jù)庫(kù)將會(huì)自動(dòng)執(zhí)行回滾操作。

可以通過(guò)查看@@AUTOCOMMIT變量來(lái)查看當(dāng)前自動(dòng)提交狀態(tài)，查看此變量SELECT@@AUTOCOMMIT。

事務(wù)的隔離級(jí)別

基于ANSI/ISOSQL規(guī)范，MySQL提供4種孤立級(jí)：

SERIALIZABLE（序列化）

REPEATABLEREAD（可重讀）

READCOMMITTED（提交后讀）

READUNCOMMITTED（未提交讀）

在MySQL中，可以使用TRANSACTIONISOLATIONLEVEL變量來(lái)修改事務(wù)孤立級(jí)，其中，MySQL的默認(rèn)隔離級(jí)別為REPEATABLEREAD（可重讀），用戶可以使用SELECT命令獲取當(dāng)前事務(wù)孤立級(jí)變量的值，其命令如下：

SELECT@@tx_isolation；

如果用戶想要修改事務(wù)的隔離級(jí)別，必須首先獲取SUPER優(yōu)先權(quán)，以便用戶可以順利執(zhí)行修改操作，set。

事務(wù)的使用技巧和注意事項(xiàng)

應(yīng)用小事務(wù)，保證每個(gè)事務(wù)不會(huì)在執(zhí)行前等待很長(zhǎng)時(shí)間，從而避免各個(gè)事務(wù)因?yàn)榛ハ嗟却鴮?dǎo)致系統(tǒng)性能的大幅度下降。

選擇合適的孤立級(jí)，因?yàn)槭聞?wù)的性能與其對(duì)服務(wù)器產(chǎn)生的負(fù)載成反比，即當(dāng)事務(wù)孤立級(jí)越高，其性能越低，但是其安全性也越高。只有選擇適當(dāng)?shù)墓铝⒓?jí)，才能有效地提高M(jìn)ySQL系統(tǒng)性能和應(yīng)用性。

死鎖的概念與避免，即當(dāng)兩個(gè)或者多個(gè)處于不同序列的用戶打算同時(shí)更新某相同的數(shù)據(jù)庫(kù)時(shí)，因互相等待對(duì)方釋放權(quán)限而導(dǎo)致雙方一直處于等待狀態(tài)。在實(shí)際應(yīng)用中，兩個(gè)不同序列的客戶打算同時(shí)對(duì)數(shù)據(jù)執(zhí)行操作，極有可能產(chǎn)生死鎖。更具體地講，當(dāng)兩個(gè)事務(wù)相互等待操作對(duì)方釋放所持有的資源，而導(dǎo)致兩個(gè)事務(wù)都無(wú)法操作對(duì)方持有的資源，這樣無(wú)限期的等待被稱作死鎖。MySQL的InnoDB表處理程序具有檢查死鎖這一功能，如果該處理程序發(fā)現(xiàn)用戶在操作過(guò)程中產(chǎn)生死鎖，該處理程序立刻通過(guò)撤銷操作來(lái)撤銷其中一個(gè)事務(wù)，以便使死鎖消失。這樣就可以使另一個(gè)事務(wù)獲取對(duì)方所占有的資源而執(zhí)行邏輯操作。

MySQL偽事務(wù)

在MySQL中，InnoDB和BDB類型表可以支持事務(wù)處理，但是MySQL中MyISAM類型表并不能支持事務(wù)處理，對(duì)于某些應(yīng)用該類型的數(shù)據(jù)表，用戶可以選擇應(yīng)用表鎖定來(lái)替代事務(wù)。這種引用表鎖定來(lái)替代事務(wù)的事件被稱作偽事務(wù)。使用表鎖定來(lái)鎖定表的操作，可以加強(qiáng)非事務(wù)表在執(zhí)行過(guò)程的安全性和穩(wěn)定性。

用表鎖定代替事務(wù)

在MySQL的MyISAM類型數(shù)據(jù)表中，并不支持COMMIT（提交）和ROLLBACK（回滾）命令。當(dāng)用戶對(duì)數(shù)據(jù)庫(kù)執(zhí)行插入、刪除、更新等操作時(shí)，這些變化的數(shù)據(jù)都被立刻保存在磁盤中。這樣，在多用戶環(huán)境中，會(huì)導(dǎo)致諸多問題。為了避免同一時(shí)間有多個(gè)用戶對(duì)數(shù)據(jù)庫(kù)中指定表進(jìn)行操作，可以應(yīng)用表鎖定來(lái)避免在用戶操作數(shù)據(jù)表過(guò)程中受到干擾。當(dāng)且僅當(dāng)該用戶釋放表的操作鎖定后，其他用戶才可以訪問這些修改后的數(shù)據(jù)表。

設(shè)置表鎖定代替事務(wù)基本步驟如下：

（1）為指定數(shù)據(jù)表添加鎖定。其語(yǔ)法如下：

LOCKTABLEStable_namelock_type，

其中，table_name為被鎖定的表名，lock_type為鎖定類型，該類型包括以讀方式（READ）鎖定表，以寫方式（WRITE）鎖定表。

（2）用戶執(zhí)行數(shù)據(jù)表的操作，可以添加、刪除或者更改部分?jǐn)?shù)據(jù)。

（3）用戶完成對(duì)鎖定數(shù)據(jù)表的操作后，需要對(duì)該表進(jìn)行解鎖操作，釋放該表的鎖定狀態(tài)。其語(yǔ)法如下：

UNLOCKTABLES

以讀方式鎖定數(shù)據(jù)表，該方式是設(shè)置鎖定用戶的其他方式操作，如刪除、插入、更新都不被允許，直至用戶進(jìn)行解鎖操作。

locktablestudentinforead；

其中的lock_type參數(shù)中，用戶指定數(shù)據(jù)表以讀方式（READ）鎖定數(shù)據(jù)表的變體為READLOCAL鎖定，其與READ鎖定的不同點(diǎn)是，該參數(shù)所指定的用戶會(huì)話可以執(zhí)行INSERT操作，它是為了使用MySQLdump工具而創(chuàng)建的一種變體形式。

以寫方式鎖定數(shù)據(jù)表，該方式是是設(shè)置用戶可以修改數(shù)據(jù)表中的數(shù)據(jù)，但是除自己以外其他會(huì)話中的用戶不能進(jìn)行任何讀操作。在命令提示符中輸入如下命令：

locktablestudentinfowrite；

當(dāng)數(shù)據(jù)表被釋放鎖定后，其他訪問數(shù)據(jù)庫(kù)的用戶即可查看數(shù)據(jù)表的內(nèi)容。

應(yīng)用表鎖實(shí)現(xiàn)偽事務(wù)

通過(guò)使用表鎖定對(duì)MyISAM表進(jìn)行鎖定操作，以此過(guò)程來(lái)代替事務(wù)型表InnoDB，即應(yīng)用表鎖定來(lái)實(shí)現(xiàn)偽事務(wù)。實(shí)現(xiàn)偽事務(wù)的一般步驟如下：

（1）對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)表進(jìn)行鎖定操作，可以對(duì)多個(gè)表做不同的方式鎖定，其代碼格式如下：

LOCKTABLEtable_name1lock_type1，table_name2lock_type2，

（2）執(zhí)行數(shù)據(jù)庫(kù)操作，向鎖定的數(shù)據(jù)表中執(zhí)行添加、刪除、修改操等操作。

如前面提到的INSERT、UPDATE、DELETE等操作。用戶可以對(duì)鎖定的數(shù)據(jù)表執(zhí)行上述操作，在執(zhí)行過(guò)程中，該偽事務(wù)所產(chǎn)生的結(jié)果是不會(huì)被其他用戶更改的。

（3）釋放鎖定的數(shù)據(jù)表，以便讓正在隊(duì)列中等待查看或操作的其他用戶可以瀏覽數(shù)據(jù)表中的數(shù)據(jù)或?qū)Σ僮鞅韴?zhí)行各種數(shù)據(jù)的操作。

如果存在其他會(huì)話要求訪問已鎖定的多個(gè)表格，則該會(huì)話必須被迫等待當(dāng)前鎖定用戶釋放鎖定表。才允許其他會(huì)話訪問該數(shù)據(jù)表，表鎖定使不同會(huì)話執(zhí)行的數(shù)據(jù)庫(kù)操作彼此獨(dú)立。應(yīng)用數(shù)據(jù)表鎖定方式可以使不支持事務(wù)類型的表實(shí)現(xiàn)偽事務(wù)。

Python執(zhí)行MySQL事務(wù)

一、MySQL事務(wù)

事務(wù)就是指邏輯上的一組SQL操作，組成這組操作的各個(gè)SQL語(yǔ)句，執(zhí)行時(shí)要么全成功要么全失敗。

舉個(gè)例子，小明給小紅轉(zhuǎn)賬100元，轉(zhuǎn)賬過(guò)程實(shí)際上就是小明的賬戶減少100元，小紅的賬戶增加100元，對(duì)應(yīng)的SQL語(yǔ)句為：

updateaccountsetmoney=money-5wherename=xiaoming

updateaccountsetmoney=money+5wherename=xiaohong

上述的兩條SQL操作，在事務(wù)中的操作就是要么都執(zhí)行成功，要么都執(zhí)行失敗，如果只有第一條成功，那么小明就損失100元，而小紅并沒有收到100元，這是不可取的，所以這就是事務(wù)，事務(wù)就是指邏輯上的一組SQL操作，組成這組操作的各個(gè)SQL語(yǔ)句，執(zhí)行時(shí)要么全成功要么全失敗。事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的SQL語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行，在MySQL中只有使用了Innodb庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)，所以很多情況下我們都使用Innodb引擎.

事務(wù)的特性如下：

原子性：事務(wù)是一個(gè)不可分割的單位，事務(wù)中的所有SQL操作要么都成功，要么都失敗

一致性：事務(wù)發(fā)生前和發(fā)生后，數(shù)據(jù)的完整性必須保持一致

隔離性：當(dāng)并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)，一個(gè)正在執(zhí)行的事務(wù)在執(zhí)行完畢前，對(duì)于其它的會(huì)話是不可見的，多個(gè)并發(fā)事務(wù)之間的數(shù)據(jù)是相互隔離的

持久性：一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)改變就是永久性的，如果出了錯(cuò)誤，事務(wù)也不允許撤銷，只能通過(guò)補(bǔ)償性事務(wù)

mysqlbegin#開啟事務(wù)

mysqlrollback#回滾事務(wù)

mysqlcommit#提交事務(wù)

數(shù)據(jù)庫(kù)默認(rèn)事務(wù)是自動(dòng)提交的，也就是說(shuō)，當(dāng)我們執(zhí)行select，insert，update，delete等操作時(shí)，就會(huì)自動(dòng)提交事務(wù)，如果關(guān)閉事務(wù)的自動(dòng)提交，那么我們執(zhí)行完select，insert，update，delete操作后需要再執(zhí)行commit來(lái)提交事務(wù)，否則就不會(huì)執(zhí)行

二、游標(biāo)

游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果，用法如下：

In[1]:importpymysql

In[2]:c=MySQLdb.connect(user=root,passwd=root,db=test)#連接數(shù)據(jù)庫(kù)

In[3]:cus=c.cursor()#創(chuàng)建一個(gè)游標(biāo)對(duì)象

In[4]:cus.execute(select*fromuser;)#使用exec