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