Bagikan melalui

Durabilitas Transaksi Kontrol

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Penerapan transaksi SQL Server dapat sepenuhnya tahan lama, default SQL Server, atau tahan lama tertunda (juga dikenal sebagai penerapan malas).

Penerapan transaksi yang sepenuhnya tahan lama sinkron dan melaporkan penerapan sebagai sukses dan mengembalikan kontrol kepada klien hanya setelah baris log untuk transaksi ditulis ke disk. Penerapan transaksi yang sepenuhnya tahan lama asinkron dan melaporkan penerapan sebagai sukses sebelum baris log untuk transaksi ditulis ke disk. Menulis entri log transaksi ke disk diperlukan agar transaksi tahan lama. Transaksi tahan lama yang tertunda menjadi tahan lama ketika entri log transaksi dibuang ke disk.

Artikel ini merinci transaksi tahan lama yang tertunda.

Durabilitas Transaksi Penuh vs. Tertunda

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.

Durabilitas transaksi yang tertunda mengurangi latensi karena I/O log dengan menyimpan catatan log transaksi dalam memori dan menulis ke log transaksi dalam batch, 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 penerapan transaksi berhasil, perubahan yang dilakukan oleh transaksi terlihat 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 penerapan transaksi berhasil dan mengembalikan kontrol ke klien.

Durabilitas transaksi 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 ketidakcocokan dalam sistem karena:

  • Pemrosesan penerapan transaksi tidak menunggu IO log selesai dan mengembalikan kontrol ke klien.

  • Transaksi bersamaan cenderung tidak bersaing untuk IO log; sebagai gantinya, buffer log dapat dibersihkan ke disk dalam gugus yang lebih besar, mengurangi pertikaian, 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 ketidakcocokan yang tinggi.
Jika sistem Anda memiliki beban kerja dengan tingkat ketidakcocokan tinggi, banyak waktu hilang menunggu kunci dirilis. Durabilitas transaksi yang tertunda mengurangi waktu penerapan dan dengan demikian melepaskan kunci lebih cepat, yang menghasilkan throughput yang lebih tinggi.

Jaminan Durabilitas Transaksi Tertunda

  • Setelah penerapan transaksi berhasil, perubahan yang dilakukan oleh transaksi terlihat oleh transaksi lain dalam sistem.

  • Durabilitas transaksi dijamin hanya setelah flush log transaksi dalam memori ke disk. Log transaksi dalam memori dihapus ke disk ketika:

    • Transaksi yang sepenuhnya tahan lama dalam database yang sama membuat perubahan dalam database dan berhasil diterapkan.

    • Pengguna berhasil menjalankan prosedur sp_flush_log tersimpan sistem.

      Jika transaksi yang sepenuhnya tahan lama atau sp_flush_log berhasil dilakukan, semua transaksi durabilitas yang tertunda sebelumnya dijamin telah dibuat 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 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 tertunda dengan ALTER DATABASE.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

CACAT
[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 PENERAPAN untuk informasi selengkapnya.

DIPAKSA
Dengan pengaturan ini, setiap transaksi yang diterapkan pada database tertunda tahan lama. 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 atom - Prosedur Tersimpan yang Dikompilasi Secara Asli

Kode berikut masuk ke dalam blok atom.

DELAYED_DURABILITY = { OFF | ON }

TIDAK AKTIF
[default] Transaksi sepenuhnya tahan lama, kecuali opsi database DELAYED_DURABLITY = FORCED berlaku, dalam hal ini penerapan tidak sinkron dan dengan demikian tertunda tahan lama. Untuk informasi selengkapnya, lihat Kontrol tingkat database.

AKTIF
Transaksi tertunda tahan lama, kecuali opsi database DELAYED_DURABLITY = DISABLED berlaku, dalam hal ini penerapannya sinkron dan dengan demikian sepenuhnya 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 durabilitas blok atom Tidak ada transaksi yang ada Transaksi dalam proses (tahan lama atau penuh atau tertunda)
DELAYED_DURABILITY = NONAKTIF Blok atom memulai transaksi baru yang sepenuhnya tahan lama. 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] COMMIT transaksi sepenuhnya tahan lama, kecuali opsi database DELAYED_DURABLITY = FORCED berlaku, dalam hal ini COMMIT tidak sinkron dan dengan demikian tertunda tahan lama. Untuk informasi selengkapnya, lihat Kontrol tingkat database.

