Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AgaK
Yak Posting Veteran
86 Posts |
 Posted - 2009-10-16 : 11:29:21
|
| HiIn SQL Server 2005, would a long running select statment cause locks on a table?For example a SELECT statement that take over 3 minutes to finish because it's summarizes the data. Will it lock the table (shared lock) preventing exclusive locks necessary for insert/update/delete)? Will it block a quick update to finish?thank you kindlyAK |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-16 : 12:18:01
|
| If you are using the default isolation level, READ_COMMITTED, then yes it will cause shared locks. We use READ_COMMITTED_SNAPSHOT to avoid reads from blocking writes. It will not exclusively lock the table though, but yes it can block a write. Switch to READ_COMMITTED_SNAPSHOT! It's the recommended isolation level by Microsoft starting in SQL Server 2005.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2009-10-19 : 13:50:18
|
| thank you! That is a good start. I will look for more documentation. AK |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
locks