Transaksi dengan tabel yang dioptimalkan untuk memori

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Artikel ini menjelaskan semua aspek transaksi yang khusus untuk tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli.

Tingkat isolasi transaksi di SQL Server berlaku secara berbeda untuk tabel yang dioptimalkan memori versus tabel berbasis disk, dan mekanisme yang mendasar berbeda. Pemahaman tentang perbedaan membantu programmer merancang sistem throughput tinggi. Tujuan integritas transaksi dibagikan dalam semua kasus.

Untuk kondisi kesalahan khusus untuk transaksi pada tabel yang dioptimalkan memori, lihat bagian Deteksi Konflik dan Logika Coba Lagi .

Untuk informasi umum, lihat SET TRANSACTION ISOLATION LEVEL.

Pesimis versus optimis

Perbedaan fungsi antara tabel yang dioptimalkan memori dan tabel berbasis disk berasal dari pendekatan pesimis versus optimis untuk integritas transaksi. Tabel yang dioptimalkan memori menggunakan pendekatan optimis:

Pendekatan pesimis menggunakan kunci untuk memblokir potensi konflik sebelum terjadi. Kunci diambil ketika pernyataan dijalankan, dan dirilis saat transaksi dilakukan.
Pendekatan optimis mendeteksi konflik saat terjadi, dan melakukan pemeriksaan validasi pada waktu penerapan.
- Kesalahan 1205, kebuntuan, tidak dapat terjadi untuk tabel yang dioptimalkan memori.

Pendekatan optimis memiliki overhead yang lebih sedikit dan biasanya lebih efisien, sebagian karena konflik transaksi jarang terjadi di sebagian besar aplikasi. Perbedaan fungsi utama antara pendekatan pesimis dan optimis adalah bahwa jika konflik terjadi, dalam pendekatan pesimis Anda menunggu, sementara dalam pendekatan optimis salah satu transaksi gagal dan perlu dicoba kembali oleh klien. Perbedaan fungsional lebih besar ketika tingkat isolasi REPEATABLE READ berlaku, dan paling besar untuk tingkat SERIALIZABLE.

Mode inisiasi transaksi

SQL Server menggunakan mode berikut untuk inisiasi transaksi:

Autocommit. Kueri sederhana atau pernyataan DML secara implisit membuka transaksi di awal, dan akhir pernyataan secara implisit melakukan transaksi. Autocommit adalah default.

Dalam mode autocommit, Anda biasanya tidak perlu menambahkan petunjuk tabel mengenai tingkat isolasi transaksi pada tabel yang dioptimalkan untuk memori dalam klausa FROM.
Eksplisit. Transact-SQL Anda berisi kode BEGIN TRANSACTION, bersama dengan COMMIT TRANSACTION yang nantinya akan digunakan. Anda dapat mengkoralkan dua pernyataan atau lebih ke dalam transaksi yang sama.

Dalam mode eksplisit, Anda harus menggunakan opsi database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT atau memberikan petunjuk tabel tentang tingkat isolasi transaksi dalam klausa FROM pada tabel yang dioptimalkan memori.
Implisit. Dimulai ketika SET IMPLICIT_TRANSACTION ON berlaku. Opsi ini secara implisit melakukan tindakan yang setara dengan secara eksplisit BEGIN TRANSACTION sebelum setiap perintah UPDATE jika @@TRANCOUNT adalah 0. Oleh karena itu, terserah kode T-SQL Anda untuk akhirnya mengeluarkan eksplisit COMMIT TRANSACTION.
Blok ATOMIC. Semua pernyataan dalam ATOMIC blok selalu berjalan sebagai bagian dari satu transaksi. Entah seluruh operasi dalam blok atom dikomitkan jika berhasil, atau semuanya dibatalkan jika terjadi kegagalan. Setiap prosedur tersimpan terkompilasi secara asli memerlukan blok ATOMIC.

Contoh kode dengan mode eksplisit

