Start Concurrent updates
In one query window, start updating the Employee table you just created inside a transaction.

BEGIN TRANSACTION
UPDATE Employee
SET city = 'Delhi'
WHERE empid = 1

Do not commit or rollback the transaction right away. Instead, while this connection is still open, start another query window by clicking on the 'New Query' button on the toolbar. You will normally find this button on the top left corner of Management Studio. In the new query window, let us now try to access the same row which is under the update transaction in the old query window:

SELECT empid, city FROM Employee
WHERE empid = 1

You will see that this activitywill get blocked. This query will complete only when you finish the transaction in the first query editor window:

ROLLBACK TRANSACTION

Management Studio neatly tabs both the query windows for easy navigation. You could alternately have committed the transaction. Either way, the query in the second query window will be unblocked and will proceed to completion.

In this case, the second query window waited for the first query window to complete the transaction before reading the data the first query window was trying to modify. This is the default behavior with SQL Server.

Configuring Concurrent Updates
What if you are in a hurry and want to read the data even before it is committed or rolled back in the previous transaction. You would need to state your intent then explicitly. Let us repeat the entire exercise then. This is what you do in the first query window, as before:

BEGIN TRANSACTION
UPDATE Employee
SET city = 'Delhi'
WHERE empid = 1

This time, in the second query window, you specify that you want to read the data before it has been committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT empid, city FROM Employee
WHERE empid = 1

You will now find your query responds immediately. Now the second query is not blocked by the first query by setting the transaction isolation level to READ UNCOMMITTED. Reading the result of an uncommitted transaction of a different connection, as we have done now, is called as dirty read. What did we gain? The job is done faster, because we did not have to wait for the first query window to complete its transaction. What did we lose? The transaction could be committed or rolled back. If it is committed, data as you read in the second query window is the final data. If it is rolled back, data read in the second query window would be different after the rollback.

Now please rollback the first transaction.

<< PREVIOUS NEXT>>

• Win 7: Taskbar and Thumbnail Enhancements

• Stop spam posts on your phpBB3 forum

• Gameloft says it, others reining in Android plans

• Tips and tricks for using Windows 7

• NVIDIA expands 3D technology to notebook mkt