XACT_ABORT & XACT_STATE
Transaktioner och felhantering i SQL Server
Miriam Antona
Software Engineer
XACT_ABORT
Anger om den aktuella transaktionen automatiskt återställs när ett fel uppstår.
SET XACT_ABORT { ON | OFF }
SET XACT_ABORT OFF
- Standardinställning
- Vid fel: Transaktioner kan förbli öppna
SET XACT_ABORT ON
- Vid fel: Återställer transaktionen och avbryter körningen
XACT_ABORT – exempel
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 – exempel
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 MED 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 med 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 -> ingen öppen transaktion
- 1 -> öppen och committbar transaktion
- -1 -> öppen och icke-committbar transaktion (dömd transaktion)
- kan inte committas
- kan inte återställas till en savepoint
- kan återställas helt
- kan inte ändra data, men kan läsa data
XACT_STATE – öppen och committbar
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 – öppen och committbar
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
| 14 | Mark | Davis | [email protected] | 555909090 |
XACT_STATE – öppen och icke-committbar (dömd)
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 – öppen och icke-committbar (dömd)
SELECT * FROM customers WHERE first_name = 'Mark';
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
Nu kör vi en övning!
Transaktioner och felhantering i SQL Server
