)
后端開(kāi)發(fā)中轉(zhuǎn)賬、訂單創(chuàng)建、庫(kù)存扣減這類業(yè)務(wù)必須保證多條數(shù)據(jù)庫(kù)操作全部成功或者全部回滾這就是事務(wù)的價(jià)值。本文基于 pymysql 講解 Python 下 MySQL 事務(wù)完整用法包含原理、代碼示例、異常處理、常見(jiàn)坑與最佳實(shí)踐。環(huán)境準(zhǔn)備安裝 pymysqlpipinstallpymysql注意MySQL 只有 InnoDB 引擎支持事務(wù)MyISAM 不支持事務(wù)。測(cè)試表準(zhǔn)備CREATETABLEaccount(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(32)NOTNULL,balanceDECIMAL(12,2)NOTNULLDEFAULT0)ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTOaccount(username,balance)VALUES(zhangsan,1000.00),(lisi,1000.00);什么是事務(wù)事務(wù)是一組SQL操作的邏輯單元全部執(zhí)行成功執(zhí)行commit()提交修改永久生效任意步驟失敗執(zhí)行rollback()回滾撤銷這一組所有修改。ACID四大特性原子性(Atomicity)事務(wù)內(nèi)操作不可分割全部成功或全部失敗。一致性(Consistency)事務(wù)執(zhí)行前后業(yè)務(wù)數(shù)據(jù)保持合法狀態(tài)。隔離性(Isolation)多個(gè)事務(wù)之間互相隔離受事務(wù)隔離級(jí)別控制。持久性(Durability)commit提交之后修改永久保存數(shù)據(jù)庫(kù)宕機(jī)也不會(huì)丟失。pymysql事務(wù)核心要點(diǎn)pymysql 默認(rèn)autocommitTrue每條SQL執(zhí)行后立即提交此時(shí)沒(méi)有事務(wù)效果。使用事務(wù)必須關(guān)閉自動(dòng)提交conn.autocommit(False)。兩個(gè)關(guān)鍵方法conn.commit()提交事務(wù)conn.rollback()回滾事務(wù)同一個(gè)事務(wù)必須使用同一個(gè)connection連接對(duì)象不能換連接。示例1轉(zhuǎn)賬業(yè)務(wù)try?except標(biāo)準(zhǔn)寫法張三轉(zhuǎn)賬200元給李四模擬業(yè)務(wù)異?；貪L。importpymysqldeftransfer():connpymysql.connect(host127.0.0.1,port3306,userroot,passwordxxx,databasetest_db,charsetutf8mb4)# 關(guān)閉自動(dòng)提交開(kāi)啟事務(wù)模式conn.autocommit(False)cursorconn.cursor(pymysql.cursors.DictCursor)try:# 1. 張三扣200cursor.execute(UPDATE account SET balance balance - %s WHERE username %s,(200,zhangsan))# 2. 模擬異常觸發(fā)回滾# 1 / 0# 3. 李四加200cursor.execute(UPDATE account SET balance balance %s WHERE username %s,(200,lisi))# 全部成功提交事務(wù)conn.commit()print(事務(wù)提交成功)exceptExceptionase:# 出現(xiàn)任何異?；貪L所有變更c(diǎn)onn.rollback()print(f事務(wù)回滾異常{e})finally:cursor.close()conn.close()if__name____main__:transfer()打開(kāi)代碼中1/0模擬報(bào)錯(cuò)你會(huì)發(fā)現(xiàn)兩條update全部失效不會(huì)出現(xiàn)張三扣錢、李四沒(méi)加錢的數(shù)據(jù)錯(cuò)亂。示例2with上下文管理器用法pymysql 的 connection 支持 with退出上下文如果沒(méi)有commit會(huì)自動(dòng)回滾。importpymysqldeftransfer_with():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse,charsetutf8mb4)try:withconn.cursor(pymysql.cursors.DictCursor)ascur:cur.execute(UPDATE account SET balancebalance-%s WHERE username%s,(100,zhangsan))cur.execute(UPDATE account SET balancebalance%s WHERE username%s,(100,lisi))# with游標(biāo)結(jié)束不會(huì)自動(dòng)commit需要手動(dòng)提交conn.commit()print(提交成功)exceptExceptionase:conn.rollback()print(f回滾{e})finally:conn.close()??重要提醒with cursor()只是管理游標(biāo)不會(huì)自動(dòng)commit/rollback事務(wù)的提交回滾仍然由connection控制。示例3嵌套事務(wù)MySQL沒(méi)有真正嵌套事務(wù)MySQL InnoDB 不支持真正嵌套事務(wù)可以使用保存點(diǎn) savepoint實(shí)現(xiàn)局部回滾。defsavepoint_demo():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse)curconn.cursor()try:cur.execute(UPDATE account SET balancebalance-50 WHERE usernamezhangsan)# 設(shè)置保存點(diǎn)cur.execute(SAVEPOINT sp1)cur.execute(UPDATE account SET balancebalance50 WHERE usernamelisi)# 回滾到保存點(diǎn)sp1只撤銷后面的操作前面的保留cur.execute(ROLLBACK TO SAVEPOINT sp1)conn.commit()exceptExceptionase:conn.rollback()print(e)finally:cur.close()conn.close()常見(jiàn)踩坑清單autocommit忘記關(guān)閉autocommitTrue時(shí)每一條SQL直接生效commit/rollback完全無(wú)效事務(wù)失效。事務(wù)中途更換connection對(duì)象同一個(gè)事務(wù)的多條SQL必須在同一個(gè)連接換連接等于新開(kāi)另一個(gè)事務(wù)。查詢不會(huì)加鎖update/delete才會(huì)產(chǎn)生事務(wù)變更普通select不會(huì)修改數(shù)據(jù)SELECT ... FOR UPDATE會(huì)開(kāi)啟行鎖用于并發(fā)扣庫(kù)存場(chǎng)景。異常捕獲后忘記rollback如果發(fā)生異常不執(zhí)行rollback未提交的事務(wù)會(huì)一直掛起占用數(shù)據(jù)庫(kù)鎖資源產(chǎn)生鎖等待。MyISAM引擎使用事務(wù)MyISAM不支持事務(wù)commit/rollback調(diào)用無(wú)效果建表必須指定ENGINEInnoDB。長(zhǎng)事務(wù)事務(wù)不要長(zhǎng)時(shí)間不commit/rollback長(zhǎng)事務(wù)會(huì)大量占用回滾段、鎖資源嚴(yán)重影響數(shù)據(jù)庫(kù)性能。結(jié)合SELECT FOR UPDATE并發(fā)扣庫(kù)存場(chǎng)景悲觀鎖示例防止并發(fā)超賣defdeduct_stock():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse)curconn.cursor()try:# for update 行鎖其他事務(wù)會(huì)阻塞此處cur.execute(SELECT balance FROM account WHERE usernamezhangsan FOR UPDATE)rowcur.fetchone()ifrow[0]100:cur.execute(UPDATE account SET balancebalance-100 WHERE usernamezhangsan)conn.commit()exceptExceptionase:conn.rollback()print(e)finally:cur.close()conn.close()最佳實(shí)踐總結(jié)業(yè)務(wù)涉及多寫操作必須使用事務(wù)設(shè)置autocommitFalse。使用try?except?finally異常分支必須執(zhí)行rollback()最后關(guān)閉連接。事務(wù)粒度盡量小避免長(zhǎng)事務(wù)執(zhí)行完盡快commit或rollback釋放鎖。并發(fā)場(chǎng)景需要鎖時(shí)合理使用SELECT ... FOR UPDATE悲觀鎖或業(yè)務(wù)層樂(lè)觀鎖。確認(rèn)表引擎為InnoDB。一個(gè)事務(wù)全程復(fù)用同一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象。如果你使用 SQLAlchemy ORM框架會(huì)封裝事務(wù)邏輯但底層依然是MySQL事務(wù)機(jī)制。 Python 如何使用 MySQL 的事務(wù)
后端開(kāi)發(fā)中轉(zhuǎn)賬、訂單創(chuàng)建、庫(kù)存扣減這類業(yè)務(wù)必須保證多條數(shù)據(jù)庫(kù)操作全部成功或者全部回滾這就是事務(wù)的價(jià)值。本文基于 pymysql 講解 Python 下 MySQL 事務(wù)完整用法包含原理、代碼示例、異常處理、常見(jiàn)坑與最佳實(shí)踐。環(huán)境準(zhǔn)備安裝 pymysqlpipinstallpymysql注意MySQL 只有 InnoDB 引擎支持事務(wù)MyISAM 不支持事務(wù)。測(cè)試表準(zhǔn)備CREATETABLEaccount(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(32)NOTNULL,balanceDECIMAL(12,2)NOTNULLDEFAULT0)ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTOaccount(username,balance)VALUES(zhangsan,1000.00),(lisi,1000.00);什么是事務(wù)事務(wù)是一組SQL操作的邏輯單元全部執(zhí)行成功執(zhí)行commit()提交修改永久生效任意步驟失敗執(zhí)行rollback()回滾撤銷這一組所有修改。ACID四大特性原子性(Atomicity)事務(wù)內(nèi)操作不可分割全部成功或全部失敗。一致性(Consistency)事務(wù)執(zhí)行前后業(yè)務(wù)數(shù)據(jù)保持合法狀態(tài)。隔離性(Isolation)多個(gè)事務(wù)之間互相隔離受事務(wù)隔離級(jí)別控制。持久性(Durability)commit提交之后修改永久保存數(shù)據(jù)庫(kù)宕機(jī)也不會(huì)丟失。pymysql事務(wù)核心要點(diǎn)pymysql 默認(rèn)autocommitTrue每條SQL執(zhí)行后立即提交此時(shí)沒(méi)有事務(wù)效果。使用事務(wù)必須關(guān)閉自動(dòng)提交conn.autocommit(False)。兩個(gè)關(guān)鍵方法conn.commit()提交事務(wù)conn.rollback()回滾事務(wù)同一個(gè)事務(wù)必須使用同一個(gè)connection連接對(duì)象不能換連接。示例1轉(zhuǎn)賬業(yè)務(wù)try?except標(biāo)準(zhǔn)寫法張三轉(zhuǎn)賬200元給李四模擬業(yè)務(wù)異?；貪L。importpymysqldeftransfer():connpymysql.connect(host127.0.0.1,port3306,userroot,passwordxxx,databasetest_db,charsetutf8mb4)# 關(guān)閉自動(dòng)提交開(kāi)啟事務(wù)模式conn.autocommit(False)cursorconn.cursor(pymysql.cursors.DictCursor)try:# 1. 張三扣200cursor.execute(UPDATE account SET balance balance - %s WHERE username %s,(200,zhangsan))# 2. 模擬異常觸發(fā)回滾# 1 / 0# 3. 李四加200cursor.execute(UPDATE account SET balance balance %s WHERE username %s,(200,lisi))# 全部成功提交事務(wù)conn.commit()print(事務(wù)提交成功)exceptExceptionase:# 出現(xiàn)任何異?；貪L所有變更c(diǎn)onn.rollback()print(f事務(wù)回滾異常{e})finally:cursor.close()conn.close()if__name____main__:transfer()打開(kāi)代碼中1/0模擬報(bào)錯(cuò)你會(huì)發(fā)現(xiàn)兩條update全部失效不會(huì)出現(xiàn)張三扣錢、李四沒(méi)加錢的數(shù)據(jù)錯(cuò)亂。示例2with上下文管理器用法pymysql 的 connection 支持 with退出上下文如果沒(méi)有commit會(huì)自動(dòng)回滾。importpymysqldeftransfer_with():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse,charsetutf8mb4)try:withconn.cursor(pymysql.cursors.DictCursor)ascur:cur.execute(UPDATE account SET balancebalance-%s WHERE username%s,(100,zhangsan))cur.execute(UPDATE account SET balancebalance%s WHERE username%s,(100,lisi))# with游標(biāo)結(jié)束不會(huì)自動(dòng)commit需要手動(dòng)提交conn.commit()print(提交成功)exceptExceptionase:conn.rollback()print(f回滾{e})finally:conn.close()??重要提醒with cursor()只是管理游標(biāo)不會(huì)自動(dòng)commit/rollback事務(wù)的提交回滾仍然由connection控制。示例3嵌套事務(wù)MySQL沒(méi)有真正嵌套事務(wù)MySQL InnoDB 不支持真正嵌套事務(wù)可以使用保存點(diǎn) savepoint實(shí)現(xiàn)局部回滾。defsavepoint_demo():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse)curconn.cursor()try:cur.execute(UPDATE account SET balancebalance-50 WHERE usernamezhangsan)# 設(shè)置保存點(diǎn)cur.execute(SAVEPOINT sp1)cur.execute(UPDATE account SET balancebalance50 WHERE usernamelisi)# 回滾到保存點(diǎn)sp1只撤銷后面的操作前面的保留cur.execute(ROLLBACK TO SAVEPOINT sp1)conn.commit()exceptExceptionase:conn.rollback()print(e)finally:cur.close()conn.close()常見(jiàn)踩坑清單autocommit忘記關(guān)閉autocommitTrue時(shí)每一條SQL直接生效commit/rollback完全無(wú)效事務(wù)失效。事務(wù)中途更換connection對(duì)象同一個(gè)事務(wù)的多條SQL必須在同一個(gè)連接換連接等于新開(kāi)另一個(gè)事務(wù)。查詢不會(huì)加鎖update/delete才會(huì)產(chǎn)生事務(wù)變更普通select不會(huì)修改數(shù)據(jù)SELECT ... FOR UPDATE會(huì)開(kāi)啟行鎖用于并發(fā)扣庫(kù)存場(chǎng)景。異常捕獲后忘記rollback如果發(fā)生異常不執(zhí)行rollback未提交的事務(wù)會(huì)一直掛起占用數(shù)據(jù)庫(kù)鎖資源產(chǎn)生鎖等待。MyISAM引擎使用事務(wù)MyISAM不支持事務(wù)commit/rollback調(diào)用無(wú)效果建表必須指定ENGINEInnoDB。長(zhǎng)事務(wù)事務(wù)不要長(zhǎng)時(shí)間不commit/rollback長(zhǎng)事務(wù)會(huì)大量占用回滾段、鎖資源嚴(yán)重影響數(shù)據(jù)庫(kù)性能。結(jié)合SELECT FOR UPDATE并發(fā)扣庫(kù)存場(chǎng)景悲觀鎖示例防止并發(fā)超賣defdeduct_stock():connpymysql.connect(host127.0.0.1,userroot,passwordxxx,databasetest_db,autocommitFalse)curconn.cursor()try:# for update 行鎖其他事務(wù)會(huì)阻塞此處cur.execute(SELECT balance FROM account WHERE usernamezhangsan FOR UPDATE)rowcur.fetchone()ifrow[0]100:cur.execute(UPDATE account SET balancebalance-100 WHERE usernamezhangsan)conn.commit()exceptExceptionase:conn.rollback()print(e)finally:cur.close()conn.close()最佳實(shí)踐總結(jié)業(yè)務(wù)涉及多寫操作必須使用事務(wù)設(shè)置autocommitFalse。使用try?except?finally異常分支必須執(zhí)行rollback()最后關(guān)閉連接。事務(wù)粒度盡量小避免長(zhǎng)事務(wù)執(zhí)行完盡快commit或rollback釋放鎖。并發(fā)場(chǎng)景需要鎖時(shí)合理使用SELECT ... FOR UPDATE悲觀鎖或業(yè)務(wù)層樂(lè)觀鎖。確認(rèn)表引擎為InnoDB。一個(gè)事務(wù)全程復(fù)用同一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象。如果你使用 SQLAlchemy ORM框架會(huì)封裝事務(wù)邏輯但底層依然是MySQL事務(wù)機(jī)制。