Skrip Transact-SQL yang ditafsirkan berikut menggunakan:

Transaksi eksplisit.
Tabel yang dioptimalkan untuk memori, bernama dbo.Order_mo.
Konteks READ COMMITTED tingkat isolasi transaksi.

Oleh karena itu, Anda perlu menambahkan petunjuk tabel pada tabel yang dioptimalkan memori. Petunjuknya harus untuk SNAPSHOT atau tingkat yang lebih mengisolasi. Dalam kasus contoh kode, petunjuknya adalah WITH (SNAPSHOT). Jika Anda menghapus petunjuk ini, skrip mengalami kesalahan 41368, di mana coba lagi otomatis tidak sesuai:

Kesalahan 41368

Mengakses tabel yang dioptimalkan untuk memori dengan menggunakan level isolasi READ COMMITTED hanya didukung untuk transaksi autocommit. Ini tidak didukung untuk transaksi eksplisit atau implisit. Berikan tingkat isolasi yang didukung untuk tabel yang dioptimalkan memori dengan menggunakan petunjuk tabel, seperti WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION; -- Explicit transaction.

-- Order_mo is a memory-optimized table.
SELECT *
FROM dbo.Order_mo AS o WITH (SNAPSHOT) -- Table hint.
     INNER JOIN dbo.Customer AS c
         ON c.CustomerId = o.CustomerId;

COMMIT TRANSACTION;

Anda dapat menghindari kebutuhan akan petunjuk WITH (SNAPSHOT) dengan menggunakan opsi database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. Ketika Anda mengatur opsi ini ke ON, akses ke tabel yang dioptimalkan memori di bawah tingkat isolasi yang lebih rendah secara otomatis ditingkatkan ke tingkat isolasi SNAPSHOT.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Penerapan versi baris

Tabel yang dioptimalkan untuk memori menggunakan sistem versi baris yang sangat canggih yang membuat pendekatan optimis efisien, bahkan pada tingkat isolasi yang paling ketat dari SERIALIZABLE. Untuk informasi selengkapnya, lihat Pengantar Tabel yang Dioptimalkan untuk Memori.

Tabel berbasis disk tidak langsung memiliki sistem versi baris ketika READ_COMMITTED_SNAPSHOT atau tingkat isolasi SNAPSHOT berlaku. Sistem ini didasarkan pada tempdb, sedangkan struktur data yang dioptimalkan untuk memori memiliki versi baris bawaan, untuk efisiensi maksimum.

Tingkat isolasi

Tabel berikut mencantumkan kemungkinan tingkat isolasi transaksi, secara berurutan dari isolasi paling sedikit ke sebagian besar. Untuk detail selengkapnya tentang konflik yang dapat terjadi dan logika percobaan ulang untuk menanganinya, lihat Deteksi Konflik dan Logika Percobaan Ulang.

