Transaction

Table of Contents



Every statement runs in a transaction. By default each statement is its own transaction, committed when it succeeds and rolled back when it fails. BEGIN TRANSACTION starts an explicit transaction that spans several statements until it is committed or rolled back. If an error occurs, the transaction is rolled back and the database is returned to its state before the transaction began.
  • The TRANSACTION keyword is required: BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION.
  • A session (connection) has at most one transaction at a time.
  • COMMIT TRANSACTION when there is no open transaction is an error. This can happen when the server rolled the transaction back, for example after choosing it as a deadlock victim.
  • Closing the connection rolls back any open transaction.


round-pushpin Begins an explicit transaction, deletes documents and then rolls the transaction back.
BEGIN TRANSACTION

DELETE FROM WordList:Word WHERE Text = 'Cat'
DELETE FROM WordList:Word WHERE Text = 'Catboat'
DELETE FROM WordList:Word WHERE Text = 'Catfish'

ROLLBACK TRANSACTION

After the rollback, the database is in the same state as it was before the transaction began. To keep the changes, use COMMIT TRANSACTION instead.
round-pushpin Inserts and commits.
BEGIN TRANSACTION
INSERT INTO WordList:Language (Id, Name) VALUES (20, 'Danish')
COMMIT TRANSACTION