AKTIF
COMMIT transaksi tertunda tahan lama, kecuali opsi database DELAYED_DURABLITY = DISABLED berlaku, dalam hal ini COMMIT sinkron dan dengan demikian sepenuhnya tahan lama. Untuk informasi selengkapnya, lihat Kontrol tingkat database.

Ringkasan opsi dan interaksinya

Tabel ini meringkas interaksi antara pengaturan durabilitas tertunda tingkat database dan pengaturan tingkat penerapan. Pengaturan tingkat database selalu diutamakan daripada pengaturan tingkat penerapan.

Pengaturan PENERAPAN/Pengaturan database DELAYED_DURABILITY = DINONAKTIFKAN DELAYED_DURABILITY = DIIZINKAN DELAYED_DURABILITY = DIPAKSA
DELAYED_DURABILITY = TRANSAKSI tingkat DATABASE OFF . Transaksi sepenuhnya tahan lama. Transaksi sepenuhnya tahan lama. Transaksi tertunda tahan lama.
DELAYED_DURABILITY = Transaksi tingkat Database ON . Transaksi sepenuhnya tahan lama. Transaksi tertunda tahan lama. Transaksi tertunda tahan lama.
DELAYED_DURABILITY = OFF Database silang atau transaksi terdistribusi. Transaksi sepenuhnya tahan lama. Transaksi sepenuhnya tahan lama. Transaksi sepenuhnya tahan lama.
DELAYED_DURABILITY = ON Cross database atau transaksi terdistribusi. Transaksi sepenuhnya tahan lama. Transaksi sepenuhnya tahan lama. Transaksi sepenuhnya tahan lama.

Cara memaksa flush log transaksi

Ada dua cara untuk memaksa flush log transaksi ke disk.

  • Jalankan transaksi yang sepenuhnya tahan lama 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 pembilasan catatan log dari semua transaksi tahan lama yang diterapkan 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 set jika 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 enabled jika Anda mencoba mengaktifkan durabilitas tertunda pada database yang dikonfigurasi dengan Replikasi Transaksional atau Ubah Pengambilan Data.

Pemulihan crash
Konsistensi dijamin, tetapi beberapa perubahan dari transaksi tahan lama yang tertunda yang telah berkomitmen mungkin hilang.

Database silang dan DTC
Jika transaksi lintas database atau terdistribusi, transaksi sepenuhnya tahan lama, terlepas dari pengaturan penerapan database atau transaksi apa pun.

Grup Ketersediaan dan Pencerminan AlwaysOn
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 penerapan, kontrol dikembalikan ke klien sebelum pengakuan diterima dari sekunder sinkron apa pun. Replikasi ke replika sekunder memang terus terjadi saat flush ke disk pada primer terjadi.

Pengklusteran failover
Beberapa penulisan transaksi tahan lama yang tertunda mungkin hilang.

Azure Synapse Link untuk SQL
Transaksi tahan lama yang tertunda tidak didukung dengan Azure Synapse Link untuk SQL.

Log pengiriman
Hanya transaksi yang telah dibuat tahan lama yang disertakan dalam log yang dikirimkan.

Pencadangan Log Transaksi
Hanya transaksi yang telah dibuat tahan lama yang disertakan dalam cadangan.

Kapan saya dapat 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 crash server, Anda akan kehilangan data untuk semua transaksi yang diterapkan yang belum disimpan ke disk. 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 memerah setiap kali menjadi penuh, tetapi itu sulit diprediksi dan tidak mungkin untuk dikontrol.

Matikan dan mulai ulang SQL Server

Untuk durabilitas tertunda, tidak ada perbedaan antara pematian tak terduga dan pematian/mulai ulang SQL Server yang diharapkan. 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 seolah-olah matikan/hidupkan ulang, baik yang direncanakan atau tidak direncanakan, kehilangan data yang sama dengan peristiwa bencana.

Langkah berikutnya