SerGioGio wrote:
> > begin tran
> > declare @my_id int
> > lock table counter in exclusive mode
> > update counter set my_id = my_id + 1
> > select @my_id = my_id from counter
> > insert into message values (@my_id, my_message) (<-- wherever you
> > get the my_message value from)
> > select @my_id
> > end
> >
>
> I do not understand the need for
> > lock table counter in exclusive mode
> In my understanding, the UPDATE statement is already locking the table.
> Am I missing something?

No, you are correct, it does lock the table... when it happens.

Locking the table here guarantees* that you won't end up with a
duplicate number. Without this statement, you still might run the
possibility (albeit very small) of grabbing a duplicate value. If you
have very high activity, of course the probablility goes up.

It is probably even more remote that this would happen with this
particular transaction, however, as the update is the first real DML in
the transaction. In my situation, I had a few selects before the
actual update, and all of this was rolled into a proc. The proc was
called maybe 2-3000 times a day, and twice a week on average, we would
run into situations where both processes of the multi-threaded app
would fire the proc off so closely together that they would retrieve
the same value.

If this table has low activity, and/or there's no chance of two apps
executing this at the same time, and you're willing to assume the
remote risk that a duplicate happens, then you can probably leave out
the lock table statement. But since the update is designed to lock the
table anyway, the lock table statement carries no additional
performance impact or the possibility of blocking, and it guarantees
you a unique value, as opposed to 99.995% of the time. Depends on your
situation I guess.

It's been several years since we encountered this issue, so it's hard
to remeber the exact circumstances of what we were doing and how we
implemented it, but I do remember it being a situation almost identical
to yours. We too originally thought that the lock placed by the update
command was sufficient to avoid duplicates, but it turned out not to be
the case, as I mentioned.

This was our experience anyway... YMMV.

-Mike