?
Solved

Stop Sql locking tables

Posted on 2011-09-12
15
Medium Priority
?
342 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 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