鬣狗技術- Java 資料庫專題｜6. JDBC協議 第10章：Transactions

健康生活、快樂學習、深度思考

大廠深耕多年，技術不斷學習迭代並且深入研究，熱愛技術，熱愛開源

右上角⬆️點選關注➕，一起進步，關注作者並獲取最新技術資訊和技術文章；持續更新有思考深度的文章和技術。

作者：鬣狗

日期：2021年7月18日

這是我建立的一個測試資料庫，本章的事務講解都是用這個測試表來進行說明。

圖1 測試表

表名為user，id是主鍵。

0。簡介

Jdbc協議規範第十章主要說明三個概念：

Auto-commit mode

Transaction isolation levels

Savepoints

Transaction Boundaries and Auto-commit

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source。 Although some data sources implement an explicit “begin transaction” statement， there is no JDBC API to do so。 Typically， a new transaction is started when the current SQL statement requires one and there is no transaction already in place。 Whether or not a given SQL statement requires a transaction is also specified by SQL：2003。

這段的重點就是，“Typically， a new transaction is started when the current SQL statement requires one and there is no transaction already in place。”- 預設情況下執行當前SQL的時候會預設啟動事務，而不需要主動宣告“begin transaction”。例如，下面兩條語句的執行效果是一樣的：

begin transaction

select * from user ；

等價於

select * from user；

The Connection attribute auto-commit specifies when to end transactions。 Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete。 The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it：

* For Data Manipulation Language （DML） statements such as Insert， Update， Delete， and DDL statements， the statement is complete as soon as it has finished executing。

* For Select statements， the statement is complete when the associated result set is closed。

* For CallableStatement objects or for statements that return multiple results， the statement is complete when all of the associated result sets have been closed， and all update counts and output parameters have been retrieved。

Connection物件中的auto-commit屬性指明什麼時間結束事務。當開啟auto-commit屬性後，每條statement語句執行完成後事物提交。在mysql資料庫中，開啟auto-commit屬性語句

set autocommit = on ，

這條命令使auto-commit屬性在當前連線中生效。

當開啟auto-commit屬性，對於DML的insert、update、delete和DDL語句執行完成事務就提交了。

但是對於select語句，只有其關聯的結果集關閉的時候事務才提交。

Disabling Auto-commit mode

// Assume con is a Connection objectcon。setAutoCommit（false）；

上述程式碼是關閉aoto-commit屬性，當關閉auto-commit屬性時，需要應用程式使用Connection。commit（）來手動提交事務。

這個特性通常被使用在第三方的事務管理器的實現上面

。

例如下面的程式碼：

con。setAutoCommit（false）；Statement stmt = con。createStatement（）； // get a statementstmt。execute（“update user set age = 15 where id = 2”）；con。commit（）；======= 上面程式碼對應的SQl語句如下：========set autocommit = off；update user set age = 15 where id = 2 ；commit；

Transaction Isolation Levels

這章是重點，這塊直接引用Jdbc規範上面的解釋，官方原文清楚的解釋了：

什麼是事務的隔離性

事務隔離級別

事務隔離下的遇到的問題

Transaction isolation levels specify what data is “visible” to the statements within a transaction。

即事務隔離級別指明事務內的statement(也就是SQL語句)能看到哪些資料，這句話翻譯成人話就是當前事務那的SQL語句能夠查詢到哪些資料，不同的事物隔離級別查出來的資料是不一樣的。

不同的事務隔離級別會引發不同的問題：

Dirty Reads

Dirty Reads occur when transactions are allowed to see uncommitted changes to the data。 In other words， changes made inside a transaction are visible outside the transaction before it is committed。 If the changes are rolled back instead of being committed， it is possible for other transactions to have done work based on incorrect， transient data。

髒讀，即讀到其它事務還沒有提交的資料。如下：

// 事務Aset autocommit = off；update user set name = ‘tommy’ where id = 2；// 事務Bset transaction isolation level read uncommitted ；select * from user where id = 2；// 結果為|id| name | age || 2| tommy| 15 |

事務B將當前連線的事務隔離級別設定為read uncommited，可以看到事務B讀到了事務A未提交的資料。

non-repeatable reads

nonrepeatable reads occur when：

a。 Transaction A reads a row

b。 Transaction B changes the row

c。 Transaction A reads the same row a second time and gets different results

不可重複讀發生的場景已經說得很明白了。記住關鍵詞A Row就好

phantom reads

a。 Transaction A reads all rows that satisfy a WHERE condition

b。 Transaction B inserts an additional row that satisfies the same condition

c。 Transaction A reevaluates the WHERE condition and picks up the additional “phantom” row

幻讀發生的場景也比較固定，即使用where條件篩選出來的是一個結果集，而不是單獨一行的情況。

isolation ladder

如圖是我自己畫的一個isolation ladder，其中橫軸是isolation level，縱軸是concurreny，其中可以看到有四種隔離級別，分別是：read-uncommited、read-commited、repeatable-read、serializable；並且每種隔離級別下指明當前存在的問題。而且還能夠看到，隨著隔離級別的提高，併發度是逐漸下降的。

圖2 isolation ladder

Savepoints

Savepoints provide finer-grained control of transactions by marking intermediate points within a transaction。 Once a savepoint has been set， the transaction can be rolled back to that savepoint without affecting preceding work。

The DatabaseMetaData。supportsSavepoints method can be used to determine whether a JDBC driver and DBMS support savepoints。

savepoints類似checkpoint的概念，事務回滾的時候可以指定回滾到之前的哪個位置（savepoints），而不是直接回到事務開始的狀態。

當然這個必須先要設定啟動auto-commit屬性。

set autocommit = off；savepoint `A`；update user set name = ‘jacky’ where id = 1；savepoint `B`；update user set name = ‘jackend’ where id = 1；select * from user where id = 1； # name is jackendrollback to `A`；select * from user where id = 1； # name is jack

Releasing a Savepoint

The method Connection。releaseSavepoint takes a Savepoint object as a parameter and removes it and any subsequent savepoints from the current transaction。 Once a savepoint has been released， attempting to reference it in a rollback operation will cause an SQLException to be thrown。 Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed or when the entire transaction is rolled back。 Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question。

Connection。releaseSavePoint（）方法用來釋放儲存點。對應的SQL語句為：release savepoint `Asavepoint`。

其它

github主頁：https://github.com/youngFF

git倉庫地址：https://github.com/youngFF/MyHearthStone.git

gitbook地址：https://youngff.github.io/MyHearthStone/

歡迎各位加入鬣狗技術社群，希望能夠為您提供有思考、有深度的文章。歡迎加入我們，如果你也有想法在鬣狗技術社群發表文章，頭條私聊即可。

求 關注➕轉發➕點贊，謝謝各位！您的支援就是我們更新的動力