Transaksi dengan Tabel yang Dioptimalkan Memori

Artikel
04/26/2023

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, lompat ke bagian Deteksi Konflik dan Logika Coba Lagi.

Untuk informasi umum, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Pesimis versus Optimis

Perbedaan fungsi ini disebabkan oleh pendekatan pesimis versus optimis terhadap 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 kurang overhead 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 fungsi lebih besar ketika tingkat isolasi READ YANG DAPAT DIULANG berlaku, dan terbesar untuk tingkat SERIALIZABLE.

Mode Inisiasi Transaksi

SQL Server memiliki mode berikut untuk inisiasi transaksi:

Autocommit - Awal kueri sederhana atau pernyataan DML secara implisit membuka transaksi, dan akhir pernyataan secara implisit melakukan transaksi. Autocommit adalah default.
- Dalam mode autocommit, biasanya Anda tidak diharuskan untuk membuat kode petunjuk tabel tentang tingkat isolasi transaksi pada tabel yang dioptimalkan memori dalam klausul FROM.
Eksplisit - Transact-SQL Anda berisi kode BEGIN TRANSACTION, bersama dengan COMMIT TRANSACTION akhirnya. Dua pernyataan atau lebih dapat dikoralkan ke dalam transaksi yang sama.
- Dalam mode eksplisit, Anda harus menggunakan opsi database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT atau mengodekan petunjuk tabel tentang tingkat isolasi transaksi pada tabel yang dioptimalkan memori dalam klausa FROM.
Implisit - Saat SET IMPLICIT_TRANSACTION AKTIF berlaku. Mungkin nama yang lebih baik akan IMPLICIT_BEGIN_TRANSACTION, karena semua opsi ini secara implisit melakukan setara dengan BEGIN TRANSACTION eksplisit sebelum setiap pernyataan UPDATE jika 0 = @@trancount. Oleh karena itu terserah kode T-SQL Anda untuk akhirnya mengeluarkan COMMIT TRANSACTION eksplisit.
ATOMIC BLOCK - Semua pernyataan dalam blok ATOMIC selalu berjalan sebagai bagian dari satu transaksi. Baik tindakan blok atom secara keseluruhan dilakukan pada keberhasilan, atau semua tindakan digulung balik ketika kegagalan terjadi. Setiap prosedur tersimpan yang dikompilasi secara asli memerlukan blok ATOMIC.

Contoh Kode dengan Mode Eksplisit

Skrip Transact-SQL yang ditafsirkan berikut menggunakan:

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

Oleh karena itu perlu memiliki 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 petunjuk ini dihapus, skrip akan mengalami kesalahan 41368, yang percobaan ulang otomatisnya tidak pantas:

Kesalahan 41368

Mengakses tabel memori yang dioptimalkan menggunakan tingkat 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 menggunakan petunjuk tabel, seperti WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

Kebutuhan akan WITH (SNAPSHOT) petunjuk dapat dihindari melalui penggunaan opsi MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTdatabase . Ketika opsi ini diatur ke ON, akses ke tabel yang dioptimalkan memori di bawah tingkat isolasi yang lebih rendah secara otomatis ditingkatkan ke isolasi SNAPSHOT.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Penerapan Versi Baris

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

Tabel berbasis disk secara tidak langsung memiliki sistem penerapan versi baris saat READ_COMMITTED_SNAPSHOT atau tingkat isolasi SNAPSHOT berlaku. Sistem ini didasarkan pada tempdb, sementara struktur data yang dioptimalkan memori memiliki penerapan 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 tentang konflik yang dapat terjadi dan mencoba kembali logika untuk menangani konflik ini, lihat Deteksi Konflik dan Logika Coba Lagi.

Tingkat Isolasi	Deskripsi
BACA TIDAK DIKOMIT	Tidak tersedia: tabel yang dioptimalkan memori tidak dapat diakses di bawah Baca Isolasi yang tidak dikomit. Masih dimungkinkan untuk mengakses tabel yang dioptimalkan memori di bawah isolasi SNAPSHOT jika TINGKAT ISOLASI TRANSAKSI tingkat sesi diatur ke READ UNCOMMITTED, dengan menggunakan petunjuk tabel WITH (SNAPSHOT) atau mengatur pengaturan database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ke AKTIF.
READ COMMITTED	Didukung untuk tabel yang dioptimalkan memori hanya ketika mode autocommit berlaku. Masih dimungkinkan untuk mengakses tabel yang dioptimalkan memori di bawah isolasi SNAPSHOT jika TINGKAT ISOLASI TRANSAKSI tingkat sesi diatur ke READ COMMITTED, dengan menggunakan petunjuk tabel WITH (SNAPSHOT) atau mengatur pengaturan database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ke AKTIF. Jika opsi database READ_COMMITTED_SNAPSHOT diatur ke AKTIF, tidak diizinkan untuk mengakses tabel yang dioptimalkan memori dan berbasis disk di bawah isolasi READ COMMITTED dalam pernyataan yang sama.
SNAPSHOT	Didukung untuk tabel yang dioptimalkan memori. SNAPSHOT secara internal adalah tingkat isolasi transaksi yang paling tidak menuntut untuk tabel yang dioptimalkan memori. SNAPSHOT menggunakan lebih sedikit sumber daya sistem daripada REPEATABLE READ atau SERIALIZABLE.
REPEATABLE READ	Didukung untuk tabel yang dioptimalkan memori. Jaminan yang diberikan oleh isolasi REPEATABLE READ adalah bahwa, pada waktu penerapan, tidak ada transaksi bersamaan yang memperbarui salah satu baris yang dibaca oleh transaksi ini. Karena model optimis, transaksi bersamaan tidak dicegah untuk memperbarui baris yang dibaca oleh transaksi ini. Sebaliknya, pada waktu penerapan transaksi ini memvalidasi bahwa isolasi READ BERULANG belum dilanggar. Jika sudah, transaksi ini digulung balik dan harus dicoba kembali.
SERIALIZABLE	Didukung untuk tabel yang dioptimalkan memori. Bernama Serializable karena isolasi sangat ketat sehingga hampir sedikit seperti transaksi berjalan secara seri daripada secara bersamaan.

