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.|