Tingkat Isolasi	Deskripsi
`READ UNCOMMITTED`	Tidak dapat diakses: Anda tidak dapat mengakses tabel yang dioptimalkan untuk memori di bawah isolasi Baca Tanpa Komit. Anda masih dapat mengakses tabel yang dioptimalkan memori di bawah `SNAPSHOT` isolasi jika Anda mengatur tingkat `TRANSACTION ISOLATION LEVEL` sesi ke `READ UNCOMMITTED`, dengan menggunakan `WITH (SNAPSHOT)` petunjuk tabel atau mengatur pengaturan `MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT` database ke `ON`.
`READ COMMITTED`	Didukung hanya untuk tabel yang dioptimalkan untuk memori ketika mode autocommit diaktifkan. Anda masih dapat mengakses tabel yang dioptimalkan memori di bawah `SNAPSHOT` isolasi jika Anda mengatur tingkat `TRANSACTION ISOLATION LEVEL` sesi ke `READ COMMITTED`, dengan menggunakan `WITH (SNAPSHOT)` petunjuk tabel atau mengatur pengaturan `MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT` database ke `ON`. Jika Anda mengatur opsi database `READ_COMMITTED_SNAPSHOT` ke `ON`, Anda tidak dapat mengakses tabel yang dioptimalkan untuk memori dan berbasis disk dalam mode isolasi `READ COMMITTED` dalam pernyataan yang sama.
`SNAPSHOT`	Didukung untuk tabel yang dioptimalkan memori. Di dalam sistem `SNAPSHOT` adalah tingkat isolasi transaksi yang paling rendah tuntutannya untuk tabel yang dioptimalkan memori. `SNAPSHOT` menggunakan lebih sedikit sumber daya sistem daripada atau `REPEATABLE READSERIALIZABLE`.
`REPEATABLE READ`	Didukung untuk tabel yang dioptimalkan memori. Jaminan yang diberikan oleh `REPEATABLE READ` isolasi adalah bahwa, pada waktu penerapan, tidak ada transaksi bersamaan yang memperbarui salah satu baris yang dibaca oleh transaksi ini. Karena model optimis, transaksi yang bersamaan tidak dibatasi untuk memperbarui baris yang telah dibaca oleh transaksi ini. Sebaliknya, pada saat komit, transaksi ini memvalidasi bahwa `REPEATABLE READ` isolasi belum dilanggar. Jika ya, transaksi ini dibatalkan dan harus diulangi.
`SERIALIZABLE`	Didukung untuk tabel yang dioptimalkan memori. Bernama Serializable karena isolasi sangat ketat sehingga hampir seperti transaksi dijalankan secara berseri bukannya secara bersamaan.

Fase transaksi dan masa pakai

Saat tabel yang dioptimalkan memori terlibat, masa pakai transaksi melewati fase yang ditunjukkan pada gambar berikut:

Diagram memperlihatkan masa pakai transaksi dalam memori.

Deskripsi fase berikut.

Fase 1 dari 3: Pemrosesan reguler

Fase ini menjalankan semua kueri dan pernyataan DML dalam kueri.
Selama fase ini, pernyataan melihat versi tabel yang dioptimalkan memori pada waktu mulai logis transaksi.

Fase 2 dari 3: Validasi

Fase validasi dimulai dengan menetapkan waktu akhir, yang menandai transaksi sebagai selesai secara logis. Penyelesaian proses ini membuat semua perubahan dalam transaksi terlihat oleh transaksi lain yang bergantung pada transaksi ini. Transaksi dependen tidak dapat dikonfirmasi hingga transaksi ini berhasil dikonfirmasi. Selain itu, transaksi yang menyimpan dependensi tersebut tidak dapat mengembalikan tataan hasil ke klien, untuk memastikan klien hanya melihat data yang berhasil diterapkan ke database.
Fase ini terdiri dari bacaan berulang dan validasi yang dapat diserialisasikan. Untuk validasi baca yang dapat diulang, ini memeriksa apakah transaksi memperbarui salah satu baris yang dibacanya. Untuk validasi yang dapat diserialisasikan, ini memeriksa apakah transaksi menyisipkan baris ke dalam rentang data apa pun yang dipindainya. Berdasarkan tabel dalam Tingkat Isolasi dan Konflik, validasi repeatable read dan serializable dapat terjadi saat menggunakan isolasi snapshot, untuk memvalidasi konsistensi batasan kunci unik dan batasan kunci asing.

Fase 3 dari 3: Menerapkan pemrosesan

Selama fase penerapan, proses menulis perubahan pada tabel tahan lama ke log, dan proses menulis log ke disk. Kemudian, proses mengembalikan kontrol ke klien.
Setelah pemrosesan commit selesai, proses tersebut memberi tahu semua transaksi dependen bahwa mereka dapat melakukan commit.

Seperti biasa, jaga unit transaksi pekerjaan Anda seminimal dan sesingkat mungkin sesuai kebutuhan data Anda.

Deteksi konflik dan logika percobaan ulang

