How to force after insert trigger to execute just after its insert, before any other insert is executed?

Question

This problem is about concurrent high speed inserts. I must admit it is very interesting in my eyes.

I'm using SQL Server 2008 R2 executing T-SQL inserts and after insert trigger.

I would like to make sure that no command will execute between an insert and its after insert trigger.

Using isolation levels causes deadlocks or just does NOT solve the problem.

The procedure I'm using is the answer/solution from Phil to SQL Server dependent Identity - is there such a thing?

The problem is:

sometimes an insert get in between a previous insert and its after insert trigger, causing this result:

RoomID  ItemID  ItemDescription ID
------  ------  --------------- --
7       1       Door             1
7       2       Window (West)    2
7       3       Window (North)   3
8       1       Door             4
8       2       Table #1         5
8       3       Table #2         6
7       4       Table #1         7
8       4       Chair #1         8
7       6       Table #2         9
7       5       Table #3        10
8       5       Chair #2        11

See IDs #9 and #10. Thair ItemIDs are switched. The ItemID should have been 5 and 6 respectively and not 6 and 5, but the 10th insert probably occurred before the after insert trigger of #9 finished execution.

This problem happens less than 0.5% of the inserts: 2 switches involving 4 records for 1000 inserts or less. Yes, sometimes there are no switches occurrences.

Raising the isolation level by one step does not help, and even cause more keys/dependent-keys switches from time to time. Raising two isolation levels up caused deadlocks.

Lowering the isolation level reduces switches, but they are still created.

Starting raised isolation level before the every insert and moving back to the default isolation level at the end of the trigger causes deadlocks (in my experiments all inserts did not commit!).

Do anyone see a way out?

How to force an insert and its after insert trigger to execute together, prohibiting other inserts to the same table in between?

Answer 1

How about using an instead of insert trigger?

Of course, you will have to make the actual insert yourself, but that's the supported behavior in SQL Server. There are no before insert triggers or something similar.

Anyway, you can still use your current trigger processing logic in this type of trigger as well.

To try and imagine a better solution, can you explain what you you are trying to do? Are multiple threads inserting in this table? Is this a real-time process?

It's auction like. Yes, multiple threads are inserting in this table. The problem is rear but happens. No reasons for complaints yet, but there may be (the switches happened by now where not at the critical point). Users who initiate inserts need to know what is their precedence in a list of orders. The ItemID is used almost imminently, therefore, may not be calculated on the fly (with select count(*)... where..) later on when needed a matter of milliseconds. Wrong order of ItemID may cause losses to one and non rightful gains for another.

Well, multithreaded inserts are almost never a good idea in SQL server. At least not without some sort of synchronization.

You need some kind of queuing. For example, I have a similar system where need to insert between 2.5-3 million records per day (well, working hours).

At first I also used 8-16 threads to do my inserts directly on the database. And I noticed exactly this behavior: deadlocks. So I start thinking about how I could queue these messages so only 1 thread (1 connection) will be inserting at any time.

I ended up queuing these records in MSMQ (transactional). Some other process comes and takes them from here (also transactional) and inserts them in batches in the database. My records are guaranteed to come in the correct order, the one in which they were sent.

So this secondary process inserts batches of records and, as you, I need some pre-processing before inserting, which I'm doing with an instead-of-insert trigger. There I can update the entire inserted table "quietly", without worrying that someone can come and mess my stuff.

It's just an idea. You may also want to consider Service Broker for queuing, especially if you don't want to take the processing outside SQL Server.

Also, something worth trying are transactions with Snapshot Isolation level and row versioning, but I recommend going the MSMQ/Service Broker way.