Are composited statements like this auto trasactionalized or must they be placed in a transaction?

DECLARE @ProjID int = 5024, @Text varchar(100) = 'test'
INSERT INTO ProjectNotes (ProjectID, Text, SortOrder)
SELECT @ProjID, @Text, (SELECT ISNULL(Max(SortOrder), 0) + 1 FROM ProjectNotes WITH (HOLDLOCK) WHERE ProjectID = @ProjID)

The HOLDLOCK here prevents another concurent insert from inserting the same next SortOrder value guaranteeing the the values in SortOrder are unique per ProjectID. The HOLDLOCK needs a transaction context to work but I am not sure if this entire INSERT statement including the sub SELECTs are transactionalized (within the context) by default or does this have to be wrapped in an explicit transaction.

In other words, without an explicity BEGIN TRANS, COMMIT TRANS how long does the HOLDLOCK hold the locks in this example. Does it hold them only for the MAX SELECT, or also during the outer INSERT?

I seem to recall that an MVP colleague tested and found that this was not sufficient to avoid collisions. But why not test yourself? Use Adam Machanic's SQLQueryStress,
http://www.datamanipulation.net/sqlquerystress/

However, I would recommend replacing HOLDLOCK with UPDLOCk, as HOLDLOCK will result in conversion deadlocks. An UPDLOCK serialises the processes on the SELECT instead.

I tried testing it but I could not get a conclusive answer manly because I can't exactly test percise timed collisions.

Should I use UPDLOCK in conjunction with HOLDLOCK or just by itself?

But my question was more about the transaction scope of the lock hint weather UPDLOCK or HOLDLOCK. The documentation states the lock is held "for the duration of the transaction defined by the statement that it is used in". My question is what is ment by the statement in my example. Is the lock held just for the SELECT MAX() statement or also for the outer INSERT statement that contains the SELECT statement. If only for the single SELECT, then shouldn't eveything be wrapped in a transaction?

• Edited by Perry Manole 8 minutes ago

I tried testing it but I could not get a conclusive answer manly because I can't exactly test percise timed collisions.

Should I use UPDLOCK in conjunction with HOLDLOCK? I think HOLDLOCK is needed for serialization.

But the main question was about the transaction scope of the lock hint. The documentation states the lock is held "for the duration of the transaction defined by the statement that it is used in". My question is what is ment by the statement in my example. Is the lock held just for the SELECT MAX() statement or also for the outer INSERT statement that contains the SELECT statement. If only for the single SELECT, then shouldn't eveything be wrapped in a transaction?

• Edited by Perry Manole Thursday, August 27, 2015 8:01 AM

I tried testing it but I could not get a conclusive answer manly because I can't exactly test percise timed collisions.

Should I use UPDLOCK in conjunction with HOLDLOCK? I think HOLDLOCK is needed for serialization.

But the main question was about the transaction scope of the lock hint. The documentation states the lock is held "for the duration of the transaction defined by the statement that it is used in". My question is what is ment by the statement in my example. Is the lock held just for the SELECT MAX() statement or also for the outer INSERT statement that contains the SELECT statement. If only for the single SELECT, then shouldn't eveything be wrapped in a transaction?

• Edited by Perry Manole Thursday, August 27, 2015 8:01 AM

I tried testing it but I could not get a conclusive answer manly because I can't exactly test percise timed collisions.

Should I use UPDLOCK in conjunction with HOLDLOCK? I think HOLDLOCK is needed for serialization.

But the main question was about the transaction scope of the lock hint. The documentation states the lock is held "for the duration of the transaction defined by the statement that it is used in". My question is what is ment by the statement in my example. Is the lock held just for the SELECT MAX() statement or also for the outer INSERT statement that contains the SELECT statement. If only for the single SELECT, then shouldn't eveything be wrapped in a transaction?

• Edited by Perry Manole Thursday, August 27, 2015 8:01 AM

I tried testing it but I could not get a conclusive answer manly because I can't exactly test percise timed collisions.

That's why I suggested SQLQueryStrees, which permits you spin up 100 parallel threads that hammers the database.

Should I use UPDLOCK in conjunction with HOLDLOCK? I think HOLDLOCK is needed for serialization.

Correct. UPDLOCK only implies REPEATABLE READ, while HOLDLOCK is the same as SERIALIZABLE. However, as soon there is already a row in the table, and all inserts are with this statement, UPDLOCK will suffice. The key-range locks required for serializable also tend to produce deadlocks.

But the main question was about the transaction scope of the lock hint. The documentation states the lock is held "for the duration of the transaction defined by the statement that it is used in". My question is what is ment by the statement in my example. Is the lock held just for the SELECT MAX() statement or also for the outer INSERT statement that contains the SELECT statement. If only for the single SELECT, then shouldn't eveything be wrapped in a transaction?

I would like to think that the lock would be held for the entire statement, but I seem to recall that I have seen reports of the opposite. This is why I suggested you to test.

Well on the last issue, I asked so I don't have to waste time to do my own tests which would require me to lean yet another tool and won't be sure if I am testing correcly. I was hoping to benefit from the experience of others.

Have you looked at using a CREATE SEQUENCE? 

No I have not looked are CREATE SEQUENCE. I don't see what this has to do with anything I asked.