Re: concurrency problem with lists ("check constraint" on groups of rows)

B D Jensen (bjorn.d.jensen@xxxxxxxxx) writes:
I made an new example below, but I didn't fully understand why to
select on myGroup only with UPDLOCK, why not on myGroupMember also?
But it seems to work. Before I fix this in production: please explain!

An Update lock is a read-lock. That is, it does not block readers. But
you cannot get an update-lock on a resource if there is an exclusive
lock on it (of course), and neither can you get an update-lock on a
resource if there is already an update-lock on it.

The resource here in this case is the current max value of myGroupID. If you
have read 5, the lock guarantees that you will read 5 next time you read
the row as well.

However, the lock itself does not guarantee that SELECT MAX will return
the same value next time. Someone could insert an ID of 87 outside the
procedure and not be blocked by this update-lock. To prevent this, you
would need HOLDLOCK as well, that is serializable. But since serializable
is very prone to deadlocks, this is something you want to avoid. And it
seems a very reasonable assumption that the only piece of code that ever
will insert rows into myGroups is this procedure.

You don't need any such locks on myGroupMembers, since by having this lock
in the beginning you have created a serialization point. And it's difficult
to grab a meaningful lock in myGroupMembers without using serializable.

While the technique with UPDLOCK is common, one could argue that in this
case it would be cleaner to use an application lock instead.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.