Monday, April 06, 2009

Oracle sql code , row lock to prevent other users while editing data

Some databases, SQL Server included, use locks to prevent users from seeing uncommitted modifications. In these systems if UserA is modifying some set of data, then UserB and all the rest of the users must wait until UserA is done modifying that data before they can even get a shot at reading the data, let alone modifying it.
 
They are used like this:
 
SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE 'foobar'
 
and
 
UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'
 

Adobe News