XACT_ABORT & XACT_STATE
Transacties en foutafhandeling in SQL Server
Miriam Antona
Software Engineer
XACT_ABORT
Bepaalt of de huidige transactie automatisch wordt teruggedraaid bij een fout.
SET XACT_ABORT { ON | OFF }
SET XACT_ABORT OFF
- Standaardinstelling
- Bij een fout: er kunnen open transacties blijven
SET XACT_ABORT ON
- Bij een fout: draait de transactie terug en stopt de uitvoering
XACT_ABORT - voorbeelden
SET XACT_ABORT OFF; --Default setting
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;
(1 row affected)
Msg. 2627, Level 14, State 1, Line 5
Violation of UNIQUE KEY 'unique_email'...
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
| 14 | Mark | Davis | [email protected] | 555909090 |
XACT_ABORT - voorbeelden
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;
Msg. 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY 'unique_email'...
SELECT * FROM customers WHERE first_name = 'Mark';
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
XACT_ABORT MET RAISERROR
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
RAISERROR('Raising an error!', 16, 1);
INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');
COMMIT TRAN;
Msg. 50000, Level 16, State 1, Line 5
Raising an error!
SELECT * FROM customers WHERE first_name IN ('Mark', 'Zack');
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
| 14 | Mark | Davis | [email protected] | 555909090 |
| 15 | Zack | Roberts | [email protected] | 555919191 |
XACT_ABORT met THROW
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
THROW 55000, 'Raising an error!', 1;
INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');
COMMIT TRAN;
(1 rows affected)
Msg. 50000, Level 16, State 1, Line 5
Raising an error!
SELECT * FROM customers WHERE first_name IN ('Mark', 'Zack');
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|-------|-------|
XACT_STATE
XACT_STATE()
- 0 -> geen open transactie
- 1 -> open en te committeren transactie
- -1 -> open en niet te committeren transactie (doomed)
- kan niet committen
- kan niet terugdraaien naar een savepoint
- kan de volledige transactie rollbacken
- kan geen wijzigingen maken/kan data lezen
XACT_STATE - open en te committeren
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
ROLLBACK TRAN;
IF XACT_STATE() = 1
COMMIT TRAN;
SELECT ERROR_MESSAGE() AS error_message;
END CATCH
| error_message |
|---------------------------------------------|
| Violation of UNIQUE KEY 'unique_email'... |
XACT_STATE - open en te committeren
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
| 14 | Mark | Davis | [email protected] | 555909090 |
XACT_STATE - open en niet te committeren (doomed)
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
ROLLBACK TRAN;
IF XACT_STATE() = 1
COMMIT TRAN;
SELECT ERROR_MESSAGE() AS Error_message;
END CATCH
XACT_STATE - open en niet te committeren (doomed)
SELECT * FROM customers WHERE first_name = 'Mark';
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
Laten we oefenen!
Transacties en foutafhandeling in SQL Server
