SQL Server Transactions and Error Handling

จัดการ transactions ใน sql server

สมมุติ:

• สร้าง table ชื่อ test_1 มี column อันเดียวชื่อ id และ เป็น primary key ด้วย
• ลือบอกไปสมมุติให้ database ชื่อ SAMPLE_DB
• ให้ id 020 มีอยู่แล้ว เพื่อจะทดสอบให้เกิด error
  

# Sample 1 => การใช้ transaction ใน storeprocedure

USE SAMPLE_DB

-- ประกาศตัวแปรไว้เพื่อรับค่าจาก @@ERROR
DECLARE @intErrorCode INT

-- เริ่ม transaction
BEGIN TRAN

INSERT INTO [sample].[dbo].[test_1] ([id]) VALUES ('020')

-- select ค่า error จากประโยค insert ด้านบน จะไม่เท่ากับ 0 เมื่อ insert ไม่ได้
SELECT @intErrorCode = @@ERROR

-- ถ้า error ไม่ใช่ 0 (มีการเกิด error นั่นเอง) ให้ไปที่ label ชื่อ problem โดยข้าม commit ไปเลยเห็นปะ
IF (@intErrorCode <> 0) GOTO PROBLEM

-- select ค่า error จากประโยค insert ด้านบน จะไม่เท่ากับ 0 เมื่อ insert ไม่ได้
INSERT INTO [sample].[dbo].[test_1] ([id]) VALUES ('020')

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

-- commit จะไม่ทำงานเลยถ้าเกิด error ด้านบน เพราะเมื่อเกิด error เค้าจะข้ามไปที่ problem label
-- ซึ่ง problem label อยู่ด้านล้าง commit อ่ะนะ
COMMIT TRAN

-- label problem จะถูกเรียกเมื่อเกิด error ทุกครั้งไปในที่นี้นะ
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END -- end if

# Sample 2 => อีกตัวอย่างที่น่าสนใจ ใช้ try catch ใน procedure

USE SAMPLE_DB

BEGIN TRY

INSERT INTO [sample].[dbo].[test_1] ([id]) VALUES ('020')
print 'insert complete'

END TRY
BEGIN CATCH

print 'insert error'

END CATCH

ข้อสังเกต

# เราต้อง เช็ค error หลังประโยค insert ทั้งสองประโยคเลย

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

# เป็นเพราะ ถ้า insert แรก error และ insert สอง ไม่ error จะทำให้ @intErrorCode ที่ select อยู่หลัง insert ที่สอง เป็น 0 และจะทำการ commit ในที่สุดนั้นเอง

# ไปดูการโค้ด transac รูปแบบที่ดีกว่าตัวอย่างด้านบน http://www.4guysfromrolla.com

BEGIN TRY

 BEGIN TRANSACTION    -- Start the transaction

-- DO your work like INSERT, UPDATE, DELETE



-- If we reach here, success!

COMMIT -- commit the transaction

END TRY

BEGIN CATCH

-- Whoops, there was an error

-- คือ ถ้าใน begin tran ถ้ามี exception จะเด้งมาที่ catch จะทำให้ @@trancount เป็น 1
IF @@TRANCOUNT > 0

 ROLLBACK

END CATCH

Notes:

• อันนี้เป็น procedure ของ MSSQL
  
• MySQL น่าจะทำคล้ายกันนะ
• รู้สึกว่า try catch จะเหมือนเขียนใน app เลยแฮะ
• สรุปเราควรใส่ SELECT @intErrorCode = @@ERROR ทุกครั้งหลัง statement ประเภท update ทุกชนิด (insert, update, delete) ของ MSSQL นะส่วน MySQL ไม่รู้เหมือนกัน
• เมื่อเรา begin tran แล้วต้องตามด้วย commit หรือ rollback ด้วย ถ้าไม่มี transaction อื่นจะไม่สามารถใช้งานใน table ที่กำลังทำอยู่ใน tran ที่ไม่ commit หรือ rollback มันจะ lock table หรือ ข้อมูลใน table นั้น
  

References:

• http://www.codeproject.com/KB/database/sqlservertransactions.aspx
• http://technet.microsoft.com/en-us/library/ms175976.aspx
• http://www.dotnetfunda.com/codes/...-stored-procedure.aspx
• http://www.4guysfromrolla.com/webtech/041906-1.shtml