June 14, 2002 at 11:02 am
Why would a SELECT statement cause a lock on at a database or table?
(not a select into)
-Kevin
June 14, 2002 at 12:08 pm
When you say a lock shared locks are common as it reads the data and doesn't want to pickup uncommitted items. What type are you seeing?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 12:10 pm
When you SELECT (without a hint), as Antares mentioned, a shared lock is placed on the data. The lock could be row, page, or table (or others), but basically prvents changes (inserts, updates, deletes) to that shared data. Other SELECTs can read the data, however.
Steve Jones
June 14, 2002 at 12:13 pm
But a shared lock like that should go away on it's own, right? As long as nothing else is locking the table)
-K
June 14, 2002 at 12:18 pm
Yes, when the SELECT is finished, the shared lock should drop.
IS this not happening? Can you provide more details about what is happening?
Steve Jones
June 14, 2002 at 12:57 pm
There is a bug in SQL Server 2000 where shared locks in a transaction are held until the end of the transaction. It's fixed in SP2.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q300412&GSSNB=1
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 15, 2002 at 2:25 am
if you dont want any locks to be implemented then you have specify a nolock hint on the select.
eg:
select * from tablename with (nolock)
HTH
June 15, 2002 at 7:10 pm
Common statement but a bad idea in a high transaction environment, especially if you want only committed data. When nolock is specified then dirty reads can occurr (reading of uncommitted data) and if these are for reporting and a record rolls back after hyou run then you have an invalid result set (some cases this is ok but if actually results are required it is not). It is a usefull option but be carefull how you implement, I don't say never use.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply