Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transaction with no read lock

Author  Topic 

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-20 : 13:13:45
I have a code that does something like below

Begin transaction

Insert into Table A
Select * from Table 8

Commit Transaction

Now during the time the insert takes place the tables get lock and cant be even read..
Is there a way to be able to at least read the data in table B when the query is running


Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 13:45:57
You should look into RCSI isolation level. You could use the nolock hint, however the insert/select could get bad data due to the dirty reads.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-20 : 15:08:49
There is no process that could read or write into Table A during the transaction . Also there could be no process that could write data into Table B during the transaction. I could only have reads for table B while transaction runs.. Also I cannot go and change the read process because and apply nowait there.. So is there a way i could run this transaction in a way that the read on table B remains unlocked
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 15:37:42
Use the nolock hint on TableB for your insert/select.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -