Design for implicit and explicit transactions

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.

Continue reading


Design for concurrency

May include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes

Most of this objective is covered in the previous post there’s simply too much overlapping in this part of the exam, as far as I can grab it. Anyway, there are some other interesting issues with locking and concurrency, so here’s a brief post on the remainders.

There are two data types related to concurrency – rowversion and timestamp. Rowversion is a binary data type which stores eight bytes, and increments automatically on every insert and update. Note that each table can have only one rowversion column, and you must give a name for it (you don’t have to specify a name for a timestamp value). Rowversion type is useful when you expecting multiple updates in roughly the same time, but I’ve stumbled into it when used LINQ to SQL along with WCF, too.

Continue reading

Design a locking granularity level

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:

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

Continue reading

Manage transactions


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.

Continue reading

Manage transactions

Transactions are data operations that need to succeed or fail as a group. The usual example is the money transfer: the given amount must be subtracted from an account, and added to another. If an exception occurs during the transfer, everything should be rolled back. No one would tolerate money which evaporates on the internet…

If you’d like to write a transaction manually in C#, using the .NET Framework, there is nothing in your way. You should use the DbTransaction base class, or one of its provider-specific derivatives to implement your custom transaction logic. There are three steps to follow here:

  1. Create the transaction
  2. Execute the commands which are parts of the transaction
  3. Commit or roll back the transaction, depending the outcome

A quick example:

SqlConnection theConnection = new SqlConnection(theConnectionString);
SqlCommand theCommand = new SqlCommand(theCommandText, theConnection);
SqlCommand theCommand2 = new SqlCommand(theCommandText2, theConnection);
using (theConnection)
SqlTransaction theTransaction = theConnection.BeginTranscation();
theCommand.Transaction = theTransaction;
theCommand2.Transaction = theTransaction;
catch(Exception ex)

Continue reading