May include but is not limited to: nested transactions, savepoints, TRY/CATCH
A transaction is an atomic, consistent, isolated, durable operation – this is best known as the acronym ACID. There are two kinds of transactions: explicit – this is what you refer as a transaction. It starts with the BEGIN TRANSACTION clause, and ends with a COMMIT / ROLL BACK – what you prefer. There are also implicit transactions. These are enabled when you connect SQL Server with implicit transactions enabled. By this, some clauses automatically start transactions (CRUD operations, DDL statements, etc.) which you can commit or roll back immediately.
To enable implicit transactions, you call the SET IMPLICIT_TRANSCATIONS ON statement. Remember – a transaction either succeeds or fails, so this mode holds some surprises to you.
Nothing can hold you back from nesting transactions. There are some rules, although. The first rule is that the outmost transaction always wins. So if you commit every nested transaction, but roll back the outmost one, every inner transaction is rolled back. The reverse is true.
May include but is not limited to: locking hints, memory consumption
SQL Server has a multigranural locking feature, which lets you lock resources on different levels. Here’s a brief table of them:
|RID||A row identifier to lock rows within a heap.|
|KEY||A row lock within an index.|
|PAGE||A whole page locked in the database (8 KB).|
|EXTENT||8 continuous pages.|
|HoBT||A heap or B-tree, depending on the existence of a clustered index.|
|TABLE||An entire table locked down.|
|FILE||A whole database file.|
|APPLICATION||An application-specified resource.|
|METADATA||Locks in metadata.|
|ALLOCATION_UNIT||An allocation unit.|
|DATABASE||An entire database.|
May include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL
A transaction is a group of related tasks, which must succeed or fail as a unit. A transaction is referred as an ACID operation. ACID stands for Atomicity, Consistency, Isolation and Durability.
To start a transaction, simply write BEGIN TRANSACTION. You can optionally specify a transaction name for it. To end it successfully, use the COMMIT clause, to undo the changes, use the ROLLBACK clause, along with the optional name of the transaction. Anything between the BEGIN TRAN(SACTION) and the ending clause is considered to be part of it.
In SQL Server, we distinct some kinds of transactions. There are explicit transactions, which are explicitly marked with the BEGIN TRAN keyword. There are also implicit transactions, which can be used after calling the SET IMPLICIT_TRANSACTIONS ON statement. After this, any SQL statement will be executed as a transaction.
A nice feature to use is SET XACT_ABORT. By default, if a run-time statement error occurs in a SQL statement, only that statement is rolled back. If you specify SET XACT_ABORT, the whole batch will be rolled back instead of just the one causing the error.