Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Aurora Tingkat isolasi saya SQL
Pelajari bagaimana instans DB di Aurora SQL My cluster mengimplementasikan properti database isolasi. Topik ini menjelaskan bagaimana Aurora Perilaku SQL default saya menyeimbangkan antara konsistensi yang ketat dan kinerja tinggi. Anda dapat menggunakan informasi ini untuk membantu memutuskan kapan akan mengubah pengaturan default berdasarkan karakteristik beban kerja Anda.
Tingkat isolasi yang tersedia untuk instans penulis
Anda dapat menggunakan tingkat isolasiREPEATABLE READ,, READ COMMITTEDREAD UNCOMMITTED, dan SERIALIZABLE pada instance utama cluster Aurora My SQL DB. Tingkat isolasi ini bekerja sama di Aurora My SQL seperti di RDS Aurora My. SQL
REPEATABLEREADtingkat isolasi untuk instance pembaca
Secara default, instans Aurora My SQL DB yang dikonfigurasi sebagai Replika Aurora hanya-baca selalu menggunakan tingkat isolasi. REPEATABLE
READ Instans DB ini mengabaikan pernyataan SET TRANSACTION ISOLATION LEVEL dan terus menggunakan tingkat isolasi REPEATABLE READ.
Anda tidak dapat mengatur tingkat isolasi untuk instans DB pembaca menggunakan parameter DB atau parameter klaster DB.
READCOMMITTEDtingkat isolasi untuk instance pembaca
Jika aplikasi Anda mencakup beban kerja sarat penulisan pada instans primer dan kueri yang berjalan lama pada Replika Aurora, Anda mungkin mengalami lag pembuangan yang substansial. Lag pembuangan terjadi jika pengumpulan sampah internal diblokir oleh kueri yang berjalan lama. Gejala yang Anda lihat adalah nilai yang tinggi untuk history list length dalam output dari perintah SHOW ENGINE INNODB STATUS. Anda dapat memantau nilai ini menggunakan RollbackSegmentHistoryListLength metrik di CloudWatch. Lag pembuangan yang substansial dapat mengurangi efektivitas indeks sekunder, menurunkan performa kueri secara keseluruhan, dan menyebabkan pemborosan ruang penyimpanan.
Jika Anda mengalami masalah seperti itu, Anda dapat mengatur pengaturan konfigurasi SQL tingkat sesi Aurora Saya,aurora_read_replica_read_committed, untuk menggunakan tingkat isolasi pada Replika Aurora. READ COMMITTED Saat menerapkan pengaturan ini, Anda dapat membantu mengurangi pelambatan dan ruang terbuang yang dapat timbul karena melakukan kueri berjalan lama pada saat yang sama seperti transaksi yang mengubah tabel Anda.
Sebaiknya pastikan Anda memahami SQL perilaku Aurora My yang spesifik dari READ COMMITTED isolasi sebelum menggunakan pengaturan ini. READ COMMITTEDPerilaku Replika Aurora sesuai dengan standar. ANSI SQL Namun, keterasingannya kurang ketat daripada SQL READ COMMITTED perilaku-Ku yang khas yang mungkin engkau kenal. Oleh karena itu, Anda mungkin melihat hasil kueri yang berbeda READ COMMITTED di bawah pada replika SQL baca Aurora My daripada yang mungkin Anda lihat untuk kueri yang sama di bawah READ COMMITTED pada instance SQL utama Aurora My atau di untuk My. RDS SQL Anda dapat mempertimbangkan untuk menggunakan pengaturan aurora_read_replica_read_committed untuk kasus seperti laporan komprehensif yang memindai basis data yang sangat besar. Sebaliknya, Anda dapat menghindarinya untuk kueri pendek dengan set hasil kecil yang mementingkan presisi dan keterulangan.
Tingkat isolasi READ COMMITTED tidak tersedia untuk sesi dengan klaster sekunder dalam basis data global Aurora yang menggunakan fitur penerusan tulis. Untuk informasi tentang penerusan tulis, lihat Menggunakan penerusan menulis dalam basis data global Amazon Aurora.
Menggunakan READ COMMITTED untuk pembaca
Untuk menggunakan tingkat isolasi READ COMMITTED untuk Replika Aurora, atur pengaturan konfigurasi aurora_read_replica_read_committed ke ON. Gunakan pengaturan ini di tingkat sesi saat terhubung ke Replika Aurora tertentu. Untuk melakukannya, jalankan SQL perintah berikut.
set session aurora_read_replica_read_committed = ON; set session transaction isolation level read committed;
Anda dapat menggunakan pengaturan konfigurasi ini sementara waktu untuk melakukan kueri interaktif satu kali. Anda mungkin juga ingin menjalankan aplikasi pelaporan atau analisis data yang mendapatkan manfaat dari tingkat isolasi READ COMMITTED, sementara membiarkan pengaturan default tidak berubah untuk aplikasi lain.
Saat pengaturan aurora_read_replica_read_committed diaktifkan, gunakan perintah SET TRANSACTION ISOLATION
LEVEL untuk menentukan tingkat isolasi untuk transaksi yang sesuai.
set transaction isolation level read committed;
Perbedaan READ COMMITTED perilaku pada replika Aurora
Pengaturan aurora_read_replica_read_committed membuat tingkat isolasi READ COMMITTED tersedia untuk Replika Aurora, dengan perilaku konsistensi yang dioptimalkan untuk transaksi yang berjalan lama. Tingkat isolasi READ
COMMITTED pada Replika Aurora memiliki isolasi yang tidak terlalu ketat dibandingkan pada instans primer Aurora. Oleh karena itu, aktifkan pengaturan ini hanya di Replika Aurora, yang memungkinkan Anda mengetahui bahwa kueri Anda dapat menerima kemungkinan jenis hasil tertentu yang tidak konsisten.
Kueri Anda dapat mengalami beberapa jenis anomali baca ketika pengaturan aurora_read_replica_read_committed diaktifkan. Dua jenis anomali ini sangat penting untuk dipahami dan ditangani dalam kode aplikasi Anda. Pembacaan yang tidak dapat diulang terjadi saat transaksi lain di-commit saat kueri Anda sedang berjalan. Kueri yang berjalan lama dapat melihat data yang berbeda pada awal kueri dibandingkan dengan yang dilihat pada akhir kueri. Pembacaan phantom terjadi ketika transaksi lain menyebabkan baris yang ada disusun ulang saat kueri Anda berjalan, dan satu atau beberapa baris dibaca dua kali oleh kueri Anda.
Kueri Anda mungkin mengalami jumlah baris yang tidak konsisten sebagai hasil dari pembacaan phantom. Kueri Anda juga dapat memberikan hasil yang tidak lengkap atau tidak konsisten karena pembacaan yang tidak dapat diulang. Misalnya, misalkan operasi gabungan mengacu pada tabel yang secara bersamaan dimodifikasi oleh SQL pernyataan seperti INSERT atauDELETE. Dalam kasus ini, kueri join mungkin membaca baris dari satu tabel, tetapi bukan baris yang sesuai dari tabel lain.
ANSISQLStandar memungkinkan kedua perilaku ini untuk tingkat READ COMMITTED isolasi. Namun, perilaku tersebut berbeda dari SQL implementasi Saya pada umumnyaREAD COMMITTED. Jadi, sebelum mengaktifkan aurora_read_replica_read_committed pengaturan, periksa SQL kode yang ada untuk memverifikasi apakah itu beroperasi seperti yang diharapkan di bawah model konsistensi yang lebih longgar.
Jumlah baris dan hasil lainnya mungkin tidak terlalu konsisten berdasarkan tingkat isolasi READ COMMITTED sementara pengaturan ini diaktifkan. Dengan demikian, Anda biasanya mengaktifkan pengaturan hanya saat menjalankan kueri analitik yang mengumpulkan data dalam jumlah besar dan tidak memerlukan presisi absolut. Jika Anda tidak memiliki jenis kueri yang berjalan lama ini bersama dengan beban kerja rata penulisan, Anda mungkin tidak memerlukan pengaturan aurora_read_replica_read_committed. Tanpa kombinasi kueri yang berjalan lama dan beban kerja yang sarat penulisan, Anda cenderung tidak akan menghadapi masalah dengan panjang daftar riwayat.
contoh Kueri yang menunjukkan perilaku isolasi untuk READ COMMITTED di Aurora Replicas
Contoh berikut menunjukkan bagaimana kueri READ COMMITTED pada Replika Aurora dapat memberikan hasil yang tidak dapat diulang jika transaksi mengubah tabel terkait pada saat yang sama. Tabel BIG_TABLE berisi 1 juta baris sebelum kueri dimulai. Pernyataan bahasa manipulasi data lainnya (DML) menambahkan, menghapus, atau mengubah baris saat mereka berjalan.
Kueri pada instans primer Aurora berdasarkan tingkat isolasi READ COMMITTED memberikan hasil yang dapat diprediksi. Namun demikian, overhead dalam mempertahankan tampilan baca yang konsisten selama masa pakai setiap kueri yang berjalan lama dapat menyebabkan pengumpulan sampah yang menimbulkan biaya mahal pada lain waktu.
Kueri pada Replika Aurora berdasarkan tingkat isolasi READ COMMITTED akan dioptimalkan untuk meminimalkan overhead pengumpulan sampah. Komprominya adalah bahwa hasilnya mungkin berbeda-beda bergantung pada apakah kueri memuat baris yang ditambahkan, dihapus, atau disusun ulang oleh transaksi yang di-commit saat kueri berjalan. Kueri diizinkan untuk mempertimbangkan baris-baris ini, tetapi tidak diwajibkan. Untuk tujuan demonstrasi, kueri hanya memeriksa jumlah baris dalam tabel dengan menggunakan fungsi COUNT(*).
| Waktu | DMLpernyataan tentang contoh utama Aurora | Kueri pada instance utama Aurora dengan READ COMMITTED | Kueri pada Aurora Replica dengan 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 | Jika Q1 selesai sekarang, hasilnya adalah 1.000.000. | Jika Q2 selesai sekarang, hasilnya adalah 1.000.000 atau 1.000.001. | |
| T5 |
DELETE FROM big_table LIMIT 2; COMMIT;
|
||
| T6 | Jika Q1 selesai sekarang, hasilnya adalah 1.000.000. | Jika Q2 selesai sekarang, hasilnya adalah 1.000.000 atau 1.000.001 atau 999.999 atau 999.998. | |
| T7 |
UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
|
||
| T8 | Jika Q1 selesai sekarang, hasilnya adalah 1.000.000. | Jika Q2 selesai sekarang, hasilnya adalah 1.000.000 atau 1.000.001 atau 999.999, atau mungkin angka tertentu yang lebih tinggi. | |
| T9 | Q3: SELECT COUNT(*) FROM big_table; |
Q4: SELECT COUNT(*) FROM big_table; |
|
| T10 | Jika Q3 selesai sekarang, hasilnya adalah 999.999. | Jika Q4 selesai sekarang, hasilnya adalah 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 | Jika Q5 selesai sekarang, hasilnya adalah 0. | Jika Q6 selesai sekarang, hasilnya adalah 0 atau 1. |
Jika kueri selesai dengan cepat, sebelum transaksi lain melakukan DML pernyataan dan komit, hasilnya dapat diprediksi dan sama antara instance utama dan Replika Aurora. Mari kita periksa perbedaan perilaku secara mendetail, dimulai dengan kueri pertama.
Hasil untuk Q1 sangat dapat diprediksi, karena READ COMMITTED pada instans primer menggunakan model konsistensi kuat yang serupa dengan tingkat isolasi REPEATABLE READ.
Hasil untuk Q2 dapat bervariasi bergantung pada transaksi apa yang dilakukan saat kueri berjalan. Misalnya, misalkan transaksi lain melakukan DML pernyataan dan komit saat kueri sedang berjalan. Dalam hal ini, kueri di Replika Aurora dengan tingkat isolasi READ COMMITTED mungkin atau mungkin tidak memperhitungkan perubahan tersebut. Hitungan baris tidak dapat diprediksi dengan cara yang sama seperti berdasarkan tingkat isolasi REPEATABLE READ. Mereka juga tidak dapat diprediksi seperti kueri yang berjalan di bawah tingkat READ COMMITTED isolasi pada instance utama, atau pada SQL instance RDS Saya.
Pernyataan UPDATE di T7 tidak benar-benar mengubah jumlah baris dalam tabel. Namun, dengan mengubah panjang kolom panjang variabel, pernyataan ini dapat menyebabkan baris-baris tersebut disusun ulang secara internal. Transaksi READ COMMITTED yang berjalan lama dapat melihat baris versi lama, lalu dalam kueri yang sama, melihat versi baru dari baris yang sama. Kueri ini juga dapat melewati versi baris lama dan baru, sehingga jumlah baris mungkin berbeda dari yang diharapkan.
Hasil Q5 dan Q6 mungkin identik atau sedikit berbeda. Kueri Q6 pada Replika Aurora berdasarkan READ
COMMITTED dapat melihat, tetapi tidak wajib untuk melihat, baris baru yang di-commit saat kueri berjalan. Kueri ini juga dapat melihat baris dari satu tabel, tetapi tidak dari tabel lainnya. Jika kueri join tidak menemukan baris yang cocok di kedua tabel, kueri ini akan menghasilkan angka nol. Jika kueri ini menemukan baris baru di PARENT_TABLE dan CHILD_TABLE, kueri tersebut akan menampilkan satu angka. Dalam kueri yang berjalan lama, pencarian dari tabel gabungan dapat terjadi pada waktu yang terpisah secara luas.
catatan
Perbedaan perilaku ini bergantung pada waktu transaksi di-commit dan kueri memproses baris tabel yang mendasarinya. Dengan demikian, Anda kemungkinan besar akan melihat perbedaan dalam kueri laporan yang memakan waktu beberapa menit atau jam dan yang berjalan pada cluster Aurora OLTP memproses transaksi pada saat yang sama. Ini adalah jenis beban kerja campuran yang mendapatkan manfaat maksimal dari tingkat isolasi READ COMMITTED pada Replika Aurora.
