| 問題 | 說明 | 發生情境 |
|---|---|---|
| 遺失更新 | 兩筆交易讀取同一資料，後寫入者覆蓋先寫入者的修改 | 無鎖定、最後寫入勝出 |
| 髒讀 | 讀取到其他交易未提交的修改資料 | 隔離等級 Read Uncommitted |
| 不可重複讀 | 同一交易多次讀取同一資料，結果不一致 (他人已提交修改) | 隔離等級 Read Committed |
| 幻讀 | 同一交易多次範圍查詢，結果筆數不同 (他人插入/刪除) | 隔離等級 Repeatable Read |
| 隔離等級 | 髒讀 | 不可重複讀 | 幻讀 | 並發度 | 備註 |
|---|---|---|---|---|---|
| Read Uncommitted | ✅ 發生 | ✅ 發生 | ✅ 發生 | 最高 | NOLOCK 提示等效 |
| Read Committed (預設) | ❌ 防止 | ✅ 發生 | ✅ 發生 | 高 | 共享鎖讀取後立即釋放 |
| Repeatable Read | ❌ 防止 | ❌ 防止 | ✅ 發生 | 中 | 共享鎖持有至交易結束 |
| Serializable | ❌ 防止 | ❌ 防止 | ❌ 防止 | 最低 | 範圍鎖 (Key-Range Lock) |
-- 設定目前連接的預設隔離等級
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 驗證目前設定
DBCC USEROPTIONS;
-- 結果中 isolation level = repeatable read選項：READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE
-- 僅影響此查詢，不影響連接其他陳述式
SELECT * FROM Authors WITH (NOLOCK); -- Read Uncommitted
SELECT * FROM Authors WITH (HOLDLOCK); -- Serializable (等同 SERIALIZABLE)
SELECT * FROM Authors WITH (READPAST); -- 跳過已鎖定列
SELECT * FROM Authors WITH (UPDLOCK, HOLDLOCK);-- 更新鎖 + 持有至交易結束建議：僅在必要時使用表提示，優先調整連接層級隔離等級。
| API | 設定方式 |
|---|---|
| ADO | Connection.IsolationLevel = adXactReadCommitted / adXactRepeatableRead / adXactReadSerializable |
| OLE DB | ITransactionLocal::StartTransaction (isoLevel = ISOLATIONLEVEL_READCOMMITTED...) |
| ODBC | SQLSetConnectAttr (SQL_ATTR_TXN_ISOLATION, SQL_TXN_READ_COMMITTED...) |
隔離等級 Repeatable Read 或 Serializable 可防止遺失更新。但若兩交易使用單一 UPDATE 陳述式 (不依賴先前讀取值)，預設 Read Committed 即可避免遺失更新，因更新鎖 (U/X Lock) 會序列化寫入。
-- sp_who2 替代版：顯示阻塞鏈、等待資源、執行 SQL
SELECT
r.session_id AS SPID,
r.blocking_session_id AS BlockerSPID,
r.wait_type,
r.wait_time_ms,
r.wait_resource,
st.text AS SQLText,
DB_NAME(r.database_id) AS DBName
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0 OR r.session_id IN (
SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0
);LCK_M_S：等待共享鎖 (SELECT 被 UPDATE 阻塞)LCK_M_X：等待獨佔鎖 (UPDATE/DELETE 被其他寫入阻塞)LCK_M_U：等待更新鎖PAGEIOLATCH_*：I/O 瓶頸非鎖定問題1204 / 1222 記錄死鎖圖至錯誤日誌。xml_deadlock_report (SQL 2008+)。SET LOCK_TIMEOUT 5000 避免無限等待 (單位：毫秒)。啟用資料庫選項 ALLOW_SNAPSHOT_ISOLATION 與 READ_COMMITTED_SNAPSHOT，以版本儲存 (row versioning in tempdb) 實現「讀不阻寫、寫不阻讀」，大幅提升高並發系統吞吐量。代價：tempdb 壓力增加、更新衝突需應用層處理 (錯誤 3960)。
