Help Needed in Transaction

Transact-SQL
1

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

2

If you are sure that,during the transaction there will be no updates or inserts on the 2 tables,you can use NOLOCK to avoid locking.

3

Hi sqllover,
By default, if you only performing select (joins) on TableA and B, it will only place a share lock, which ,means anyone else can perform select on it. The only thing is that when someone trying to perform an update/delete, it will then place a intend lock and block other select afterwards and wait until that update competes before it releases it.

By that, if you are sure that there was no update on those 2 tables, you do not really have to do anything.

For placing NOLOCK table hint, it means that it can read dirty pages, meaning even if there is an update occurring, your select can still get through. It might not be what you wanted.

Hope this helps.

4

If you are sure there will be no updates to TableA and TableB then locking them will not BLOCK anyone, will it?

If so no need for NOLOCK that I can think of.

OTOH if you do use NOLOCK and anyone DOES update either of the tables then the outcome of your UPDATE may be wrong - some rows may be missed altogether, or some might be processed twice. There is also a type of index page split which will cause the update to fail with an error.

Personally I wouldn't use NOLOCK for anything like this ...

I don't know if the "Updater" using READ COMMITTED solves this? or if the "Selector" would have to use that isolation? We just set the database to READ_COMMITTED_SNAPSHOT which, for us and many like us :), solves the whole problem.

1 Like
5

Thank you guys. appreciated....