Dua jenis kondisi kesalahan terkait transaksi dapat menyebabkan transaksi gagal dan digulung balik. Dalam kebanyakan kasus, Anda perlu mencoba kembali transaksi setelah kegagalan seperti itu, mirip dengan ketika kebuntuan terjadi.

Konflik antara transaksi bersamaan. Konflik ini, termasuk konflik pembaruan dan kegagalan validasi, dapat terjadi karena pelanggaran tingkat isolasi transaksi atau pelanggaran batasan.
Kegagalan dependensi. Kegagalan ini diakibatkan oleh kegagalan transaksi yang bergantung pada Anda untuk diselesaikan, atau dari jumlah dependensi yang tumbuh terlalu besar.

Kondisi kesalahan berikut dapat menyebabkan transaksi gagal saat mengakses tabel yang dioptimalkan memori.

Kode kesalahan	Deskripsi	Penyebab
`41302`	Upaya memperbarui baris yang telah diperbarui dalam transaksi lain sejak awal transaksi ini.	Kondisi kesalahan ini terjadi jika dua transaksi bersamaan mencoba memperbarui atau menghapus baris yang sama secara bersamaan. Salah satu dari dua transaksi menerima pesan kesalahan ini dan perlu dicoba kembali.
`41305`	Kegagalan validasi baca yang dapat diulang. Baris yang dibaca dari tabel yang dioptimalkan memori transaksi ini telah diperbarui oleh transaksi lain yang dilakukan sebelum penerapan transaksi ini.	Kesalahan ini dapat terjadi saat menggunakan `REPEATABLE READ` atau `SERIALIZABLE` mengisolasi, dan juga jika tindakan transaksi bersamaan menyebabkan pelanggaran batasan `FOREIGN KEY` . Pelanggaran bersamaan terhadap batasan kunci asing semacam itu jarang terjadi, dan biasanya menunjukkan masalah dengan logika aplikasi atau dengan entri data. Namun, kesalahan juga dapat terjadi jika tidak ada indeks pada kolom yang terlibat dengan batasan `FOREIGN KEY` . Oleh karena itu, selalu buat indeks pada kolom kunci asing dalam tabel yang dioptimalkan memori. Untuk pertimbangan lebih rinci tentang kegagalan validasi yang disebabkan oleh pelanggaran kunci asing, lihat Pertimbangan sekeliling kesalahan validasi 41305 dan 41325 pada tabel memori yang dioptimalkan dengan kunci asing oleh Tim Penasihat Pelanggan SQL Server.
`41325`	Kegagalan validasi yang dapat diserialisasikan. Baris baru dimasukkan ke dalam rentang yang dipindai sebelumnya oleh transaksi saat ini. Kami menyebut ini baris semu.	Kesalahan ini dapat terjadi saat menggunakan `SERIALIZABLE` isolasi, dan juga jika tindakan transaksi bersamaan menyebabkan pelanggaran terhadap batasan `PRIMARY KEY`, `UNIQUE`, atau `FOREIGN KEY`. Pelanggaran batasan bersamaan tersebut jarang terjadi, dan biasanya menunjukkan masalah dengan logika aplikasi atau entri data. Namun, mirip dengan kegagalan validasi baca yang dapat diulang, kesalahan ini juga dapat terjadi jika ada `FOREIGN KEY` batasan tanpa indeks pada kolom yang terlibat.
`41301`	Kegagalan dependensi: dependensi terjadi pada transaksi lain yang kemudian gagal melakukan commit.	Transaksi ini (`Tx1`) mengambil dependensi pada transaksi lain (`Tx2`) sementara transaksi tersebut (`Tx2`) berada dalam fase validasi atau pemrosesan penerapan, dengan membaca data yang ditulis oleh `Tx2`. `Tx2` kemudian gagal melakukan komit. Penyebab paling umum `Tx2` gagal melakukan commit adalah kegagalan validasi repeatable read (41305) dan serializable (41325). Penyebab yang jarang terjadi adalah kegagalan log IO.
`41823` dan `41840`	Kuota data pengguna dalam tabel yang dioptimalkan untuk memori dan variabel tabel telah tercapai.	Kesalahan 41823 berlaku untuk edisi SQL Server Express, Web, dan Standard, dan database tunggal di Azure SQL Database. Kesalahan 41840 berlaku untuk kumpulan elastis di Azure SQL Database. Dalam kebanyakan kasus, kesalahan ini menunjukkan bahwa ukuran data pengguna maksimum tercapai. Untuk mengatasi kesalahan, hapus data dari tabel yang dioptimalkan memori. Namun, kasus langka ada di mana kesalahan ini bersifat sementara. Coba lagi ketika pertama kali mengalami kesalahan ini. Seperti kesalahan lain dalam daftar ini, kesalahan 41823 dan 41840 menyebabkan transaksi aktif dibatalkan.
`41839`	Transaksi melebihi jumlah maksimum dependensi penerapan.	Ada batasan jumlah transaksi yang dapat bergantung pada transaksi tertentu (`Tx1`). Transaksi tersebut merupakan dependensi yang keluar. Selain itu, ada batasan jumlah transaksi yang dapat bergantung pada transaksi tertentu (`Tx1`). Transaksi ini adalah dependensi masuk. Batas untuk keduanya adalah 8. Kasus paling umum untuk kegagalan ini adalah di mana sejumlah besar transaksi baca mengakses data yang ditulis oleh satu transaksi tulis. Kemungkinan mencapai kondisi ini meningkat jika transaksi baca semuanya melakukan pemindaian besar dari data yang sama dan jika validasi atau melakukan pemrosesan transaksi tulis membutuhkan waktu lama. Misalnya, transaksi tulis melakukan pemindaian besar di bawah isolasi serializable (yang memperpanjang fase validasi) atau log transaksi ditempatkan pada perangkat IO log lambat (yang memperpanjang waktu pemrosesan commit). Jika transaksi baca melakukan pemindaian besar dan diharapkan hanya mengakses beberapa baris, indeks mungkin hilang. Demikian pula, jika transaksi tulis menggunakan isolasi yang dapat diserialisasikan dan melakukan pemindaian besar tetapi diharapkan hanya mengakses beberapa baris, kondisi ini juga menunjukkan indeks yang hilang. Anda dapat mengangkat batas jumlah dependensi commit dengan menggunakan bendera pelacakan `9926`. Gunakan trace flag ini hanya jika Anda masih mengalami kondisi kesalahan ini setelah dikonfirmasi bahwa tidak ada indeks yang hilang, karena dapat menyembunyikan masalah ini dalam kasus yang disebutkan di atas. Perhatian lain adalah bahwa grafik dependensi yang kompleks, di mana setiap transaksi memiliki sejumlah besar dependensi masuk dan keluar, dan transaksi individu memiliki banyak lapisan dependensi, dapat menyebabkan inefisiensi dalam sistem. Berlaku untuk: SQL Server 2016 (13.x). Versi terbaru dari SQL Server dan Azure SQL Database tidak memiliki batas jumlah commit dependencies.

