Solved

I need to lock a specific row in the database...

Posted on 2011-03-22
12
381 Views
Last Modified: 2012-05-11
I need to lock a specific row in the database...

Is this possible?
0
Comment
Question by:Mr_Shaw
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 125 total points
ID: 35188302
You could use some locking hints:

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx

holdlock and rowlock



select * from mytable
with (holdlock,rowlock)
where mycol = '-- insert where clause --'

0
 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 313 total points
ID: 35188358
Hello Bro,

Change your SELECT statement to look something like this:

 
BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

Open in new window


Please find the link below for more information

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.developerfusion.com/article/1688/sql-server-locks/4/

Hope this will help you to fix your problem.

Regards,

Asif Ahmed Khan
0
 

Author Comment

by:Mr_Shaw
ID: 35188404
I just ran:

SELECT  RowNumber
FROM    trace1 WITH (HOLDLOCK, ROWLOCK)
where rowNumber = 0

I then ran in a different SPID:

SELECT  RowNumber
FROM    trace1
where rowNumber = 0

I was expecting nothing to happen.. but it returned a row. I thought HOLDLOCK and ROWLOCK would prevent this row being read?
0
 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 313 total points
ID: 35188437
Hello Bro!

Try This and let me know then

 
BEGIN TRAN

    SELECT 1
    FROM Table
    WITH (XLOCK, ROWLOCK)

COMMIT TRAN

Open in new window


Regards,

Asif Ahmed Khan
0
 

Author Comment

by:Mr_Shaw
ID: 35188451
Hi khan_webguru,

The same thing happens when I use WITH (XLOCK, ROWLOCK)

0
 

Author Comment

by:Mr_Shaw
ID: 35188461
Though when I run sp_lock I do see the locks... I thought the locking would stop reads?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 125 total points
ID: 35188478
No. You'd have to write that in your code yourself:

http://msdn.microsoft.com/en-us/library/ms172909%28SQL.100%29.aspx

When locking the row you are locking to prevent data modification. Preventing data reading will require you to add in a field to say this row is currently being read, don't return it.
0
 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 313 total points
ID: 35188536
An explanation...

ROWLOCK/PAGELOCK is granularity
XLOCK is mode
Granularity and isolation level and mode are orthogonal.

Granularity = what is locked = row, page, table (PAGLOCK, ROWLOCK, TABLOCK)

Isolation Level = lock duration, concurrency (HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)

Mode = sharing/exclusivity (UPDLOCK, XLOCK)

"combined" eg NOLOCK, TABLOCKX

XLOCK would have locked the row exclusively as you want. ROWLOCK/PAGELOCK wouldn't have.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 62 total points
ID: 35188541
no. locks will not prevent the data to be read, just to be updated by other processes.
0
 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 313 total points
ID: 35188570
To work around this behavior, you can specify the XLOCK and PAGLOCK hints for the SELECT query. By doing so, the problem does not occur because an X PAG lock is incompatible with the IS PAG lock, which is what queries in other READ_COMMITTED connections try to acquire. For example:


 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- equivalent to HOLDLOCK
GO
BEGIN TRAN
        SELECT * FROM titles (XLOCK, PAGLOCK) WHERE title_id = 'BU1111'

Open in new window


This behavior is by design.

The READ COMMITTED transaction isolation level is ANSI compliant.

Steps to Reproduce the Behavior

1

Connect to an instance of SQL Server 2000.

2

Use the pubs database.

3

Open two additional connections in Query Analyzer (for a total of three connections).

In the first connection, copy and paste the following code:

 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- equivalent to HOLDLOCK
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
        SELECT * FROM titles (XLOCK) WHERE title_id = 'BU1111'

Open in new window


In connection 2, copy and paste the following code:

 
SET STATISTICS PROFILE ON
GO
SELECT * FROM titles WHERE title_id = 'BU1111'

Open in new window


In connection 3, copy and paste the following code:

 
EXEC sp_lock

Open in new window


Run the code in connection 1, and then check the locking behavior by running the code in connection 3. You see an exclusive (X) lock on a KEY of the clustered index (the single row that is being affected):

Run the code in connection 2. The output shows that the clustered index is being used, but the row is being read even though there is an exclusive lock on it that connection 1 is holding:


Hope this will help you to solve your problem.
0
 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 313 total points
ID: 35188572
Hello Bro,

My previous post was link with this reference

http://support.microsoft.com/kb/324417

Regards,

Asif Ahmed Khan
0
 

Author Closing Comment

by:Mr_Shaw
ID: 35188685
thanks.. I changed the transaction to an update. It then locked the row.

Thanks for your help... there is so much to learn!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now