Hello Friends,

I have a scenario as mentioned below,

TableA,TableB,TableC

I am taking records by joining the TableA and TableB then inserting into TableC. I dealing this with one million of data and it is inside the transaction. during this operation i don't want to lock the TableA and TableB. Also, TableA and TableB will not gets updated during this time and even if it's get updated i don't care. My only concern is during the transaction A,B table should not be locked as it will be used by my website for displaying data. How do i achieve this? Any sample please

If all you are doing to TableA and TableB is reading them, then other processes will also be able to read those tables.  It is possible to prevent other processes from reading those tables by specifying an XLOCK query hint, but the default behavior is reads do not block other reads.

Tom

• Proposed as answer by Naomi NModerator 4 hours 41 minutes ago

Hi Tom,

Thanks for the reply and if my understanding is correct about your answer that  TableA and TableB willnot get locked though it is used in transaction.

I don't want to lock the tables. For example, i have created procedure which has the transnational operation as mentioned in my original post and scheduled in a job. please assume that it's dealing with million of data and once the sql job triggers that procedure  and if the procedure take 10 min to process the data because if huge data transformation, during this time, transaction will not get committed unless the process gets over. During this time those two tables should not get locked. Those two tables should be available for other resources to use it.

How to achieve this?

The Idea of using transaction seems little daunting, what if just before the last insert is finished the insert fails you would have to wait for whole data to rollback and believe me this is really a nightmare keeping in mind that you don't want tables to get locked. Is transaction really required why can't we just do insert without transaction

To your other question I would suggest to use Optimistic Isolation level either Read committed snapshot (RCSI) or snapshot Isolation level but I would go for RCSI.

Yes, if what you are doing is something like

Begin Tran
Insert TableC (<column list>)
Select <column list>
From TableA
Inner Join Table On <On Condition>
Where <Where Condition>

That will not prevent other connections from selecting, inserting, deleting or updating rows in TableA or TableB.

Tom