Tuesday, February 24, 2009

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:

No comments:

Post a Comment