Discussion Locked

Implement a FIFO and a LIFO stack

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In this week's SQL Server newsletter, the author discusses why you may want to implement a stack and describes how to Implement a FIFO and a LIFO stack.

Are you already implementing stacks in SQL Server? If not, do you think you will after reading this tip? Let us know.

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

4 replies

by dan cooperstock

There's no guarantee in the code that you can't have two users pushing at once, who may both get the same new ItemNumber before they increment it. The 2nd one will then fail on the INSERT. The same type of problem occurs with the pop.

I'm not positive there is a completely reliable way to fix this. I think that including the code in a transaction helps, but I'm not really positive.

by sergey

IMHO, just placing a transaction isn't enough to obtain "tread-safety"
with (XLOCK) table hint probably should do.
Didn't try it though. it's kinda hard to simulate
concurr. conflict in simple tests :)

by sergey

dbo.FIFO_Push SP given in the article isn't safe in situation when there are no records in table.
It should have
------------
set @ItemNumber=1 --
TOP 1 @ItemNumber = ItemNumber FROM AssemblyLine
....
------------
to avoid @ItemNumber being NULL

One can even add
if @ItemNumber is NULL set @ItemNumber=1
to be perfectly safe.

This discussion is closed to new replies.