Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Komitmen transaksi SQL Server dapat berkelanjutan penuh, yang merupakan default SQL Server, atau berkelanjutan tertunda (juga dikenal sebagai lazy commit).
Komitmen transaksi yang sepenuhnya tahan lama adalah sinkron dan melaporkan komitmen sebagai berhasil serta mengembalikan kontrol kepada klien hanya setelah catatan log untuk transaksi ditulis ke dalam disk. Komitmen transaksi tahan lama yang ditunda bersifat asinkron dan melaporkan komit sebagai berhasil sebelum catatan log untuk transaksi ditulis ke disk. Menulis entri log transaksi ke disk diperlukan agar transaksi tahan lama. Transaksi tahan lama yang tertunda menjadi permanen ketika entri log transaksi dikeluarkan ke disk.
Artikel ini merinci transaksi tahan lama yang tertunda.
Durabilitas Transaksi Penuh vs. Ditunda
Durabilitas transaksi penuh dan tertunda memiliki kelebihan dan kekurangan. Aplikasi dapat memiliki campuran transaksi tahan lama sepenuhnya dan tertunda. Anda harus mempertimbangkan kebutuhan bisnis Anda dengan hati-hati dan bagaimana masing-masing sesuai dengan kebutuhan tersebut.
Durabilitas transaksi penuh
Transaksi yang sepenuhnya tahan lama menulis log transaksi ke disk sebelum mengembalikan kontrol ke klien. Anda harus menggunakan transaksi yang sepenuhnya tahan lama setiap kali:
Sistem Anda tidak dapat mentolerir kehilangan data apa pun. Lihat bagian Kapan saya bisa kehilangan data untuk informasi tentang kapan Anda bisa kehilangan beberapa data Anda.
Hambatan ini bukan karena latensi penulisan log transaksi.
Ketahanan transaksi yang tertunda mengurangi latensi yang diakibatkan oleh I/O log dengan menjaga catatan log transaksi tetap berada dalam memori dan menulis ke log transaksi secara bertahap, sehingga membutuhkan lebih sedikit operasi I/O. Durabilitas transaksi yang tertunda berpotensi mengurangi ketidakcocokan I/O log, sehingga mengurangi penantian dalam sistem.
Jaminan Durabilitas Transaksi Penuh
Setelah komit transaksi berhasil, perubahan yang dilakukan oleh transaksi dapat dilihat oleh transaksi lain dalam sistem. Untuk informasi selengkapnya tentang tingkat isolasi transaksi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL) atau Transaksi dengan Tabel yang Dioptimalkan Memori.
Durabilitas dijamin pada penerapan. Rekaman log yang sesuai disimpan ke disk sebelum komitmen transaksi berhasil dan kontrol dikembalikan kepada klien.
Ketahanan transaksi yang tertunda
Durabilitas transaksi yang tertunda dicapai menggunakan penulisan log asinkron ke disk. Rekaman log transaksi disimpan dalam buffer dan ditulis ke disk ketika buffer mengisi atau peristiwa pembilasan buffer terjadi. Durabilitas transaksi yang tertunda mengurangi latensi dan persaingan dalam sistem karena:
Pemrosesan penyelesaian transaksi tidak menunggu IO log selesai dan kontrol dikembalikan ke klien.
Transaksi yang berlangsung bersamaan waktu cenderung tidak bersaing dalam IO log; sebagai gantinya, buffer log dapat dibersihkan ke disk dalam bagian yang lebih besar, mengurangi kompetisi, dan meningkatkan throughput.
Catatan
Anda mungkin masih mengalami ketidakcocokan I/O log jika ada konkurensi tingkat tinggi, terutama jika Anda mengisi buffer log lebih cepat daripada Anda membersihkannya.
Kapan menggunakan durabilitas transaksi tertunda
Beberapa kasus di mana Anda dapat memperoleh manfaat dari penggunaan durabilitas transaksi yang tertunda adalah:
Anda dapat mentolerir beberapa kehilangan data.
Jika Anda dapat mentolerir beberapa kehilangan data, misalnya, di mana catatan individual tidak penting selama Anda memiliki sebagian besar data, durabilitas yang tertunda mungkin layak dipertimbangkan. Jika Anda tidak dapat mentolerir kehilangan data apa pun, jangan gunakan durabilitas transaksi yang tertunda.
Anda mengalami hambatan pada penulisan log transaksi.
Jika masalah performa Anda disebabkan oleh latensi dalam penulisan log transaksi, aplikasi Anda kemungkinan akan mendapat manfaat dari penggunaan durabilitas transaksi yang tertunda.
Beban kerja Anda memiliki tingkat persaingan yang tinggi.
Jika sistem Anda memiliki beban kerja dengan tingkat kontensi tinggi, banyak waktu terbuang menunggu kunci pelepasan. Durabilitas transaksi yang tertunda mengurangi waktu penerapan dan dengan demikian melepaskan kunci lebih cepat, yang menghasilkan throughput yang lebih tinggi.
Jaminan Ketahanan Transaksi Ditunda
Setelah komit transaksi berhasil, perubahan yang dilakukan oleh transaksi dapat dilihat oleh transaksi lain dalam sistem.
Durabilitas transaksi dijamin hanya setelah pembersihan log transaksi dalam memori ke disk. Log transaksi dalam memori dihapus ke disk ketika:
Transaksi yang sepenuhnya terjamin dalam database yang sama membuat perubahan dalam database dan berhasil di-commit.
Pengguna berhasil menjalankan prosedur sistem tersimpan
sp_flush_log.Jika transaksi yang sepenuhnya bersifat tahan lama atau sp_flush_log berhasil dikomit, semua transaksi dengan durabilitas tertunda sebelumnya dijamin sudah menjadi tahan lama.
SQL Server memang mencoba untuk menghapus log ke disk baik berdasarkan pembuatan log maupun waktu, bahkan jika semua transaksi tertunda tahan lama. Ini biasanya berhasil jika perangkat IO dapat mengikuti. Namun, SQL Server tidak memberikan jaminan durabilitas keras selain transaksi tahan lama dan sp_flush_log.
Cara mengontrol durabilitas transaksi
Kontrol tingkat database
Anda, DBA, dapat mengontrol apakah pengguna dapat menggunakan durabilitas transaksi yang tertunda pada database dengan pernyataan berikut. Anda harus mengatur pengaturan durabilitas yang tertunda dengan ALTER DATABASE.
ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }
DINONAKTIFKAN
[default] Dengan pengaturan ini, semua transaksi yang diterapkan pada database sepenuhnya tahan lama, terlepas dari pengaturan tingkat penerapan (DELAYED_DURABILITY=[ON | NONAKTIF]). Tidak perlu perubahan prosedur tersimpan dan kompilasi ulang. Ini memungkinkan Anda untuk memastikan bahwa tidak ada data yang pernah berisiko oleh durabilitas yang tertunda.
DIPERBOLEHKAN
Dengan pengaturan ini, durabilitas setiap transaksi ditentukan pada tingkat transaksi - DELAYED_DURABILITY = { OFF | PADA }. Lihat kontrol tingkat blok atom - Prosedur Tersimpan yang Dikompilasi Secara Asli dan kontrol tingkat COMMIT untuk informasi selengkapnya.
DIPAKSA
Dengan pengaturan ini, setiap transaksi yang mengikat pada database bersifat tertunda dalam durabilitasnya. Apakah transaksi menentukan sepenuhnya tahan lama (DELAYED_DURABILITY = OFF) atau tidak membuat spesifikasi, transaksi tertunda tahan lama. Pengaturan ini berguna ketika durabilitas transaksi yang tertunda berguna untuk database dan Anda tidak ingin mengubah kode aplikasi apa pun.
Kontrol tingkat blok atomik - Prosedur Tersimpan yang Dikompilasi Secara Lokal
Kode berikut masuk ke dalam blok atom.
DELAYED_DURABILITY = { OFF | ON }
TIDAK AKTIF
[default] Transaksi sepenuhnya tahan lama, kecuali jika opsi database DELAYED_DURABILITY = FORCED berlaku, dalam hal ini komitmen dilakukan secara asinkron dan dengan demikian ketahanannya tertunda. Untuk informasi selengkapnya, lihat Kontrol tingkat database.
AKTIF
Transaksi tertunda secara berkelanjutan, kecuali opsi database DELAYED_DURABILITY = DISABLED berlaku, dalam hal ini, commit dilakukan secara sinkron dan oleh karena itu sepenuhnya bersifat tahan lama. Untuk informasi selengkapnya, lihat Kontrol tingkat database.
Contoh Kode:
CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'
)
/* procedure body goes here */
END
Tabel 1: Durabilitas dalam Blok Atom
| Opsi ketahanan blok atom | Tidak ada transaksi yang ada | Transaksi dalam proses (sepenuhnya atau bertahan lama tertunda) |
|---|---|---|
| DELAYED_DURABILITY = NONAKTIF | Blok atom memulai transaksi baru yang sepenuhnya terjamin. | Blok atom membuat titik penyimpanan dalam transaksi yang ada, lalu memulai transaksi baru. |
| DELAYED_DURABILITY = AKTIF | Blok atom memulai transaksi tahan lama baru yang tertunda. | Blok atom membuat titik penyimpanan dalam transaksi yang ada, lalu memulai transaksi baru. |
Kontrol tingkat COMMIT -Transact-SQL
Sintaks COMMIT diperluas sehingga Anda dapat memaksa durabilitas transaksi yang tertunda. Jika DELAYED_DURABILITY DINONAKTIFKAN atau DIPAKSAKAN pada tingkat database (lihat di atas) opsi COMMIT ini diabaikan.
COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
TIDAK AKTIF
[default] Transaksi COMMIT sepenuhnya andal, kecuali jika opsi database DELAYED_DURABILITY = FORCED berlaku, dalam hal ini COMMIT bersifat asinkron dan tertunda namun tetap andal. Untuk informasi selengkapnya, lihat Kontrol tingkat database.
AKTIF
Transaksi COMMIT tertunda dalam hal ketahanan, kecuali jika opsi database DELAYED_DURABILITY = DISABLED berlaku, di mana COMMIT menjadi sinkron dan dengan demikian sepenuhnya terjamin ketahanannya. Untuk informasi selengkapnya, lihat Kontrol tingkat database.
Ringkasan opsi dan interaksinya
Tabel ini meringkas interaksi antara pengaturan ketahanan tertunda tingkat database dan pengaturan tingkat komit. Pengaturan tingkat database selalu diutamakan daripada pengaturan tingkat penerapan.
| Pengaturan KOMIT/Pengaturan database | DELAYED_DURABILITY = DINONAKTIFKAN | DELAYED_DURABILITY = DIIZINKAN | DELAYED_DURABILITY = DIPAKSAKAN |
|---|---|---|---|
| DELAYED_DURABILITY = OFF Transaksi di tingkat basis data. | Transaksi sepenuhnya tahan lama. | Transaksi sepenuhnya tahan lama. | Transaksi tertunda untuk waktu yang lama. |
| DELAYED_DURABILITY = ON pada transaksi tingkat database. | Transaksi sepenuhnya tahan lama. | Transaksi tertunda untuk waktu yang lama. | Transaksi tertunda untuk waktu yang lama. |
| DELAYED_DURABILITY = OFF Database silang atau transaksi terdistribusi. | Transaksi sepenuhnya tahan lama. | Transaksi sepenuhnya tahan lama. | Transaksi sepenuhnya tahan lama. |
| DELAYED_DURABILITY = ON Transaksi lintas database atau terdistribusi. | Transaksi sepenuhnya tahan lama. | Transaksi sepenuhnya tahan lama. | Transaksi sepenuhnya tahan lama. |
Cara memaksa log transaksi untuk di-flush
Ada dua cara untuk memaksa mengosongkan log transaksi ke disk.
Jalankan transaksi berkelanjutan sepenuhnya yang mengubah database yang sama. Ini memaksa pembilasan catatan log dari semua transaksi durabilitas tertunda yang dilakukan sebelumnya ke disk.
Jalankan prosedur
sp_flush_logtersimpan sistem . Prosedur ini memaksa pemindahan catatan log dari semua transaksi bertahan lama yang ditunda dan sudah dikomit sebelumnya ke disk. Untuk informasi selengkapnya, lihat sys.sp_flush_log (Transact-SQL).
Durabilitas tertunda dan fitur SQL Server lainnya
Replikasi Transaksional, Pelacakan Perubahan, dan Ubah Pengambilan Data
Untuk database yang diaktifkan untuk Replikasi Transaksional atau Change Data Capture (CDC), penggunaan durabilitas tertunda tidak didukung.
Pelacakan Perubahan dengan Durabilitas Tertunda didukung. Semua transaksi dengan Pelacakan Perubahan sepenuhnya tahan lama. Transaksi memiliki properti pelacakan perubahan jika melakukan operasi tulis ke tabel yang telah mengaktifkan pelacakan perubahan.
Dimulai dengan SQL Server 2022 CU 2 dan SQL Server 2019 CU 20, Anda mungkin melihat:
Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability setjika Anda mencoba mengaktifkan Replikasi Transaksional atau Mengubah Pengambilan Data pada database yang telah mengaktifkan durabilitas tertunda.Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabledjika Anda mencoba mengaktifkan durabilitas tertunda pada database yang dikonfigurasi dengan Replikasi Transaksional atau Ubah Pengambilan Data.
Pemulihan Kerusakan
Konsistensi dijamin, tetapi beberapa perubahan dari transaksi tahan lama yang tertunda dan telah berkomitmen mungkin hilang.
Pangkalan Data Silang dan DTC
Jika transaksi lintas database atau terdistribusi, transaksi sepenuhnya tahan lama, terlepas dari pengaturan penerapan database atau transaksi apa pun.
Grup Ketersediaan Always On dan Pencerminan
Transaksi tahan lama yang tertunda tidak menjamin durabilitas apa pun baik pada primer atau sekunder mana pun. Selain itu, mereka tidak menjamin pengetahuan apa pun tentang transaksi di sekunder. Setelah komit, kontrol dikembalikan ke klien sebelum pengakuan diterima dari sekunder sinkron manapun. Replikasi ke replika sekunder memang terus terjadi saat proses flush ke disk pada primer berlangsung.
Pengklusteran Cadangan Otomatis
Beberapa penulisan transaksi persisten yang tertunda mungkin hilang.
Azure Synapse Link untuk SQL
Transaksi tahan lama yang tertunda tidak didukung dengan Azure Synapse Link untuk SQL.
Pengiriman Log
Hanya transaksi yang telah dibuat permanen yang disertakan dalam log yang dikirimkan.
Pencadangan Log Transaksi
Hanya transaksi yang telah dibuat tahan lama yang disertakan dalam cadangan.
Kapan saya bisa kehilangan data
Jika Anda menerapkan durabilitas tertunda pada salah satu tabel Anda, Anda harus memahami bahwa keadaan tertentu dapat menyebabkan kehilangan data. Jika Anda tidak dapat mentolerir kehilangan data apa pun, Anda tidak boleh menggunakan durabilitas tertunda pada tabel Anda.
Peristiwa bencana
Dalam kasus peristiwa bencana, seperti kerusakan server, Anda akan kehilangan data untuk semua transaksi yang diselesaikan yang belum disimpan ke cakram. Transaksi tahan lama yang tertunda disimpan ke disk setiap kali transaksi yang sepenuhnya tahan lama dijalankan terhadap tabel apa pun (tahan lama memori dioptimalkan atau berbasis disk) dalam database, atau sp_flush_log dipanggil. Jika Anda menggunakan transaksi tahan lama yang tertunda, Anda mungkin ingin membuat tabel kecil dalam database yang dapat Anda perbarui atau panggil sp_flush_log secara berkala untuk menyimpan semua transaksi berkomitmen yang terutang. Log transaksi juga dibersihkan setiap kali menjadi penuh, tetapi itu sulit diprediksi dan tidak dapat dikendalikan.
Matikan dan mulai ulang SQL Server
Untuk durabilitas tertunda, tidak ada perbedaan antara pematian yang tidak terduga dan pematian atau mulai ulang SQL Server yang terencana. Seperti peristiwa bencana, Anda harus merencanakan kehilangan data. Dalam pematian/mulai ulang yang direncanakan, beberapa transaksi yang belum ditulis ke disk mungkin disimpan ke disk sebelum dimatikan, tetapi Anda tidak boleh merencanakannya. Rencanakan seandainya penghentian/penyalaan ulang, baik yang direncanakan maupun tidak direncanakan, kehilangan data sama seperti peristiwa bencana.