เหตุใดธุรกรรม t-sql ต่อไปนี้จึงไม่ทำงานตามที่คาดไว้

เพื่อทดสอบว่าธุรกรรมทำงานอย่างไร ฉันเขียน t-sql ต่อไปนี้ เนื่องจากแอตทริบิวต์แรกเป็นคีย์หลัก จึงไม่ควรคอมมิตการแทรกใดๆ แต่การแทรกครั้งแรกเกิดขึ้นแล้วเหรอ? ทำไม

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 แถวได้รับผลกระทบ) ข่าวสารเกี่ยวกับ 2627 ระดับ 14 สถานะ 1 บรรทัด 100 การละเมิดข้อจำกัดคีย์หลัก 'PK__instruct__3214EC278C8DA99F' ไม่สามารถแทรกคีย์ที่ซ้ำกันในวัตถุ 'dbo.instructor' ค่าคีย์ที่ซ้ำกันคือ (99999) แถลงการณ์ได้สิ้นสุดลงแล้ว

เวลาดำเนินการเสร็จสิ้น: 2019-11-06T14:02:27.3436411+02:00


sql-server transactions
person Shahin Mehdipour Ataee    schedule 06.11.2019    source แหล่งที่มา
comment
คุณจะได้รับผลลัพธ์นี้หาก XACT_ABORT เป็น OFF เนื่องจากการละเมิดคีย์ที่ซ้ำกันจะยกเลิกการแทรกครั้งที่สองเท่านั้น และไม่ใช่ธุรกรรมโดยรวม ดังนั้น COMMIT จึงยังคงเกิดขึ้น หากคุณทำ SET XACT_ABORT ON ก่อน การคอมมิตจะไม่เกิดขึ้น กฎเกณฑ์ที่แน่นอนสำหรับสิ่งที่ทำและไม่ยกเลิกธุรกรรมนั้น ซับซ้อนมาก แม้กระทั่ง ถ้า XACT_ABORT สามารถทำให้สิ่งต่าง ๆ ง่ายขึ้นได้มาก   -  person Jeroen Mostert    schedule 06.11.2019

คำตอบ (2)


arrow_upward
0
arrow_downward

เพราะเมื่อ XACT_ABORT ปิดอยู่ (ซึ่งเป็นค่าเริ่มต้น):

เมื่อ SET XACT_ABORT ปิดอยู่ ในบางกรณี เฉพาะคำสั่ง Transact-SQL ที่ทำให้เกิดข้อผิดพลาดเท่านั้นที่จะถูกย้อนกลับ และธุรกรรมยังคงประมวลผลต่อไป

และเมื่อเป็น ON:

.. ถ้าคำสั่ง Transact-SQL ทำให้เกิดข้อผิดพลาดรันไทม์ ธุรกรรมทั้งหมดจะถูกยกเลิกและย้อนกลับ

นั่นคือสิ่งที่เราต้องเปลี่ยนให้เป็น ON และหากคุณลองใช้โค้ดด้านล่าง คุณสามารถตรวจสอบสิ่งนี้ได้:

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];

นอกจากนี้ โปรดทราบว่า:

ข้อผิดพลาดในการคอมไพล์ เช่น ข้อผิดพลาดทางไวยากรณ์ จะไม่ได้รับผลกระทบจาก SET XACT_ABORT

ข้างต้นหมายความว่า หากคุณต้องการให้มีธุรกรรมอัตโนมัติจริงๆ คุณต้องใช้บล็อคโค้ดต่อไปนี้:

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;

หากคุณไม่ได้ใช้ TRY-CATCH block ในสถานการณ์เฉพาะ เช่นเดียวกับสถานการณ์ถัดไป คำสั่งแรกจะถูกคอมมิตอีกครั้ง:

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
ไม่สามารถตรวจพบข้อผิดพลาดในการคอมไพล์ได้ เนื่องจากจะทำให้ทั้งชุดทำงานไม่ได้ รวมถึง TRY / CATCH ด้วย ดังนั้นการล้อม BEGIN TRY รอบธุรกรรมทั้งหมดจึงไม่มีผลเพิ่มเติม วิธีเดียวที่จะ CATCH ข้อผิดพลาดดังกล่าวคือการใส่แบบสอบถามทั้งหมดในรูปแบบไดนามิก EXEC ดังตัวอย่างสุดท้ายที่แสดง (ซึ่งมีการใช้งาน แต่ไม่ใช่สิ่งที่คุณต้องการทำโดยทั่วไป คุณควรควบคุมธุรกรรมจากรหัสลูกค้าดีกว่า ). - person Jeroen Mostert; 06.11.2019
comment
@JeroenMostert จริงอยู่ ประเด็นของฉันคือถ้าคุณมีคำสั่ง T-SQL แบบไดนามิกและคุณทำอะไรผิดที่นั่น (เช่นลืมพื้นที่และต่อส่วนคำสั่งสองสามส่วนระหว่างการสร้าง) การใช้ XACT ที่ตั้งค่าเป็นเปิดนั้นไม่เพียงพอ - person gotqn; 06.11.2019
comment
ขอบคุณมาก @gotqn! - person Shahin Mehdipour Ataee; 06.11.2019

arrow_upward
0
arrow_downward

คุณต้องเพิ่ม SET XACT_ABORT ON; และ/หรือใช้ TRY/CATCH เพื่อให้สคริปต์ไม่ไปที่ COMMIT หลังจากเกิดข้อผิดพลาด

SET XACT_ABORT ON ตัวอย่าง:

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

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

ลอง/จับตัวอย่าง:

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;

แม้ว่า SET XACT_ABORT ON จะเป็นตัวเลือกสำหรับรูปแบบ TRY/CATCH นี้ แต่ฉันยังคงแนะนำให้ใช้กับโค้ด T-SQL ทั้งหมดที่มีธุรกรรมที่ชัดเจน สิ่งนี้จะช่วยให้มั่นใจได้ว่าธุรกรรมจะถูกย้อนกลับทันที แม้ว่าแบทช์จะถูกยกเลิกด้วยตนเอง หรือเนื่องจากการหมดเวลาของแบบสอบถาม โดยที่ไม่มีการเรียกใช้โค้ดบล็อก CATCH มิฉะนั้น ธุรกรรมที่ไม่มีข้อผูกมัดจะยังคงเปิดอยู่จนกว่าการเชื่อมต่อจะถูกปิดหรือนำกลับมาใช้ใหม่พร้อมกับการรวมการเชื่อมต่อ

person Dan Guzman    schedule 06.11.2019
comment
โปรดช่วยแนะนำหน่อยได้ไหมว่าทำไมคุณถึงพูดถึงและ/หรือใช้ TRY/CATCH ในตัวอย่าง try/catch คุณไม่ได้ใช้ xact_abort ดังนั้นจะเป็นทางเลือกหรือไม่เมื่อเราใช้ try/catch - person variable; 20.02.2021
comment
@variable ฉันเพิ่มคำอธิบายให้กับคำตอบของฉัน - person Dan Guzman; 20.02.2021