Mengapa transaksi t-sql berikut tidak berfungsi seperti yang diharapkan?

Untuk menguji cara kerja suatu transaksi, saya menulis t-sql berikut. Karena atribut pertama adalah kunci utama, tidak ada penyisipan yang harus dilakukan. Tapi, penyisipan pertama sudah dilakukan? Mengapa?

begin transaction
   insert into instructor
   values ('99999', 'Yellow', 'Biology', 700000)

   insert into instructor
   values ('99999', 'Blue', 'Statistics', 85000)
commit;

select * from instructor where ID = '99999'

--delete from instructor where ID = '99999'

(1 baris terpengaruh) Pesan 2627, Level 14, Status 1, Baris 100 Pelanggaran batasan PRIMARY KEY 'PK__instruct__3214EC278C8DA99F'. Tidak dapat memasukkan kunci duplikat ke objek 'dbo.instructor'. Nilai kunci duplikat adalah (99999). Pernyataan itu telah dihentikan.

Waktu penyelesaian: 06-11-2019T14:02:27.3436411+02:00


sql-server transactions
person Shahin Mehdipour Ataee    schedule 06.11.2019    source sumber
comment
Hasil ini bisa Anda peroleh jika XACT_ABORT adalah OFF, karena pelanggaran kunci duplikat hanya membatalkan penyisipan kedua, dan bukan transaksi secara keseluruhan, sehingga COMMIT tetap terjadi. Jika Anda melakukan SET XACT_ABORT ON terlebih dahulu, komit tidak akan terjadi. Aturan pasti tentang apa yang benar-benar membatalkan dan tidak membatalkan suatu transaksi sangat rumit, bahkan jika XACT_ABORT dapat menyederhanakan banyak hal.   -  person Jeroen Mostert    schedule 06.11.2019

Jawaban (2)


arrow_upward
0
arrow_downward

Sebab, ketika XACT_ABORT IS OFF (yang merupakan default):

Ketika SET XACT_ABORT OFF, dalam beberapa kasus hanya pernyataan Transact-SQL yang memunculkan kesalahan yang dibatalkan dan transaksi melanjutkan pemrosesan.

dan ketika ON:

.. jika pernyataan Transact-SQL menimbulkan kesalahan run-time, seluruh transaksi dihentikan dan dibatalkan.

Itu yang kita butuhkan untuk mengubahnya menjadi ON dan jika Anda mencoba kode di bawah ini, Anda dapat memeriksanya:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [StackID] TINYINT PRIMARY KEY
);

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

SELECT [StackID]
FROM [dbo].[StackOverflow];

Juga, perhatikan bahwa:

Kesalahan kompilasi, seperti kesalahan sintaksis, tidak terpengaruh oleh SET XACT_ABORT.

Artinya, jika Anda ingin benar-benar melakukan transaksi otomatis, Anda perlu menggunakan blok kode berikut:

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY

    BEGIN TRANSACTION;
    -- CODE BLOCK GOES HERE
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH 

   IF @@TRANCOUNT > 0
   BEGIN
      ROLLBACK TRANSACTION
   END;

   -- GET ERRORS DETAILS OR THROW ERROR

END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

Jika Anda tidak menggunakan blok TRY-CATCH dalam situasi tertentu, seperti situasi berikutnya, pernyataan pertama akan dikomit lagi:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [StackID] TINYINT PRIMARY KEY
);

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

EXEC 
(
    'INSERrrrrrT INTO [dbo].[StackOverflow] ([StackID]) VALUES (106);'
)

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

SELECT [StackID]
FROM [dbo].[StackOverflow];
person gotqn    schedule 06.11.2019
comment
Kesalahan kompilasi tidak dapat ditangkap karena mencegah seluruh batch berjalan, termasuk TRY / CATCH, jadi membungkus BEGIN TRY di seluruh transaksi tidak memiliki efek tambahan. Satu-satunya cara untuk CATCH kesalahan tersebut adalah dengan menempatkan seluruh kueri dalam EXEC dinamis, seperti yang ditunjukkan oleh contoh terakhir (yang memiliki kegunaannya, tetapi bukan sesuatu yang biasanya ingin Anda lakukan; lebih baik Anda mengontrol transaksi dari kode klien ). - person Jeroen Mostert; 06.11.2019
comment
@JeroenMostert Benar, maksud saya adalah jika Anda memiliki misalnya pernyataan T-SQL dinamis dan Anda melakukan kesalahan di sana (misalnya lupa spasi dan menggabungkan beberapa klausa selama pembuatan) menggunakan XACT yang disetel ke ON tidaklah cukup. - person gotqn; 06.11.2019
comment
Terima kasih banyak, @gotqn! - person Shahin Mehdipour Ataee; 06.11.2019

arrow_upward
0
arrow_downward

Anda perlu menambahkan SET XACT_ABORT ON; dan/atau menggunakan TRY/CATCH agar skrip tidak berlanjut ke COMMIT setelah terjadi kesalahan.

SET XACT_ABORT ON contoh:

SET XACT_ABORT ON;
begin transaction
   insert into instructor
   values ('99999', 'Yellow', 'Biology', 700000)

   insert into instructor
   values ('99999', 'Blue', 'Statistics', 85000)
commit;

Contoh COBA/TANGKAP:

BEGIN TRY
    begin transaction
       insert into instructor
       values ('99999', 'Yellow', 'Biology', 700000)
    
       insert into instructor
       values ('99999', 'Blue', 'Statistics', 85000)
    commit;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

Meskipun SET XACT_ABORT ON bersifat opsional dengan pola TRY/CATCH ini, saya tetap merekomendasikannya di semua kode T-SQL dengan transaksi eksplisit. Hal ini akan memastikan transaksi segera dibatalkan bahkan ketika batch dibatalkan secara manual atau karena waktu tunggu kueri habis ketika tidak ada kode blok CATCH yang dieksekusi. Jika tidak, transaksi yang belum dikomit akan tetap terbuka hingga koneksi ditutup atau digunakan kembali dengan pengumpulan koneksi.

person Dan Guzman    schedule 06.11.2019
comment
Mohon bisakah Anda memberi tahu mengapa Anda menyebutkan dan/atau menggunakan TRY/CATCH? Dalam contoh coba/tangkap Anda belum menggunakan xact_abort, jadi apakah ini opsional saat kami menggunakan coba/tangkap? - person variable; 20.02.2021
comment
@variable, saya menambahkan penjelasan pada jawaban saya. - person Dan Guzman; 20.02.2021