[DB2]原創--實戰式體驗事務隔離級別之UR篇
前言：課上老師佈置了任務，我有幸分到了研究DB2的UR的任務。
UR即 Uncommitted Read（未提交讀），是隔離級別低等級的級別，相當於SQL92標準中的RU（Read Uncommitted），它能夠解決“更新丟失”的讀一致性問題，但是無法避免“髒讀”、“不可重複讀”和“幻讀”。
下面進行實戰操作，來體驗下這種隔離級別。
1、禁止事務自動提交
首先，我們檢視下事物是否自動提交，使用如下命令檢視，發現Option為“-c”的開關值為ON，說明自動提交開啟：
db2 => list command options
Command Line Processor Option Settings
Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) =
Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit ON
-d Retrieve and display XML declarations OFF
………………以下內容省略…………………
我們欲在“全域性”模式下，將自動提交禁止，使用db2set將DB2OPTIONS變數設定為+c即可。具體演示過程如下：
使用db2set命令，檢視下都設定了哪些profile variables：
[db2inst1@localhost ~]$ db2set
DB2COMM=tcpip
DB2AUTOSTART=YES
其中沒有我們設定的變數，大概記變數中帶有“option”，於是進行搜尋：
[db2inst1@localhost ~]$ db2set -lr | grep -i option
DB2OPTIONS
將該變數設定為+c，即可：
[db2inst1@localhost ~]$ db2set DB2OPTIONS=+c
[db2inst1@localhost ~]$ db2set
DB2OPTIONS=+c
DB2COMM=tcpip
DB2AUTOSTART=YES
斷開與db2的連線，再連線上資料庫，使用list command options命令去檢查禁止自動提交是否生效。
2、修改事務隔離級別
我們嘗試著修改資料庫的隔離級別。但是提示，如果資料庫不支援UR，隔離級別會自動升級：
db2 => terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@localhost ~]$ db2 change isolation to ur
DB21053W Automatic escalation will occur when you connect to a database that
does not support UR.
DB20000I The CHANGE ISOLATION command completed successfully.
可以檢視DB21053W具體含義：
db2 => ? DB21053W
DB21053W Automatic escalation will occur when you connect to a database that does not support "
Explanation:
Isolation levels are database dependent. Some, like NC, are only supported by specific databases. If you select an isolation level that is not supported by the database you are connecting to, it will
automatically escalate to a supported level.
User response:
Connect to a database that supports the isolation level you have selected, or select a different isolation level.
由此分析可知，不一定能夠成功得將資料庫的隔離級別設定為UR。
但是，DB2中有一個CURRENT ISOLATION special register，可以修改會話級的事務隔離級別。使用如下語句，確保本會話的級別為ur：
db2 => set current isolation ur
DB20000I The SQL command completed successfully.
檢視當前會話的隔離級別，發現已經是“UR”：
db2 => values current isolation
1
--
UR
注：關於“CURRENT ISOLATION special register”，詳見IBM官方文件：
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010945.htm
3、關於丟失更新、髒讀、不可重複讀和幻讀：
Lost update: This occurs when two transactions read and then attempt to update the same data, and one of the updates is lost.
丟失更新：兩個事務可以對一個值進行修改，導致其中一個事務所更新的值丟失。
Dirty read: This occurs when a transaction reads data that has not yet been committed.
髒讀：一個事務讀取到了，另外一個事務尚未提交的值。
Nonrepeatable read: This occurs when a transaction reads the same row of data twice, but gets different data values each time. For example:Transaction 1 reads a row of data and Transaction 2 changes or deletesthat row and commits the change. When Transaction 1 attempts to reread
the row, it will retrieve different data values (if the row was updated) or discover that the row no longer exists (if the row was deleted).
不可重複讀：當讀取某一行值的時候，發現取值被修改了。如事務1先後兩次讀取相同的資料，卻因為事務2對資料進行了修改或刪除，導致事務1讀取到了更新的值或發現值已經被刪除。
簡單概括就是，先後讀取的資料不一致，讀取到的資料被修改或是被刪除。
Phantom: This occurs when a row of data that matches search criteria is not seen initially, but then seen in a later read operation. For example: Transaction 1 reads a set of rows that satisfy some search criteria and Transaction 2 inserts a new row that matches Transaction 1's search criteria. If Transaction 1 re-executes the query that produced the original set of rows, a different set of rows will be retrieved.
幻讀：按照相同的條件去檢索值，後一次檢索到了新的資料。如，事務1按照某條件檢索資料時，事務2插入了符合該條件的資料。當事務1再次檢索資料時，就會發現檢索出的結果集有新資料。
簡單概括就是，按照相同條件檢索資料，發現結果集中有新資料。
注：英文定義Lost update、Dirty read、Nonrepeatable read和Phantom出處：
DB2 9 Fundamentals exam 730 prep, Part 6: Data concurrency Page 7 of 27
4、UR級別下的實戰操作
準備工作：
1） 修改資料配置引數，開啟鎖監視功能：
db2 => update dbm cfg using dft_mon_lock on
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
2） 開啟兩個客戶端去連線db2，確認好每個客戶端所分別對應的Application Handle。使用list applications命令檢視：
db2 => list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 1911 *LOCAL.db2inst1.100727134142 DEMO 1
db2 => list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 1911 *LOCAL.db2inst1.100727134142 DEMO 1
DB2INST1 db2bp 2060 *LOCAL.db2inst1.100727145725 DEMO 1
db2 => list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 1911 *LOCAL.db2inst1.100727134142 DEMO 1
DB2INST1 db2bp 2061 *LOCAL.db2inst1.100727145812 DEMO 1
DB2INST1 db2bp 2060 *LOCAL.db2inst1.100727145725 DEMO 1
第一個連上來的客戶端的Handle是2060，第二個是2061。
3） 檢視所的情況，我們需要獲取一個鎖的快照並去讀其中的內容，使用如下命令即可：
[db2inst1@localhost ~]$ db2 get snapshot for locks on demo | less
下面，我們正式進入到UR隔離級別下的實戰操作中去。
4.1、 “丟失更新”實驗：
儘管UR隔離級別在關係型資料庫中是最弱的，但該級別下仍能防止丟失更新的出現。下面，我們來進行驗證操作：
4.1.1、目前我們已經建立了一張emps表，我們對其中一行進行更新操作（進行操作的是Handle為2060的Session）：
db2 => select * from emps
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
7903. SMITH Clerk 7788. 10/23/1998 4500. 500. 20.
7823. ROBOT Salesman 7788. 02/02/1995 7500. 200. 20.
1000. SCOTT Presi - 01/01/1986 9000. 3000. 10.
………………部分查詢結果敏感，在此進行省略…………….
db2 => update emps set salary=1234 where empno=7903
DB20000I The SQL command completed successfully.
db2 =>
4.1.2、我們在Handle為2061的Session中，嘗試去修改該行值，看看出現的狀況，發現阻塞了，出於等待狀態：
db2 => update emps set salary=4321 where empno=7903
4.1.3、我們去檢視下，鎖的情況，執行如下命令：
[db2inst1@localhost ~]$ db2 get snapshot for locks on demo | less
在產生的資訊報告中，摘取了如下的資訊：
可以看到，在表EMPS上有一個行級的排它鎖，這種粒度的鎖就足以保證其他會話，不能修改本行的資料，從而能夠避免“丟失更新”的出現。
但是，我們同時還看到，在該表上，還加上了一個表級的意向排它鎖，這種鎖的產生有待於進一步的學習，來解釋該現象。
List Of Locks
Lock Name = 0x05000400040000000000000052
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 4
Object Type = Row
Tablespace Name = TBS_SMS
Table Schema = DB2INST1
Table Name = EMPS
Mode = X
Lock Name = 0x41414141415A425A7F4760B841
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
Lock Name = 0x05000400000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 4
Object Type = Table
Tablespace Name = TBS_SMS
Table Schema = DB2INST1
Table Name = EMPS
Mode = IX
小結：UR隔離級別下，能夠避免“丟失更新”的出現。
現在可以將上述兩個事務依次進行回滾，釋放鎖，為我們下面的實驗做準備。
4.2、“髒讀”實驗
4.2.1、在Handle為2601的會話中，進行如下的事務：
db2 => update emps set salary=7788 where empno=7903
DB20000I The SQL command completed successfully.
4.2.2、在另一個會話中，進行查詢操作：
db2 => select * from emps
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
7903. SMITH Clerk 7788. 10/23/1998 7788. 500. 20.
7823. ROBOT Salesman 7788. 02/02/1995 7500. 200. 20.
1000. SCOTT Presi - 01/01/1986 9000. 3000. 10.
4.2.3、將第一個會話的事務回滾：
db2 => rollback
DB20000I The SQL command completed successfully.
4.2.4、在另一會話中去查詢：
db2 => select * from emps
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
7903. SMITH Clerk 7788. 10/23/1998 4500. 500. 20.
7823. ROBOT Salesman 7788. 02/02/1995 7500. 200. 20.
1000. SCOTT Presi - 01/01/1986 9000. 3000. 10.
小結：上述實驗，真實反應了在UR隔離級別下，一個會話修改資料時，另一個會話能夠讀取到未提交的資料。
4.3、“不可重複讀”實驗
該實驗需要驗證的是，先後兩次檢索到的資料不一致，後檢索到的資料被更新或者刪除了。
4.3.1、在一個事務中查詢如下資訊：
db2 => select * from emps where empno=1000
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
1000. SCOTT Presi - 01/01/1986 9000. 3000. 10.
1 record(s) selected.
4.3.2、另一個事務中，執行刪除操作，並提交事務：
db2 => delete from emps where empno=1000
DB20000I The SQL command completed successfully.
db2 => commit
DB20000I The SQL command completed successfully.
db2 =>
4.3.3、回到第一個事務中，發現剛才查詢的資料已經丟失：
db2 => select * from emps where empno=1000
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
0 record(s) selected.
小結：該隔離級別下，“不可重複讀”難以避免。
4.4、“幻讀”實驗
該實驗需要驗證的是，在UR隔離級別下，按照相同條件去檢索資料，後一次能夠讀取新插入的資料（結果集增大）。
4.4.1、事務1中，去檢索月薪大於8000的員工資料：
db2 => select * from emps where salary>8000
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
7652. HENRY Salesman 7788. 06/28/2004 9800. 100. 20.
1 record(s) selected.
4.4.2、事務2中，去插入一條記錄並提交：
db2 => insert into emps values(9000,'Tom','Manager','9898','11/20/2012',9876,1000,40)
DB20000I The SQL command completed successfully.
db2 => commit
DB20000I The SQL command completed successfully.
db2 =>
4.4.3、事務1中，再次去查詢結果，發現新增了一條記錄：
db2 => select * from emps where salary>8000
EMPNO ENAME JOB MAMANGER_ID HIREDATE SALARY COMM DEPARTMENT_ID
------ ------- -------- ----------- ---------- ------ ------ -------------
9000. Tom Manager 9898. 11/20/2012 9876. 1000. 40.
7652. HENRY Salesman 7788. 06/28/2004 9800. 100. 20.
2 record(s) selected.
5、總結與思考
總結：UR隔離級別下，能夠避免“丟失更新”，但這種最弱的隔離級別無法解決“髒讀”、“不可重複讀”和“幻讀”，三大“讀一致性”問題。
思考：為什麼需要討論“讀一致性”（丟失更新、髒讀、不可重複讀和幻讀）問題？
個人的一些觀點：在多使用者環境的資料庫中，每個使用者都是相互隔離的，很多情況下並不知道自己所能控制的資料，被其他使用者修改了。有時會有這樣的需求：一個使用者，在其自身沒有修改資料的情況下，希望讀取到的資料保持一致。
解決讀一致性的辦法：
1、使用鎖機制，對“讀”出來的資料結果集進行加鎖，阻止其他事務對結果集的修改。不同的隔離級別，鎖的粒度與機制不同。客戶依據自身的業務特點，選取一個合適的隔離級別，展開業務；
2、不使用鎖機制，通過一定機制生成快照，使用者直接去讀快照即可。這種技術就是MVCC（多版本併發控制）。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14600958/viewspace-669657/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 事務隔離級別解析和實戰MySql
- mysql 5.5 innodb事務隔離級別之開篇MySql
- DB2併發控制-事務隔離級別DB2
- MySQL 事務隔離級別MySql
- PostgreSQL事務隔離級別SQL
- 事務、特性、隔離級別
- MySQL事務隔離級別MySql
- [Mysql]事務/隔離級別MySql
- MySQL 事務隔離級別實現原理MySql
- MySQL事務的隔離級別MySql
- MySQL的事務隔離級別MySql
- 理解MySQL事務隔離級別MySql
- mysql修改事務隔離級別MySql
- Oracle-事務隔離級別Oracle
- JDBC 事務（一） 隔離級別JDBC
- MySQL事務隔離級別的實現原理MySql
- SqlServer事務詳解（事務隔離性和隔離級別詳解）SQLServer
- MySQL事務隔離級別和MVCCMySqlMVC
- 事務系統的隔離級別
- mysql如何修改事務隔離級別MySql
- mysql事務隔離級別和鎖MySql
- 啥是 MySQL 事務隔離級別？MySql
- Mysql 四種事務隔離級別MySql
- Mysql鎖與事務隔離級別MySql
- 理解mysql的事務隔離級別MySql
- 資料庫事務隔離級別資料庫
- MySQL事務隔離級別詳解MySql
- SQL Server事務的隔離級別SQLServer
- 重新整理 mysql 基礎篇————— 事務隔離級別[四]MySql
- 資料庫事務與事務的隔離級別資料庫
- 事務隔離級別(圖文詳解)
- 事務ACID特性與隔離級別
- 資料庫事務與隔離級別資料庫
- 事務基礎特性及隔離級別
- MySQL 的四種事務隔離級別MySql
- 面試被吊打系列 - 事務隔離級別面試
- MySQL 事務的隔離級別初窺MySql
- CYmysql事務隔離級別詳情dbzMySql