Menggunakan transaksi dengan kumpulan SQL khusus di Azure Synapse Analytics

Artikel
06/01/2023

Tips untuk menerapkan transaksi dengan kumpulan SQL khusus di Azure Synapse Analytics untuk mengembangkan solusi.

Hal yang diharapkan

Seperti yang Anda harapkan, kumpulan SQL khusus mendukung transaksi sebagai bagian dari beban kerja gudang data. Namun, untuk memastikan performa kumpulan SQL khusus dipertahankan pada skala besar, beberapa fitur akan terbatas jika dibandingkan dengan SQL Server. Artikel ini menyoroti perbedaan dan mencantumkan yang lain.

Tingkat isolasi transaksi

Kumpulan SQL khusus akan menerapkan transaksi ACID. Tingkat isolasi dukungan transaksional diatur menjadi default ke READ UNCOMMITTED. Anda dapat mengubahnya ke READ COMMITTED SNAPSHOT ISOLATION dengan MENGAKTIFKAN opsi database READ_COMMITTED_SNAPSHOT untuk database pengguna saat tersambung ke database master.

Setelah diaktifkan, semua transaksi dalam database ini dijalankan di READ COMMITTED SNAPSHOT ISOLATION dan pengaturan READ UNCOMMITTED pada tingkat sesi tidak akan diterapkan. Centang opsi ALTER DATABASE SET (T-SQL) untuk mengetahui detailnya.

Ukuran transaksi

Satu transaksi modifikasi data berukuran terbatas. Batas diterapkan per distribusi. Dengan demikian, total alokasi dapat dihitung dengan mengalikan batas dengan jumlah distribusi.

Untuk memperkirakan jumlah maksimum baris dalam transaksi, bagi batas distribusi dengan ukuran total setiap baris. Untuk kolom panjang variabel, pertimbangkan untuk membuat ukuran panjang kolom rata-rata daripada menggunakan ukuran maksimum.

Dalam tabel di bawah asumsi berikut telah dibuat:

Distribusi data yang merata telah diterapkan
Panjang baris rata-rata adalah 250 byte

Gen2

DWU	Tutup per distribusi (GB)	Jumlah Distribusi	Ukuran transaksi MAX (GB)	# Baris per distribusi	Maks Baris per transaksi
DW100c	1	60	60	4\.000.000	240.000.000
DW200c	1.5	60	90	6\.000.000	360.000.000
DW300c	2,25	60	135	9\.000.000	540.000.000
DW400c	3	60	180	12.000.000	720.000.000
DW500c	3,75	60	225	15.000.000	900.000.000
DW1000c	7,5	60	450	30.000.000	1\.800.000.000
DW1500c	11,25	60	675	45.000.000	2\.700.000.000
DW2000c	15	60	900	60.000.000	3\.600.000.000
DW2500c	18,75	60	1125	75.000.000	4\.500.000.000
DW3000c	22,5	60	1\.350	90.000.000	5\.400.000.000
DW5000c	37,5	60	2,250	150.000.000	9\.000.000.000
DW6000c	45	60	2\.700	180.000.000	10.800.000.000
DW7500c	56,25	60	3\.375	225.000.000	13.500.000.000
DW10000c	75	60	4\.500	300.000.000	18.000.000.000
DW15000c	112,5	60	6\.750	450.000.000	27.000.000.000
DW30000c	225	60	13.500	900.000.000	54.000.000.000

Gen1

DWU	Tutup per distribusi (GB)	Jumlah Distribusi	Ukuran transaksi MAX (GB)	# Baris per distribusi	Maks Baris per transaksi
DW100	1	60	60	4\.000.000	240.000.000
DW200	1.5	60	90	6\.000.000	360.000.000
DW300	2,25	60	135	9\.000.000	540.000.000
DW400	3	60	180	12.000.000	720.000.000
DW500	3,75	60	225	15.000.000	900.000.000
DW600	4,5	60	270	18.000.000	1\.080.000.000
DW1000	7,5	60	450	30.000.000	1\.800.000.000
DW1200	9	60	540	36.000.000	2\.160.000.000
DW1500	11,25	60	675	45.000.000	2\.700.000.000
DW2000	15	60	900	60.000.000	3\.600.000.000
DW3000	22,5	60	1\.350	90.000.000	5\.400.000.000
DW6000	45	60	2\.700	180.000.000	10.800.000.000

Batas ukuran transaksi diterapkan per transaksi atau operasi. Batas tidak diterapkan di semua transaksi bersamaan. Oleh karena itu, setiap transaksi diizinkan untuk menulis jumlah data ini ke log.

