June 20, 2002 at 11:40 am
We have a procedure that updates a temp table several times by joining it to different permanent tables.
The temp table may have about 100 rows but it references permanent tables with 500,000 rows or more while updating.
Here is an example of the update statements that we have in the stored proc.
begin tran
UPDATE #tmp_ABC
SET field1 = z.field1,
field2 = z.field2,
field3 = z.field3,
field4 = z.field4,
FROM XYZ z (READUNCOMMITTED)
WHERE #tmp_ABC.field9 = z.field9
commit tran
Question:
----------
There would be some locks placed on the temp table because it is being updated, locks like 'Intent Exclusive' and finally 'Exclusive'
but would we have any locks at all on the permenant tables? One would think that while the temp table is being updated the
fields that it is joining to in the permanent table should not be changed within the transaction. But if I look at the locks
it seems there are none (no shared read lock) on the permanent table. Is that correct or does Sql Server infact put shared locks on
the table that appear in the FROM part of the 'UPDATE' statement? We still experience slow response as if users have to wait
because of locks and there is some blocking too.
Also, what is a good way of looking at the locks while the query is running, it is difficult to get what locks are placed by
running sp_lock or sp_lock2 after the query is over in the EM.
June 20, 2002 at 1:10 pm
You could run profiler, but that might result in gobs of information. I would expect that shared locks were being placed on the perm tables. You should be able to run sp_lock while the query is executing to see these locks.
Steve Jones
June 20, 2002 at 1:46 pm
Steve,
I tried creating a small perm table with a few records and then updated a temp table which joined with this perm table in the from part of my update. Surprisingly, see no shared locks on the perm table. Can you try this scenario and tell me if you see them, am I not invoking sp_lock2 at the right time maybe. Can someone else try this kind of update and see if they don't see any locks on the perm table as well.
Thanks.
June 20, 2002 at 7:31 pm
It is the fact you are issuing a READUNCOMMITTED which is equivalent to NOLOCK hint.
From BOL
quote:
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
In other words don't issue any locks on the tables from the select statement just read all data and any uncommitted data. Remove the READUNCOMMITTED hint and a shared lock should appear for the duration of the selection from the tables.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 21, 2002 at 3:25 pm
Did without the 'readuncommited' still the same result. No locks on the permanent table.
June 27, 2002 at 8:08 am
I have spent a lot of time trying to determine the locking architecture/logic for sql server and I am still no expert. From what I know, sql server is more concerned with the table that is being updated then the table that is being selected from. Normally, if you select from a table, no locks will be put on that table unless you specify with a locking hint. You should use the UPDLOCK hint in your case. It will allow any other user to select from this table but no updates will be allowed. Of course, I do not know how your program runs(for example is that perm table updated a lot) so I can not guarantee that you will not run into blocking issues...
June 28, 2002 at 4:22 pm
Thanks, I landed up changing the way we were querying so this is not an issue really, but we were not really trying to lock anything it's just that logically it seems that if you update your table with info from another table then you would think that while you are using info from this other table, it's got at least shared read locks. When I looked at sp_lock did not see those. I am still curious and will do some research but our problem has been resolved.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply