CODEWITHSUNDEEP
sqlsql-servertriggersatomictransaction-isolation
Rumpelstinsk
Rumpelstinsk

Reputation: 3241

SQL Server: Atomic Trigger

I have two quick questions.

  1. Are SqlServer Triggers atomic by default?
  2. How to achieve it if not?

The environment is simple. I have two users (let's call them UserA and UserB) with two different connections to the database. Both connections are active at the same time.

When UserA inserts a row in a specific table, a trigger is fired (let's call it TriggerAtomic). I need to ensure that when the TriggerAtomic is fired, UserB can't execute anything until TriggerAtomic has finished. Obviously no other triggers, functions, procedures... can be executed until the TriggerAtomic finishes.

Upvotes: 3

Views: 2034

Answers (2)

MarredCheese
MarredCheese

Reputation: 20831

Yes, triggers are atomic in that they are executed in the same transaction as their parent query, and they either commit or roll back together. The parent query's transaction could be explicit, as suggested in Tab Alleman's answer, but that's unnecessary for a single statement since if you don't explicitly wrap the query in a transaction, you will get an implicit one, as discussed in the answers to this question.

Ok, but let's back up for a minute. You used the term atomic, but from your description, it seems that your actual goal is isolation rather than atomicity. It's a confusing subject because atomicity means two completely different things in the worlds of concurrent programming and databases:

  • In programming, atomicity pertains to mutual exclusion. A series of actions is atomic if it is uninterruptible by other processes. In the database world, that concept is instead referred to as isolation. Adding further confusion, atomicity is typically a binary concept, whereas database transaction isolation exists on a spectrum.

  • With databases, atomicity pertains to whether a partial failure is allowed among a series of actions. If a transaction is atomic, then either the entire sequence succeeds or none of it does. In-between states in which some actions succeeded and some failed are not permitted. This has nothing to do with excluding other users from resources while the sequence of actions is taking place.

In this case, the transaction might be adequate (such as if it is set to the strictest isolation level of "serializable"). But I'm no expert on isolation, and I'm not sure of the details of your situation. Various types of locks are another option.

More info:

  • Another question in which someone was asking for atomicity when they really needed isolation. Apparently, using a lock was recommended in that particular situation.
  • A great article (actually the first of a whole series) explaining the myths and confusion surrounding atomicity and the other characteristics of databases making up the acronym ACID
  • SQL Server Transaction Isolation Levels

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

Put a transaction around the INSERT statement, and commit it afterwards. SQL Triggers operate in the transaction of the statement that triggered them.

Upvotes: 5

Related Questions