SQLServer: What is implicit & explicit transaction

Question

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

Answer 1

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.

Answer 2

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