question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked ErlandSommarskog commented

SQLServer: What is implicit & explicit transaction

I found a two solution to use table as queue. second solution said there is no transaction. that means first one has transaction.

first solution code has insert & update both. second set code one has composed in such a way as a result insert & update are happening at same time or in one transaction.

see 1st set of code


 -- Find the next queued item that is waiting to be processed
 DROP TABLE IF EXISTS #process;
 CREATE TABLE #process (ID INT);
 INSERT INTO #process
 SELECT TOP (25) ID
   FROM MyQueueTable WITH (UPDLOCK, READPAST)
  WHERE StateField = 0
  ORDER BY ID ASC;
    
 -- if we've found one, mark it as being processed
 IF EXISTS (SELECT TOP (1) ID FROM #process)
    BEGIN
     UPDATE MyQueueTable
        SET Status = 1 
       FROM MyQueueTable AS mqt 
       JOIN #process AS p
         ON mqt.ID = p.ID;
    END;

Second set of code


 DROP TABLE IF EXISTS #process;
 CREATE TABLE #process (ID INT);
    
 -- Find the next queued item that is waiting to be processed
 UPDATE mqt
 SET Status = 1
 OUTPUT inserted.ID
   INTO #process (ID)
 FROM (
     SELECT TOP (25) *
     FROM MyQueueTable WITH (READPAST)
     WHERE StateField = 0
     ORDER BY ID ASC
 ) AS mqt;

why second set of code consider as implicit transaction ?

i am looking for two sample scenario....first one will tell me what is explicit transaction and second one will tell me what is implicit transaction.

looking for guide line. thanks

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Implicit and explicit transactions as such has nothing to do with queue tables really.

Explicit transaction: you say BEGIN TRANSACTION, and then COMMIT TRANSACTION to commit it.

Implicit transactions are something which normally are used in SQL Server: If you don't say BEGIN TRANSACTION, the engine starts a transaction for anything you perform be that SELECT, INSERT, CREATE USER whatever. Then you still need to commit that transaction with COMMIT TRANSACTION. Implicit transaction is mandated by ANSI and is the default (and probably the only option in many products). In SQL Server, you need to issue the command SET IMPLICIT_TRANSACTIONS ON to get this functionality. There is very little reason to do this - implicit transactions are very alien in the SQL Server world.

The default in SQL Server when there is no explicit BEGIN TRANSACTION is that each statement is its own transaction. That is known as autocommit (which is what you actually had in mind when you said "implicit transaction", but I like to get the terminology right.)

Of the examples above, you must have explicit BEGIN TRANSACTION around the two statements in the first example. Without BEGIN TRANSACTION, the UPDLOCK taken in the first SELECT would be released before the UPDATE is executed, which means that two concurrent process could get the same row.

The second attempts to do all in a single statement, so the lack of BEGIN TRANSACTION should not matter here. Whether it actually works, I don't want to vouch on, because these things are difficult to analyse, and you have to do some heavy load testing to prove that your scheme works.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sir here you said UPDLOCK taken in the first SELECT would be released before the UPDATE is executed

if i want update lock should release after update then should i put that code with in BEGIN TRAN AND Commit TRAN ? if use HOLD Lock instead of
UPDLOCK then what will happen ? in this case also UPDLOCK would be release before update data ?

please share some knowledge. thanks

0 Votes 0 ·

if i want update lock should release after update then should i put that code with in BEGIN TRAN AND Commit TRAN

Yes. I did say so in my post.

if use HOLD Lock instead of UPDLOCK then what will happen ?

Things will break down completely. There will be deadlocks and all sorts of problems. Do not use HOLDLOCK.

JHOLDLOCK is a synonym for SERIALIZABLE, and SQL Server will take range locks. On the other hand, you will only get shared locks, so concurrent processes can both run the first statement. Whereupon they will clash on the second statement.

UPDLOCK on the other hand, is a read lock which can only be held by one process, so if there are two concurrent processes, the second process would be blocked if the READPAST hint had no been there before. The READPAST hint permits the second process to move on to next available row.

Once locks have been taken inside a transaction they are held until the transaction commits.

1 Vote 1 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

I suggest you read this for queues:
https://rusanu.com/2010/03/26/using-tables-as-queues/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.