Monday, February 21, 2011

NOLOCK Hints In the MS SQL Server Database

Many application in the SQL Server used the same database and it is very difficult to managing the locking issues that affect the potential performance of the SQL Server database. Locking and blocking are the real things that happen regardless of how well our database architecture. The main problem is that how to avoid this locking and blocking problems. As mentioned above many applications used the same database that application are reporting based application, transaction based application and many more.

How to Solve: Microsoft SQL Server database provides many hints that are used in excuting the query. The hints also show the impact on the other processes. The hints offered by the MS SQL for locking is NOLOCK hint. The NOLOCK hint allows MS SQL to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.

Example: Lets take an example. Create a table and insert some values in it.

CREATE TABLE Employee
(
id int,
firstName varchar(10)
)

INSERT INTO Employee VALUES (1, 'David')
INSERT INTO Employee VALUES (2, 'Mark')
INSERT INTO Employee VALUES (3, 'Sam')


After the data has been inserted we begin a transaction and update one of the records.


BEGIN TRAN
UPDATE Employee SET firstName = 'Geoger' WHERE id = 3


If we open up a new query window and try to run the query below, the query will not complete because it is being blocked by the first query above which we have neither committed or rolled back and therefore the locks are still being held and this second query below can not complete until the first transaction is totally complete.

SELECT * FROM Employee


At this point we can either cancel the second query that does the SELECT or we can commit or rollback the UPDATE query for everything to complete. Another option is to run the query with the NOLOCK hint as written below.

SELECT * FROM Employee (nolock)


When this query runs you will see that the query completes, but the problem we have is that the data for ID =3 shows "George" where it should still be "Sam".

Since we never committed the UPDATE transaction the data should still have the value of "Steve", but since we are using the NOLOCK hint this overrides all locks that are currently being held and therefore gives you the result set of what the data currently looks like in the database. This is known as a dirty read. The advantage of NOLOCK is that it does not have to wait for the other transaction to complete, but the disadvantage is that is gives you incorrect data. If the UPDATE statement later gets committed, then you get lucky and have the correct data, but if the UPDATE gets rolled back the result set is incorrect.

No comments:

Post a Comment