Oracle 與 MySQL 的差異分析（9）：事務

1 自動送出

1.1Oracle

預設不會自動送出，需要顯式的送出或復原。如果斷開連接配接時有未送出事務，用戶端工具一般可以配置自動送出或復原。

1.2 MySQL

InnoDB支援事務，預設是自動送出的。

關閉自動送出的方法：

（1）會話級關閉：

set autocommit = 0; // 1表示開啟

檢查是否關閉：

show variables like “autocommit”;

結果：autocommit OFF

（2）全局關閉：

set global autocommit = 0 或者把 autocommit=0 加到參數檔案中。

注：在開啟自動送出情況下，可以用starttransaction; 開啟一個事務，然後就不會自動送出了。

2 鎖級别

2.1Oracle

DML語句産生行鎖，鎖資訊儲存在資料塊上，隻有被更新的資料會被鎖定。

2.2 MySQL

不同引擎使用不同的鎖級别，InnoDB預設也是行鎖，但是它鎖定的是索引條目，這一點與Oracle顯著不同，如果沒有通過索引來更新資料的話，那麼就會鎖定整個表。需要注意：

（1）雖然查詢列上有索引，但是優化器仍然可能選擇全表掃描，由于沒有通過索引通路資料，此時仍然是表鎖（鎖定所有資料）。

（2）通過A索引查出的資料，雖然被B條件過濾掉了，但是該資料仍然會被鎖定。比如，userid = 1and username = ‘a’ 如果是走了userid的索引，雖然沒有更新username = ‘b’的資料，但是試圖通過userid = 1and username = ‘b’ 更新資料時也會被阻塞。

（3）如果通過userid > 1 來更新資料，并且走userid的索引，那麼會鎖定所有userid >1 的資料，包括未插入的資料，是以此時insert一條userid = 100 的資料時也會被阻塞。這一點與Oracle很不同，Oracle的insert語句一般不會被阻塞。

3 事務隔離級别

SQL标準中定義的隔離級别，隔離級别越高，并發性越差。

（1）Read Uncommitted：

B會話可以看到A會話未送出事務修改的資料（髒資料）。

（2）Read Commited:

A會話的事務送出了，B會話就可以看到修改的資料。

可以避免髒讀（讀到未送出的資料）。

（3）Repeatable：

A會話的一個事務内查詢同一個表的資料不會變化，即使B會話修改了資料并且已經送出。

可以避免不可重複讀問題（同一個SQL第二次發現資料已變化）。

（4）Serializable：

事務好像是串行的，它是在每個讀的資料上加上共享鎖。select相當于select ... from update。

可以避免幻影讀問題（同一個SQL第二次讀/寫不會看到新的資料）。

3.1Oracle

預設隔離級别是Read Commited，支援Serializable，不過一般不用。

3.2 MySQL

InnoDB預設事務隔離級别是Repeatable，其它幾種也支援。

查詢事務隔離級别：

select @@tx-isolation;

結果：REPEATABLE-READ

修改方法：

set session|global tx-isolation = ‘serializable’;

4 鎖逾時時間

4.1Oracle

如果A會話鎖定了某條資料，那麼B會話試圖更新這條資料時會被阻塞，并且會一直等待。

4.2 MySQL

MySQL資料庫參數：innodb_lock_wait_timeout設定鎖等待的時間是50s，一旦資料庫鎖超過這個時間就會報逾時錯誤。

預設值有些小，建議改大些，代碼需要考慮這個特性，鎖定資料有失敗的風險，需要捕獲異常，這一點與Oracle不同。