- Transaction Properties
- Transaction Types
- Transaction Syntax
- Transaction Tricks
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Transaction Types
SQL Server supports implicit and explicit transactions. By default, each INSERT, UPDATE, and DELETE statement runs within an implicit transaction. Suppose that we want to update the sales table in the pubs database by increasing the qty column by 6 for each record. Let's also suppose that there was a check constraint on the qty column, defined as follows:
ALTER TABLE [dbo].[sales] ADD CONSTRAINT [CK_sales] CHECK ([qty] < 80)
Now let's run the following update statement:
UPDATE sales SET qty = qty + 6
The statement will fail with the following error:
Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with COLUMN CHECK constraint 'CK_sales'. The conflict occurred in database 'pubs', table 'sales', column 'qty'. The statement has been terminated.
Even if there is a problem with a single row, none of the rows will be updated.
Similarly, if you try to delete all rows from the table, and the power goes out, SQL Server will roll back all changes, and none of the rows will be deleted.
Explicit transactions, on the other hand, must be specified by the programmer. Such transactions are included in BEGIN TRANSACTION ... COMMIT TRANSACTION block, as you saw in the beginning of this article. SQL Server 2000 also supports the WITH MARK option, compliant with ANSI SQL-92 syntax. The WITH MARK option lets you mark a transaction inside the transaction log. This way, you can restore a transaction log up to a certain transaction. Keep in mind, though, that you can mark only the first DML statement within a batch.