The SQL Server Method
When someone begins modifying data in SQL Server, that data becomes locked. Locking prevents any other connection from accessing that data-even to query it. The data will only be accessible to other users when the transaction is either committed or rolled back.
I can demonstrate this behavior rather simply using the pubs sample database that comes with SQL Server. Open up two windows in Query Analyzer. In the first window, raise the price of all books in the pubs database by executing the following SQL:
set price = price * 1.05
title_id = 'BU2075'
Because I haven't yet executed a commit statement in the code the change is still not finished.
Now in the other window, I'll try to query the titles table by executing:
order by title_id.
You will not get any results. Instead, the little globe on the bottom will keep spinning. Although I'm only updating one row, my select statement includes the one row that is being changed. Therefore, no data will be returned till the update is either committed or rolled back.
The solution chosen by SQL Server potentially lowers throughput and performance. The longer data is locked, or the larger the amount of data being locked, the more likely that other users will have to wait for their statements to execute. Therefore, as a programmer, it is important to keep transactions small and fast.
In more recent versions of the product, Microsoft has enabled SQL Server to lock smaller amounts of data at a time, which is a big improvement. In version 6.5 and below, the smallest lock was on a page. So even if you were only modifying one row out of 10 on a page, all 10 rows would be locked. This increased the likelihood of causing a wait for another connection till the modification was completed. In version 7, Microsoft introduced row-level locking, so now SQL Server locks only the rows that are actually being changed.
SQL Server's solution sounds simple, but there's a lot that has to happen behind the scenes to maintain sufficient performance. For example, if you are modifying many rows, SQL Server will escalate the lock to the page level or even the table level so as not to have to track and maintain separate locks for each record.