Fase Transaksi dan Masa Pakai

Ketika tabel yang dioptimalkan memori terlibat, masa pakai transaksi berlangsung melalui fase seperti yang ditampilkan dalam gambar berikut:

hekaton_transactions

Deskripsi fase berikut.

Pemrosesan Reguler: Fase 1 (dari 3)

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

Validasi: Fase 2 (dari 3)

Fase validasi dimulai dengan menetapkan waktu akhir, sehingga menandai transaksi sebagai selesai secara logis. Penyelesaian ini membuat semua perubahan transaksi terlihat oleh transaksi lain yang mengambil dependensi pada transaksi ini. Transaksi dependen tidak diizinkan untuk dilakukan sampai transaksi ini berhasil dilakukan. Selain itu, transaksi yang menyimpan dependensi tersebut tidak diizinkan untuk mengembalikan tataan hasil ke klien, untuk memastikan klien hanya melihat data yang telah berhasil diterapkan ke database.
Fase ini terdiri dari bacaan berulang dan validasi yang dapat diserialisasikan. Untuk validasi baca yang dapat diulang, ini memeriksa apakah salah satu baris yang dibaca oleh transaksi telah diperbarui. Untuk validasi yang dapat diserialisasikan, ia memeriksa apakah ada baris yang telah dimasukkan ke dalam rentang data apa pun yang dipindai oleh transaksi ini. Per tabel di Tingkat Isolasi dan Konflik, validasi baca yang dapat diulang dan dapat diserialisasikan dapat terjadi saat menggunakan isolasi rekam jepret, untuk memvalidasi konsistensi batasan kunci unik dan asing.

Pemrosesan Penerapan: Fase 3 (dari 3)

Selama fase penerapan, perubahan pada tabel tahan lama ditulis ke log, dan log ditulis ke disk. Kemudian kontrol dikembalikan ke klien.
Setelah pemrosesan penerapan selesai, semua transaksi dependen diberi tahu bahwa mereka dapat melakukan.

Seperti biasa, Anda harus mencoba menjaga unit kerja transaksional Anda minimal dan singkat seperti yang berlaku untuk kebutuhan data Anda.

Deteksi Konflik dan Logika Coba Lagi

Ada dua jenis kondisi kesalahan terkait transaksi yang menyebabkan transaksi gagal dan digulung balik. Dalam kebanyakan kasus, setelah kegagalan seperti itu terjadi, transaksi perlu dicoba kembali, mirip dengan ketika kebuntuan terjadi.

Konflik antara transaksi bersamaan. Ini adalah konflik pembaruan dan kegagalan validasi, dan dapat disebabkan oleh pelanggaran tingkat isolasi transaksi atau pelanggaran batasan.
Kegagalan dependensi. Hasil ini dari transaksi yang Anda bergantung pada kegagalan penerapan, atau dari jumlah dependensi yang tumbuh terlalu besar.

Berikut ini adalah kondisi kesalahan yang dapat menyebabkan transaksi gagal saat mengakses tabel yang dioptimalkan memori.

