Stop Sql locking tables
Posted on 2011-09-12
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.