Untuk mengoptimalkan dan meminimalkan jumlah data yang tertulis pada log, lihat artikel Praktik terbaik transaksi.

Peringatan

Ukuran transaksi maksimum hanya dapat dicapai untuk tabel terdistribusi HASH atau ROUND_ROBIN dengan penyebaran data merata. Jika transaksi menulis data secara miring ke distribusi, maka batasnya kemungkinan akan tercapai sebelum ukuran transaksi maksimum.

Status transaksi

Kumpulan SQL khusus menggunakan fungsi XACT_STATE() untuk melaporkan transaksi yang gagal menggunakan nilai -2. Nilai ini berarti transaksi telah gagal dan ditandai hanya untuk pembatalan.

Catatan

Penggunaan -2 oleh fungsi XACT_STATE untuk menunjukkan transaksi gagal mewakili perilaku yang berbeda dengan SQL Server. SQL Server menggunakan nilai -1 untuk mewakili transaksi yang tidak dapat dieksekusi. SQL Server dapat mentolerir beberapa kesalahan dalam transaksi tanpa harus ditandai sebagai tidak dapat dieksekusi. Misalnya SELECT 1/0 akan menyebabkan kesalahan tetapi tidak memaksa transaksi ke dalam keadaan yang tidak dapat dieksekusi. SQL Server juga mengizinkan pembacaan dalam transaksi yang tidak dapat dieksekusi. Namun, kumpulan SQL khusus tidak memungkinkan Anda melakukan ini. Jika kesalahan terjadi di dalam transaksi kumpulan SQL khusus, transaksi akan secara otomatis memasuki status -2 dan Anda tidak akan dapat membuat pernyataan select selanjutnya sampai pernyataan telah digulung balik. Oleh karena itu, penting untuk memeriksa kode aplikasi Anda untuk melihat apakah itu menggunakan XACT_STATE() karena Anda mungkin perlu membuat modifikasi kode.

Misalnya, di SQL Server Anda mungkin melihat transaksi yang terlihat seperti berikut:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

Kode sebelumnya memberikan pesan kesalahan berikut:

Msg 111233, Tingkat 16, Negara Bagian 1, Jalur 1 111233; Transaksi saat ini telah dibatalkan, dan setiap perubahan yang tertunda telah digulung balik. Penyebab: Transaksi dalam status hanya putar kembali tidak secara eksplisit digulirkan kembali sebelum pernyataan DDL, DML, atau SELECT.

Anda tidak akan mendapatkan output dari fungsi ERROR_*.

Dalam kumpulan SQL khusus, kode perlu sedikit diubah:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;
    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

Perilaku yang diharapkan sekarang diamati. Kesalahan dalam transaksi dikelola dan fungsi ERROR_* memberikan nilai seperti yang diharapkan.

Yang berubah hanyalah bahwa ROLLBACK transaksi harus terjadi sebelum pembacaan informasi kesalahan di blok CATCH.

Fungsi Error_Line()

Perlu juga dicatat bahwa kumpulan SQL khusus tidak mengimplementasikan atau mendukung fungsi ERROR_LINE(). Jika Anda memiliki fungsi ini dalam kode Anda, Anda perlu menghapusnya agar sesuai dengan kumpulan SQL khusus. Gunakan label kueri dalam kode Anda sebagai gantinya untuk menerapkan fungsionalitas yang setara. Untuk mengetahui informasi selengkapnya, lihat artikel LABEL.

Penggunaan THROW dan RAISERROR

THROW adalah implementasi yang lebih modern untuk menerapkan pengecualian di kumpulan SQL khusus tetapi RAISERROR juga didukung. Namun ada beberapa perbedaan yang patut diperhatikan.

Nomor pesan kesalahan yang ditentukan pengguna tidak boleh berada dalam rentang 100.000 - 150.000 untuk THROW
Pesan kesalahan RAISERROR diperbaiki pada 50.000
Penggunaan sys.messages tidak didukung

Batasan

Kumpulan SQL khusus memang memiliki beberapa batasan lain yang berkaitan dengan transaksi. Klaimnya sebagai berikut:

Tidak ada transaksi terdistribusi
Tidak ada transaksi berlapis yang diizinkan
Tidak ada titik simpan yang diizinkan
Tidak ada transaksi bernama
Tidak ada transaksi yang ditandai
Tidak ada dukungan untuk DDL seperti CREATE TABLE di dalam transaksi yang ditentukan pengguna

Langkah berikutnya

Untuk mempelajari lebih lanjut cara mengoptimalkan transaksi, lihat Praktik terbaik transaksi. Panduan praktik terbaik tambahan juga tersedia untuk kumpulan SQL Khusus dan kumpulan SQL tanpa server.