C# Gnu (10/16/2012)
GilaMonster (10/16/2012)
C# Gnu (10/16/2012)
Addition: so this in effect was my worry - that systems would be locked out while the transaction is openIf they try to read rows that are locked, they have to wait until the locks are released.
Hmm, Seems like an option is missing to me!
BEGIN TRANSACTION NOBLOCK
That's the read uncommitted isolation level, read committed snapshot isolation level and snapshot isolation level and the readpast locking hint depending exactly what behaviour you want.
I would have thought the default 'ISOLATION LEVEL COMMITED' select statement should return all committed rows and ignore any uncommitted rows - rather than wait forever.
So what happens when you select while rows are being update? Just not read them because they're currently locked for update? That would be interesting in terms of inconsistent results.
If you want that behaviour, you use the readpast hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability