Solved

Stop Sql locking tables

Posted on 2011-09-12
15
335 Views
Last Modified: 2012-05-12
Hi Folks,

I have this scenario:

Batch process A is inserting records into table MyTable within a long transaction
Users B, C, D are reading records from that table.

Users B, C, D don't need to see the records that batch process A is writing until they are committed.

At the moment the users are locked out and their connection hangs until the batch process has committed.

Is there a locking hint on INSERT (or other method) that will guarantee that users B, C, D will be able to read records from MyTable without getting locked ?

I've tried "set transaction isolation level read uncommitted", it works but of course users can see the uncommitted transactions also.. don't want that !

I've looked at writing the records to a temp table but the problem is that the batch process needs to see its own records in its own transaction so adding in unions to temp tables in many very complex queries would create a lot of complexity and possibly destabilise the product, so a native sql method of doing this would be excellent.

thanks

0
Comment
Question by:plq
  • 8
  • 7
15 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36524280
<<Is there a locking hint on INSERT (or other method) that will guarantee that users B, C, D will be able to read records from MyTable without getting locked ?>>
Instead put a NOLOCK hint on the SELECT statement.
0
 
LVL 8

Author Comment

by:plq
ID: 36524309
Just tested this, and its the same as  "set transaction isolation level read uncommitted" which is already in the product

I really need to let users b, c, d read only the records that were in the table before the transaction started. Not the records that the transaction is saving.

thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36524329
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

Read this
http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

Hope this helps...
0
 
LVL 8

Author Comment

by:plq
ID: 36524377
Thanks for this.

Actually I've found another way:

     select * from MyTable with(readpast)

that does the trick - do you know if there's a corresponding isolation level for readpast ??

thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36524399
readpast skips any locked rows in the returned set.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36524422
if you need all commited rows use the solution I provided you.  In summary:
> nolock returns any rows commited or uncommitted
> readpast: see above
> ALLOW_SNAPSHOT_ISOLATION returns any commited rows (all of them)
0
 
LVL 8

Author Comment

by:plq
ID: 36524483
This is good and I'm getting close, just need to understand all this properly..

Does sql ever escalate its locks to a table lock ? Or can I guarantee that committed rows will always be readable this way ?

Why do you prefer ALLOW_SNAPSHOT_ISOLATION to READPAST ? The former seems to create a lot of traffic and even data IO in tempdb, whereas the latter is just operating on the table. I think that all uncommitted rows will be locked, so READPAST would work for me ??

thanks
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 36524548
<<Does sql ever escalate its locks to a table lock ?>>
Not always.  Depends on the operation.

<<Or can I guarantee that committed rows will always be readable this way ?>>
ALLOW_SNAPSHOT_ISOLATION guarantees all commited rows are always available for any select independently of required locking made for modifying the records.

<<Why do you prefer ALLOW_SNAPSHOT_ISOLATION to READPAST ?>>
For the exact reason I mentioned earlier.  READPAST will return incomplete set of committed rows.  ALLOW_SNAPSHOT_ISOLATION returns all of them

<<I think that all uncommitted rows will be locked, so READPAST would work for me ??>>
The exact purpose of ALLOW_SNAPSHOT_ISOLATION is to prevent that.  You asked the question

Is there a locking hint on INSERT (or other method) that will guarantee that users B, C, D will be able to read records from MyTable without getting locked ?

and ALLOW_SNAPSHOT_ISOLATION does that better and safer than READPAST.  No if you already set your mind on to using READPAST, just be aware that only the records that are locked won't be presented to select: htey will simply be skipped.  
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36525080
Hope you have all you need to answer your questions.
0
 
LVL 8

Author Comment

by:plq
ID: 36525297
Yes probably, you've been very helpful. I'll run some tests tomorrow morning before closing this off but this looks very good.

thanks
0
 
LVL 8

Author Comment

by:plq
ID: 36527818
OK this is not working for me.

TEST A :

In session 1:

ALTER DATABASE MyDB
SET ALLOW_SNAPSHOT_ISOLATION ON

begin tran
insert into a values (4)

In Session 2:

selecT * from a   -- hangs



TEST B :

In session 1:

ALTER DATABASE MyDB
SET ALLOW_SNAPSHOT_ISOLATION ON

In Session 2:

ALTER DATABASE MyDB
SET ALLOW_SNAPSHOT_ISOLATION ON

In session 1:

begin tran
insert into a values (4)

In Session 2:

selecT * from a   -- still hangs


I also tried this outside of any transactions:

ALTER DATABASE MyDB
SET READ_COMMITTED_SNAPSHOT ON

but this just hangs forever, I quit it after 15 minutes.


I've tried other combinations, so far I cannot get anything to work.

Do I still have to use nolock or readpast ??

thanks
0
 
LVL 8

Author Comment

by:plq
ID: 36528206
ok it looks like I have to do this on the user connections

set transaction isolation level snapshot

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36528232
Please look at the example provided in the link
0
 
LVL 8

Author Closing Comment

by:plq
ID: 36528267
thanks for helping
0
 
LVL 8

Author Comment

by:plq
ID: 36528275
and yes the example did the same but in the ADO.NET way - you open the transaction specifying snapshot

- connection1.BeginTransaction(IsolationLevel.Snapshot);

to test in ssms you use this as the equivalent -        

- set transaction isolation level snapshot



0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now