Menggunakan transaksi di kumpulan SQL di Azure Synapse Analytics

Artikel
06/01/2023

Artikel ini mencakup tips untuk menerapkan transaksi dan mengembangkan solusi di kumpulan SQL.

Hal yang diharapkan

Seperti yang Anda harapkan, kumpulan SQL mendukung transaksi sebagai bagian dari beban kerja gudang data. Namun, untuk memastikan kumpulan SQL dipertahankan pada skala besar, beberapa fitur akan dibatasi jika dibandingkan dengan SQL Server. Artikel ini menyoroti perbedaannya.

Tingkat isolasi

Kumpulan SQL 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 kumpulan SQL 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 berikut, dua asumsi telah dibuat:

Distribusi data yang merata telah terjadi
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 mengecilkan 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 menggunakan fungsi XACT_STATE() untuk melaporkan transaksi gagal menggunakan nilai -2. Nilai ini berarti transaksi telah gagal dan ditandai hanya untuk putar kembali.

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 status yang tidak dapat dieksekusi.

SQL Server juga mengizinkan pembacaan dalam transaksi yang tidak dapat dieksekusi. Namun, kumpulan SQL khusus tidak mengizinkan Anda melakukan ini. Jika kesalahan terjadi di dalam transaksi kumpulan SQL, transaksi akan secara otomatis memasuki status -2 dan Anda tidak akan dapat membuat pernyataan pilihan selanjutnya sampai pernyataan telah digulung balik.

Karena itu, penting untuk memeriksa apakah kode aplikasi Anda menggunakan XACT_STATE() karena Anda mungkin perlu melakukan 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, Status 1, Garis 1 111233; Transaksi saat ini telah dibatalkan, dan setiap perubahan yang tertunda telah digulung balik. Penyebab masalah ini adalah bahwa transaksi dalam keadaan hanya gulung balik tidak secara eksplisit digulung balik sebelum pernyataan DDL, DML, atau SELECT.

Anda tidak akan mendapatkan output dari fungsi ERROR_*.

Dalam kumpulan SQL, 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 tidak menerapkan atau mendukung fungsi ERROR_LINE(). Jika Anda memiliki ini dalam kode, Anda perlu menghapusnya agar sesuai dengan kumpulan SQL.

Gunakan label kueri dalam kode Anda sebagai gantinya untuk menerapkan fungsionalitas yang setara. Untuk detail selengkapnya, lihat artikel LABEL.

Menggunakan THROW dan RAISERROR

THROW adalah implementasi yang lebih modern untuk menerapkan pengecualian di kumpulan SQL 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 memang memiliki beberapa batasan lain yang berkaitan dengan transaksi.

Batasannya adalah 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. Untuk mempelajari praktik terbaik kumpulan SQL lainnya, lihat Praktik terbaik kumpulan SQL.

Bagikan melalui