May 25, 2010 at 8:41 am
If iam want to do an INSERT and UPDATE on a table, i understrand 1 process has to wait for the other as either of them would lock the table to do the process but I was wondering if there is a way to do in parallel as the INSERT will run on differnt partition and UPDATE on other partition.
Is it true that it locks whole table for any DML activity or can we manage to lock only that particular partition ?
May 25, 2010 at 8:54 am
Tara-1044200 (5/25/2010)
If iam want to do an INSERT and UPDATE on a table, i understrand 1 process has to wait for the other as either of them would lock the table to do the process but I was wondering if there is a way to do in parallel as the INSERT will run on differnt partition and UPDATE on other partition.Is it true that it locks whole table for any DML activity or can we manage to lock only that particular partition ?
\
It is not generally true that DML activity locks an entire table. If an index can be efficiently used by the optimizer to do the update/delete then you can get page or row locks and thus have lots of concurrent activities. Inserts usually do not take table locks.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2010 at 8:56 am
so how do i make sure INSERT is not conflicted with UPDATE ? what i need to force in T-SQL to do so?
May 25, 2010 at 11:07 am
Tara-1044200 (5/25/2010)
so how do i make sure INSERT is not conflicted with UPDATE ? what i need to force in T-SQL to do so?
You cannot do this. Best you can do is ensure your updates are as "tight" as possible. This usually means updating few rows that us an index seek to get to the row(s) updated.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply