Aurora 我的SQL隔離等級 - Amazon Aurora
寫入器執行個體可用的隔離層級REPEATABLEREAD讀者執行個體的隔離層級READCOMMITTED讀者執行個體的隔離層級

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

Aurora 我的SQL隔離等級

了解 Aurora 我的SQL叢集中的資料庫執行個體如何實作隔離的資料庫屬性。本主題說明 Aurora 我的SQL預設行為如何在嚴格一致性和高效能之間取得平衡。您可以根據工作負載的特性，使用此資訊協助您決定何時變更預設設定。

寫入器執行個體可用的隔離層級

您可以在 Aurora My SQL DB 叢集的主要執行個體SERIALIZABLE上使用隔離層級REPEATABLE READREAD UNCOMMITTED、、和。READ COMMITTED這些隔離等級在 Aurora My SQL 中的工作方式與我RDS的相同SQL。

REPEATABLEREAD讀者執行個體的隔離層級

根據預設，設定為唯讀 Aurora 複本的 Aurora 我的SQL資料庫執行個體一律使用REPEATABLE READ隔離層級。這些資料庫執行個體忽略任何 SET TRANSACTION ISOLATION LEVEL 陳述式，並繼續使用 REPEATABLE READ 隔離層級。

您無法使用資料庫參數或資料庫叢集參數，設定讀取器資料庫執行個體的隔離層級。

READCOMMITTED讀者執行個體的隔離層級

如果應用程式在主要執行個體上有密集寫入的工作負載，而在 Aurora 複本上有長時間執行的查詢，您可能會感受到嚴重的清除延遲。如果長時間執行的查詢阻礙內部垃圾收集，此即所謂的清除延遲。您看見的徵狀是在 history list length 命令的輸出中，SHOW ENGINE INNODB STATUS 的值很高。您可以使用中的RollbackSegmentHistoryListLength度量來監視此值 CloudWatch。嚴重的清除延遲會降低次要索引的效率，導致整體查詢效能下降和儲存空間浪費。

如果您遇到此類問題，可以設定 Aurora My SQL 工作階段層級組態設定aurora_read_replica_read_committed，以在 Aurora 複本上使用READ COMMITTED隔離層級。在交易修改資料表的同時，長時間執行的查詢可能導致速度變慢和浪費空間，套用此設定有助於避免這種情形。

我們建議您在使用此設定之前，先確定您瞭解READ COMMITTED隔離的特定 Aurora My SQL 行為。Aurora 複本READ COMMITTED行為符合標ANSISQL準。但是，隔離比您可能熟悉的典型「我的SQLREAD COMMITTED行為」不太嚴格。因此，您可能會READ COMMITTED在 Aurora My 僅供SQL讀取複本下看到不同的查詢結果，與您在 Aurora My SQL 主執行個體或 My READ COMMITTED 上看到的相同查RDS詢結果不同SQL。在某些案例中，例如綜合報告掃描巨大的資料庫，您可以考慮使用 aurora_read_replica_read_committed 設定。反之，如果準確性和可重複性很重要，以較短的查詢來產生較小的結果集可避免此情況。

READ COMMITTED 隔離層級不適用於在使用寫入轉送功能的 Aurora 全域資料庫中次要叢集內的工作階段。如需寫入轉送的資訊，請參閱 在 Amazon Aurora 全域資料庫中使用寫入轉送。

使用READCOMMITTED於讀者

若要對 Aurora 複本使用 READ COMMITTED 隔離層級，請將 aurora_read_replica_read_committed 組態設定為 ON。請於連線至特定 Aurora 複本時，在工作階段層級使用此設定。若要這麼做，請執行下列SQL命令。

set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;

您可以暫時使用此組態設定，以執行互動的臨時性一次查詢。您也可以執行報告或資料分析應用程式來善用 READ COMMITTED 隔離層級，但對於其他應用程式維持預設設定不變。

啟用 aurora_read_replica_read_committed 設定時，請使用 SET TRANSACTION ISOLATION LEVEL 命令為適當的交易指定隔離層級。

set transaction isolation level read committed;

Aurora 複本上的READCOMMITTED行為差異

aurora_read_replica_read_committed 設定可讓 Aurora 複本使用 READ COMMITTED 隔離層級，其一致性行為最適合長時間執行的交易。Aurora 複本上的 READ COMMITTED 隔離層級，不像 Aurora 主要執行個體上的隔離那麼嚴格。因此，在 Aurora 複本上，只在您知道查詢能接受可能有某種不一致結果時，才應該啟用此設定。

啟用 aurora_read_replica_read_committed 設定時，查詢可能會遇到某種讀取異常狀況。在應用程式碼中，特別需要了解和處理兩種異常。當查詢執行時有另一個交易遞交，此即所謂的不可重複讀取。長時間執行的查詢在查詢開始和結束時所見的資料不同。當查詢執行時有其他交易導致現有的列重組，使得查詢讀取一或多列兩次，此即所謂的幻影讀取。

幻影讀取可能導致查詢看到不一致的列計數。查詢也可能因為不可重複讀取而傳回不完整或不一致的結果。例如，假設聯結作業參照由SQL陳述式 (例如INSERT或DELETE) 同時修改的資料表。在此情況下，聯結從一個資料表中讀取的列，可能不是另一個資料表中相應的列。

該ANSISQL標準允許READ COMMITTED隔離級別的這兩種行為。但是，這些行為與典型的 My SQL 實現不同READ COMMITTED。因此，在啟用aurora_read_replica_read_committed設定之前，請先檢查任何現有SQL程式碼，以確認其在較寬鬆的一致性模型下是否如預期般運作。