Logika percobaan ulang

Ketika transaksi gagal karena salah satu kondisi yang disebutkan sebelumnya, coba kembali transaksi.

Anda dapat menerapkan logika coba lagi di sisi klien atau server. Terapkan logika coba lagi di sisi klien untuk efisiensi yang lebih baik. Pendekatan ini juga membantu Anda menangani kumpulan hasil yang dikembalikan oleh transaksi sebelum kegagalan terjadi.

Coba lagi contoh kode T-SQL

Gunakan logika coba lagi sisi server dengan T-SQL hanya untuk transaksi yang tidak mengembalikan tataan hasil ke klien. Jika tidak, upaya ulang berpotensi mengembalikan set hasil tambahan ke klien.

Skrip T-SQL yang ditafsirkan berikut menunjukkan seperti apa logika coba lagi untuk kesalahan yang terkait dengan konflik transaksi yang melibatkan tabel yang dioptimalkan memori.

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry AS INT = 10;

    WHILE (@retry > 0)
    BEGIN

        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
            SET OrderDate = GETUTCDATE()
            WHERE CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
            SET OrderDate = GETUTCDATE()
            WHERE CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0; -- Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry - = 1;

            IF (@retry > 0
                AND ERROR_NUMBER() IN (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205))
                BEGIN
                    IF XACT_STATE() = -1
                        ROLLBACK TRANSACTION;

                    WAITFOR DELAY '00:00:00.001';
                END
            ELSE
                BEGIN
                    PRINT 'Suffered an error for which Retry is inappropriate.';
                    THROW;
                END
        END CATCH

    END -- While loop
