?
Solved

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

Posted on 2011-03-22
12
Medium Priority
?
426 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 500 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 1252 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 10

Assisted Solution

by:khan_webguru
khan_webguru earned 1252 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 500 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 1252 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 248 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 1252 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 1252 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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