啟用此設定時，在 READ COMMITTED 隔離層級下，列計數和其他結果可能不那麼一致。因此，通常只在執行分析查詢來彙總大量資料，且不需要絕對精準時，您才啟用此設定。如果您沒有這種長時間執行的查詢，也沒有密集寫入的工作負載，可能就不需要 aurora_read_replica_read_committed 設定。如果既沒有長時間執行的查詢，也沒有寫入密集的工作負載，就不太可能遭遇歷史記錄清單過長的問題。

範例 顯示 Aurora 複本上隔離READCOMMITTED行為的查詢

下列範例顯示 Aurora 複本上的 READ COMMITTED 查詢在同時有交易修改相關聯資料表時，如何傳回不可重複的結果。在任何查詢開始前，資料表 BIG_TABLE 包含 1 百萬列。其他資料操作語言 (DML) 陳述式會在執行時新增、移除或變更資料列。

在 READ COMMITTED 隔離層級下，Aurora 主要執行個體上的查詢產生可預測的結果。但是，為了替每個長時間執行的查詢自始至終維持一致的讀取檢視，所付出的成本將導致後來垃圾收集的代價更高。

在 READ COMMITTED 隔離層級下，Aurora 複本上的查詢最能將此垃圾收集成本降到最低。根據查詢擷取的列是否為查詢執行當時遞交的交易所新增、移除或重組而定，結果可能不同，而這就是代價。查詢可以考慮這幾列，但並非必要。為了示範，查詢只使用 COUNT(*) 函數檢查資料表的列數。

時間 DML關於 Aurora 主實例的聲明 使用 Aurora 主執行個體查詢 READ COMMITTED 在 Aurora 副本上查詢 READ COMMITTED
T1 INSERT INTO big_table SELECT * FROM other_table LIMIT 1000000; COMMIT;
T2 Q1: SELECT COUNT(*) FROM big_table; Q2: SELECT COUNT(*) FROM big_table;
T3 INSERT INTO big_table (c1, c2) VALUES (1, 'one more row'); COMMIT;
T4 如果 Q1 現在完成，則結果為 1,000,000。 如果 Q2 現在完成，則結果為 1,000,000 或 1,000,001。
T5 DELETE FROM big_table LIMIT 2; COMMIT;
T6 如果 Q1 現在完成，則結果為 1,000,000。 如果 Q2 現在完成，則結果為 1,000,000 或 1,000,001 或 999,999 或 999,998。
T7 UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
T8 如果 Q1 現在完成，則結果為 1,000,000。 如果 Q2 現在完成，則結果為 1,000,000 或 1,000,001 或 999,999，或者，可能是某個更大的數字。
T9 Q3: SELECT COUNT(*) FROM big_table; Q4: SELECT COUNT(*) FROM big_table;
T10 如果 Q3 現在完成，則結果為 999,999。 如果 Q4 現在完成，則結果為 999,999。
T11 Q5: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000; Q6: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;
T12 INSERT INTO parent_table (id, s) VALUES (1000, 'hello'); INSERT INTO child_table (id, s) VALUES (1000, 'world'); COMMIT;
T13 如果 Q5 現在完成，則結果為 0。 如果 Q6 現在完成，則結果為 0 或 1。

如果查詢快速完成，則在任何其他交易執行DML陳述式和認可之前，主執行個體和 Aurora 複本之間的結果是可預測的，且結果相同。從第一個查詢開始詳細檢查行為的差異。

Q1 的結果很容易預測，因為主要執行個體上的 READ COMMITTED 使用類似於 REPEATABLE READ 隔離層級的強大一致性模式。

隨著查詢執行當時交易是遞交什麼而定，Q2 的結果可能不同。例如，假設其他交易在查詢執行時執行DML陳述式和認可。在此情況下，Aurora 複本上搭配 READ COMMITTED 隔離層級的查詢可能考量或不考量變更。列計數不能像在 REPEATABLE READ 隔離層級下那樣預測。它們也不像在主執行個RDS體或 My SQL 執行個體的READ COMMITTED隔離層級下執行的查詢那樣可預測。

T7 上的 UPDATE 陳述式實際上不變更資料表的列數。不過，此陳述式能夠變更可變長度欄的長度，導致列在內部重組。長時間執行的 READ COMMITTED 交易可能看到某一列的舊版本，而後來在相同查詢內，可能又看到那同一列的新版本。該查詢也可以同時略過此列的新舊版本，因此列數可能與預期不同。

Q5 和 Q6 的結果可能完全相同或稍微不同。Aurora 複本上在 READ COMMITTED 下的查詢 Q6 能夠看到 (但不必一定看到) 查詢執行當時遞交的新列。也可能看到只在一個資料表而不在另一個資料表中的列。如果聯結查詢在兩個資料表中找不到相符一列，則傳回計數為零。如果查詢在 PARENT_TABLE 和 CHILD_TABLE 中都找到新列，查詢傳回計數為 1。在長時間執行的查詢中，可能間隔很久才查閱聯結的資料表。

注意

這些行為差異取決於交易遞交的時間，以及查詢何時處理基礎資料表列。因此，您很可能會在報告查詢中看到這種差異，這些查詢需要花費數分鐘或數小時，並且在 Aurora 叢集上同時處理OLTP交易。Aurora 複本上的 READ COMMITTED 隔離層級最有益於這幾種混合工作負載。