Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #486

Re: multiple rows changes and consistency

Show all headers | View raw

migurus (migurus@yahoo.com) writes:
> The biggest concern is to make sure that
> a) nobody does updates to runs 1 and 2 while the update steps 1
> through 6 are running
> b) nobody could read a state of the runs while updates are in progress
> 
> One posibility is to use sp_getapplock to serialize all access, but
> that is not ANSI, and we would like to stay away from db-specific
> solutions.
 
Moreover, sp_getapplock would apply to that procedure only. I don't know, 
but it seems to me that there would be several paths of access in this case.
Also, an application lock on the lock would mean that while you are working 
with runs 1 and 2, no one can work with runs 12 and 13.

I think you should start the transction with something like:
  
   SELECT * INTO #temp FROM Stops WITH (UPDLOCK) WHERE RUN_ID IN (1, 2)
   DROP TABLE #temp

The temp table here is of no interest in itself. What you do is that you 
place an update lock on the runs you plan to modify. An update lock is a
shared lock which states an intent to update. Other process can still
read, but no can update the data. Or take a second update lock on the
resource. 

As written, this gives you REPEATABLE READ. This means that none of 
the data you have read can be changed. However, it still possible 
for another process to insert new trips of stops in the runs.

To get protection against such phantoms (as they are called), you can 
change the locking hint to (UPDLOCK, SERIALIZABLE). There are two drawbacks
with this. One is that your lock may be too wide. You may also block 
another process to insert a new trip first in run 3. The other drawback
is that the serliasable isolation level is prone to create deadlock 
situations.

You semed to be concerned about portability, and UPDLOCK is indeed a
Microsoft-specific hint. You can to without it, but in that case you
will see a lot of conversion deadlocks, where two processes both 
were able to lock the same trip and then tries to continue with an
update.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

multiple rows changes and consistency migurus <migurus@yahoo.com> - 2011-06-28 18:28 -0700
  Re: multiple rows changes and consistency Erland Sommarskog <esquel@sommarskog.se> - 2011-06-30 00:00 +0200

csiph-web