Solved

Stop Sql locking tables

Posted on 2011-09-12
15
338 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server connect issues 4 37
SSIS GUID Variable 2 30
SQL works but want to get the XML node data separately 11 25
SQL Server Compression Decision 5 37
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

713 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