Solved

Stop Sql locking tables

Posted on 2011-09-12
15
334 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
Comment Utility
<<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
readpast skips any locked rows in the returned set.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
Comment Utility
<<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
Comment Utility
Hope you have all you need to answer your questions.
0
 
LVL 8

Author Comment

by:plq
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Please look at the example provided in the link
0
 
LVL 8

Author Closing Comment

by:plq
Comment Utility
thanks for helping
0
 
LVL 8

Author Comment

by:plq
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

11 Experts available now in Live!

Get 1:1 Help Now