END
GO

-- EXECUTE usp_update_salesorder_dates;

Transaksi lintas kontainer

Transaksi adalah transaksi lintas kontainer jika:

Mengakses tabel yang dioptimalkan untuk memori dalam Transact-SQL yang ditafsirkan.
Menjalankan proc asli ketika transaksi sudah terbuka (XACT_STATE() = 1).

Istilah "cross-container" berasal dari fakta bahwa transaksi berjalan melintasi dua kontainer manajemen transaksi. Satu kontainer mengelola tabel berbasis disk, dan yang lain mengelola tabel yang dioptimalkan memori.

Dalam satu transaksi lintas kontainer, Anda dapat menggunakan tingkat isolasi yang berbeda untuk mengakses tabel berbasis disk dan memori yang dioptimalkan. Anda mengekspresikan perbedaan ini melalui petunjuk tabel eksplisit seperti WITH (SERIALIZABLE) atau melalui opsi MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTdatabase . Opsi ini secara implisit meningkatkan tingkat isolasi untuk tabel yang dioptimalkan memori ke snapshot jika dikonfigurasi TRANSACTION ISOLATION LEVEL sebagai READ COMMITTED atau READ UNCOMMITTED.

Dalam contoh kode Transact-SQL berikut:

Tabel berbasis disk, Table_D1, diakses menggunakan READ COMMITTED tingkat isolasi.
Tabel yang dioptimalkan memori Table_MO7 diakses menggunakan tingkat isolasi SERIALIZABLE. Table_MO6 tidak memiliki tingkat isolasi tertentu yang terkait, karena penyisipan selalu konsisten dan dijalankan pada dasarnya di bawah isolasi serialisasi.

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.
SELECT *
FROM Table_D1;

-- Table_MO6 and Table_MO7 are memory-optimized tables.
-- Table_MO7 is accessed using SERIALIZABLE isolation,
-- while Table_MO6 does not have a specific isolation level.

INSERT INTO Table_MO6
SELECT *
FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;

Batasan

Transaksi lintas database tidak didukung untuk tabel yang dioptimalkan memori. Jika transaksi mengakses tabel yang dioptimalkan memori, transaksi tidak dapat mengakses database lain, kecuali untuk:
- tempdb Database.
- Akses baca-saja dari master database.
Transaksi terdistribusi tidak didukung: Saat Anda menggunakan BEGIN DISTRIBUTED TRANSACTION, transaksi tidak dapat mengakses tabel memori yang dioptimalkan.

Prosedur tersimpan yang dikompilasi secara asli

Dalam proc asli, ATOMIC blok harus menyatakan tingkat isolasi transaksi untuk seluruh blok, seperti:

... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...
Anda tidak dapat menyertakan pernyataan kontrol transaksi eksplisit dalam isi prosedur tersimpan asli. Pernyataan seperti BEGIN TRANSACTION dan ROLLBACK TRANSACTION tidak diizinkan.
Untuk informasi selengkapnya tentang kontrol transaksi dengan ATOMIC blok, lihat Blok Atom dalam Prosedur Asli.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-07-20