4 ms·

If I read this correctly, does this article say that an Update statement is blocking selects? Does SQL Server really work like this? My expertise is in Oracle

by fendale 18y ago

If I read this correctly, does this article say that an Update statement is blocking selects? Does SQL Server really work like this? My expertise is in Oracle - readers don't block writers and writers don't block readers. Infact writers don't block other writers unless they are updating the same row. I think MySQL works the same way as Oracle in this regard. I find it hard to imagine building a performing web application on top on a database in which writers block readers!

KiwiNige 18y ago
SQL server now has row versioning like Oracle, so that writes won't block reads, and you don't have to read dirty data (you just read data how it was before the write started). I think this is not turned on by default because it has a performance hit. Also I can't remember if it's new to 2005 or 2008 versions and can't be bothered to look it up.
- gnaritas 18y ago
  It's new to 2005, and though it's not turned on by default, it damn well should be as it is in Oracle and Postgres. Multi version concurrency control (i.e. writers not blocking readers) is old hat in the db field. Prior to 05, you just liberally sprinkled (nolock) on your selects and accepted that you may get a dirty read but that it was better than the db falling over with over aggressive locking.
  - fendale 18y ago
    Its even turned on in MySQL if you are using innodb tables too!
bootload 18y ago
"... does this article say that an Update statement is blocking selects? Does SQL Server really work like this? ..." I don't know. One thing I do know is in the places I've seen SQLServer used this wasn't a problem. In StackOverflow #17~ http://itc.conversationsnetwork.org/shows/detail3792.html http://itc.conversationsnetwork.org/shows/detail3792.html Atwood mentions using LINQ a lot, instead of raw SQL or code they have written. I'm wary of generated code from MS tools. I wonder if this might be the problem. You can read the transcript here. The podcast is pretty good. Spolsky the Morcombe to Atwoods, Wise ~ http://en.wikipedia.org/wiki/Morecambe_and_Wise http://en.wikipedia.org/wiki/Morecambe_and_Wise "... I'm guessing that MySQL, which grew up on web apps, is much less pessimistic out of the box than SQL Server ..." That one sums it all up. But does it summarise the problem? SQLServer works well enough out of the box.
icey 18y ago
What is likely happening is that he's rapidly selecting out of the database immediately after inserting his record. I am guessing that he's trying insert a number of records at once. SQL does something weird with locking when you use an auto-incrementing integer for a primary key - it uses page locking for safety when creating records. So if he's just inserted record #10, and is now trying to select it while simultaneously inserting record #11, there can be some problems with timing. It's pretty rare, but if he's running a program to blast a bunch of stuff in at once, I've seen it happen. The other possibility is that he's inserting or updating foreign key requirements at the same time he's trying to select out that data; and he has the option turned on to enforce foreign key constraints. SQL will lock the dependent data while any updates or inserts occur to ensure the FKs are valid through the lifetime of the transaction execution on the foreign table.

by fendale 18y ago

KiwiNige 18y ago

SQL server now has row versioning like Oracle, so that writes won't block reads, and you don't have to read dirty data (you just read data how it was before the write started). I think this is not turned on by default because it has a performance hit. Also I can't remember if it's new to 2005 or 2008 versions and can't be bothered to look it up.

gnaritas 18y ago
It's new to 2005, and though it's not turned on by default, it damn well should be as it is in Oracle and Postgres. Multi version concurrency control (i.e. writers not blocking readers) is old hat in the db field. Prior to 05, you just liberally sprinkled (nolock) on your selects and accepted that you may get a dirty read but that it was better than the db falling over with over aggressive locking.
- fendale 18y ago
  Its even turned on in MySQL if you are using innodb tables too!

bootload 18y ago

"... does this article say that an Update statement is blocking selects? Does SQL Server really work like this? ..." I don't know. One thing I do know is in the places I've seen SQLServer used this wasn't a problem. In StackOverflow #17~ http://itc.conversationsnetwork.org/shows/detail3792.html http://itc.conversationsnetwork.org/shows/detail3792.html Atwood mentions using LINQ a lot, instead of raw SQL or code they have written. I'm wary of generated code from MS tools. I wonder if this might be the problem. You can read the transcript here. The podcast is pretty good. Spolsky the Morcombe to Atwoods, Wise ~ http://en.wikipedia.org/wiki/Morecambe_and_Wise http://en.wikipedia.org/wiki/Morecambe_and_Wise "... I'm guessing that MySQL, which grew up on web apps, is much less pessimistic out of the box than SQL Server ..." That one sums it all up. But does it summarise the problem? SQLServer works well enough out of the box.

icey 18y ago

What is likely happening is that he's rapidly selecting out of the database immediately after inserting his record. I am guessing that he's trying insert a number of records at once. SQL does something weird with locking when you use an auto-incrementing integer for a primary key - it uses page locking for safety when creating records. So if he's just inserted record #10, and is now trying to select it while simultaneously inserting record #11, there can be some problems with timing. It's pretty rare, but if he's running a program to blast a bunch of stuff in at once, I've seen it happen. The other possibility is that he's inserting or updating foreign key requirements at the same time he's trying to select out that data; and he has the option turned on to enforce foreign key constraints. SQL will lock the dependent data while any updates or inserts occur to ensure the FKs are valid through the lifetime of the transaction execution on the foreign table.