Kode Kesalahan	Deskripsi	Penyebab
41302	Mencoba memperbarui baris yang diperbarui dalam transaksi yang berbeda sejak awal transaksi saat 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 telah dilakukan sebelum penerapan transaksi ini.	Kesalahan ini dapat terjadi saat menggunakan ISOLASI REPEATABLE READ atau SERIALIZABLE, dan juga jika tindakan transaksi bersamaan menyebabkan pelanggaran batasan KUNCI ASING. 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 KUNCI ASING. Oleh karena itu, panduannya adalah selalu membuat indeks pada kolom kunci asing dalam tabel yang dioptimalkan memori. Untuk pertimbangan lebih rinci tentang kegagalan validasi yang disebabkan oleh pelanggaran kunci asing, lihat posting blog ini 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 menyebutnya baris phantom.	Kesalahan ini dapat terjadi saat menggunakan isolasi SERIALIZABLE, dan juga jika tindakan transaksi bersamaan menyebabkan pelanggaran kunci PRIMER, UNIK, atau batasan KUNCI ASING. 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 batasan KUNCI ASING tanpa indeks pada kolom yang terlibat.
41301	Kegagalan dependensi: dependensi diambil pada transaksi lain yang kemudian gagal diterapkan.	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 berkomitmen. Penyebab paling umum agar Tx2 gagal melakukan adalah kegagalan validasi baca yang dapat diulang (41305) dan dapat diserialisasikan (41325) ; penyebab yang kurang umum adalah kegagalan IO log.
41823 dan 41840	Kuota untuk data pengguna dalam tabel yang dioptimalkan memori dan variabel tabel tercapai.	Kesalahan 41823 berlaku untuk SQL Server Express/Web/Standard Edition, serta 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, dan cara mengatasi kesalahan adalah dengan menghapus data dari tabel yang dioptimalkan memori. Namun, ada kasus langka di mana kesalahan ini bersifat sementara. Oleh karena itu, kami menyarankan untuk mencoba kembali 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.	Berlaku untuk: SQL Server 2016 (13.x). Versi SQL Server dan Azure SQL Database yang lebih baru tidak memiliki batasan jumlah dependensi penerapan. Ada batasan jumlah transaksi yang dapat bergantung pada transaksi tertentu (Tx1). Transaksi tersebut adalah dependensi 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 ada sejumlah besar transaksi baca yang 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 penerapan pemrosesan transaksi tulis membutuhkan waktu lama, misalnya transaksi tulis melakukan pemindaian besar di bawah isolasi yang dapat diserialisasikan (meningkatkan panjang fase validasi) atau log transaksi ditempatkan pada perangkat IO log lambat (meningkatkan lama pemrosesan penerapan). 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, ini juga merupakan indikasi indeks yang hilang. Batas jumlah dependensi penerapan dapat diangkat dengan menggunakan Bendera Pelacakan 9926. Gunakan bendera pelacakan ini hanya jika Anda masih mencapai kondisi kesalahan ini setelah mengonfirmasi bahwa tidak ada indeks yang hilang, karena dapat menutupi 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 serta keluar, dan transaksi individu memiliki banyak lapisan dependensi, dapat menyebabkan inefisiensi dalam sistem.

Logika Coba Lagi

Ketika transaksi gagal karena salah satu kondisi yang disebutkan di atas, transaksi harus dicoba kembali.

Logika coba lagi dapat diimplementasikan di sisi klien atau server. Rekomendasi umumnya adalah menerapkan logika coba lagi di sisi klien, karena lebih efisien, dan memungkinkan Anda untuk menangani kumpulan hasil yang dikembalikan oleh transaksi sebelum kegagalan terjadi.

Coba Lagi Contoh Kode T-SQL

Logika coba lagi sisi server menggunakan T-SQL hanya boleh digunakan untuk transaksi yang tidak mengembalikan tataan hasil ke klien. Jika tidak, percobaan ulang berpotensi menghasilkan tataan hasil tambahan di luar yang diantisipasi dikembalikan ke klien.

Skrip T-SQL yang ditafsirkan berikut menggambarkan 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 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 disebut transaksi lintas kontainer jika:

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

Istilah "lintas kontainer" berasal dari fakta bahwa transaksi berjalan di dua kontainer manajemen transaksi, satu untuk tabel berbasis disk dan satu untuk tabel yang dioptimalkan memori.

Dalam satu transaksi lintas kontainer, tingkat isolasi yang berbeda dapat digunakan untuk mengakses tabel berbasis disk dan memori yang dioptimalkan. Perbedaan ini dinyatakan melalui petunjuk tabel eksplisit seperti WITH (SERIALIZABLE) atau melalui opsi database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, yang secara implisit meningkatkan tingkat isolasi untuk tabel yang dioptimalkan memori untuk rekam jepret jika TINGKAT ISOLASI TRANSAKSI dikonfigurasi sebagai READ COMMITTED atau READ UNCOMMITTED.

Dalam contoh kode Transact-SQL berikut:

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

-- 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 Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

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:
- database tempdb.
- Baca-saja dari database master.
Transaksi terdistribusi tidak didukung: Ketika BEGIN DISTRIBUTED TRANSACTION digunakan, transaksi tidak dapat mengakses tabel memori yang dioptimalkan.

Prosedur Tersimpan yang Dikompilasi Secara Asli

Dalam proc asli, blok ATOMIC harus menyatakan tingkat isolasi transaksi untuk seluruh blok, seperti:
- ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...
Tidak ada pernyataan kontrol transaksi eksplisit yang diizinkan dalam isi proc asli. BEGIN TRANSACTION, ROLLBACK TRANSACTION, dan sebagainya, semuanya tidak diizinkan.
Untuk informasi selengkapnya tentang kontrol transaksi dengan blok ATOMIC, lihat Blok Atomik

Bagikan melalui