Solved

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

Posted on 2011-03-22
12
422 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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
 
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 143

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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