Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stop Sql locking tables

Posted on 2011-09-12
15
Medium Priority
?
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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