Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Does SQL Server always honour HOLDLOCK?

Author  Topic 

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-17 : 20:28:18
Hi,

If I have a SELECT statement with the HOLDLOCK locking hint, does the database always honour this hint for the duration of the transaction?

Cheers,

I.

simondeutsch
Aged Yak Warrior

547 Posts
Posted - 2003-09-17 : 22:11:04
If you have this SELECT in a transaction, why not set the transaction isolation level to SERIALIZABLE? This is equivalent to issuing a HOLDLOCK on the SELECT.

Sarah Berger MCSD
Go to Top of Page

JustinBigelow
SQL Gigolo

1157 Posts
Posted - 2003-09-18 : 01:06:47
quote:
Originally posted by simondeutsch

If you have this SELECT in a transaction, why not set the transaction isolation level to SERIALIZABLE? This is equivalent to issuing a HOLDLOCK on the SELECT.

Sarah Berger MCSD


Ok, will SQL Server always honor the serializable isolation level?

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2003-09-18 : 04:28:06
Should do.
Do you have an example where it doesn't?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 08:55:07
quote:
why not set the transaction isolation level to SERIALIZABLE


Won't SERIALIZABLE have a higher overhead than holding a single shared lock on a record? In other words, is it not overkill?

Similarly, maybe using 'WITH (HOLDLOCK)' has a lower overhead than setting the transaction isolation level, doing the read, and then setting it back to the default. I don't know!

I only need to hold the lock on a single record - not all the records read during the rest of the transaction.
Go to Top of Page

JustinBigelow
SQL Gigolo

1157 Posts
Posted - 2003-09-18 : 09:05:24
quote:
Originally posted by nr

Should do.
Do you have an example where it doesn't?


Nope. I was just being facetious. Since substituting one mechanism for an equivalent didn't really answer the question at hand.

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2003-09-18 : 11:46:38
Should do.
Do you have an example where it doesn't?

That was a question for the original question but applies to your question too.

Maybe it should have been
Do you have an example where you think it doesn't?

Note it will only hold the shared lock unless you change the lock type (and make sure you are actually reading the data page not an index).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 13:07:37
quote:
Do you have an example where it doesn't?


Not me.

I did try using the XLOCK hint on a SELECT and another process was able to read from the row. However, TABLOCKX stopped a seperate process reading the record. Is there some reason why XLOCK won't actually put an exclusive lock on a row when reading?

quote:
it will only hold the shared lock unless you change the lock type


Do you mean, the shared lock will be lost if, say, an update is made on the record resulting in an exclusive lock?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2003-09-18 : 14:02:36
The shared lock will block an update.

xlock
try

create table a (i int)
insert a select 1

begin tran
select * from a (xlock, holdlock)

then on another spid
select * from a

you should find the second spid blocked.

>> Do you mean, the shared lock will be lost if, say, an update is made on the record resulting in an exclusive lock?
If the spid does an update then the lock will be upgraded to exclusive and automatically held to the end of the transaction.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 15:29:37
Silly me, I forgot to use HOLDLOCK when using XLOCK.

Thanks for your help.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 16:47:51
I'm afraid I can still read a record with a different process after reading it with XLOCK, HOLDLOCK. I ran the following code in VB.net and broke where indicated. During the break, I ran..

'SELECT * FROM products WHERE productID = 132'

in Query Analyzer and up came the record.


        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlCommand
        Dim myTrans As SqlTransaction
        Dim myReader As SqlDataReader

        MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        MyCommand = MyConnection.CreateCommand()
        MyConnection.Open()
        myTrans = MyCommand.Connection.BeginTransaction()
        MyCommand.Transaction = myTrans
        MyCommand.CommandText = "SELECT * FROM products WITH (XLOCK, HOLDLOCK) WHERE productID = 132"
        myReader = MyCommand.ExecuteReader()
        myReader.Read()
        Dim id As Integer = myReader("productID")
PAUSED HERE:        myReader.Close()
        myTrans.Commit()
        MyConnection.Close()
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-09-18 : 16:54:16
I have a question to your question...

What is it you're trying to accomplish.

Seems to be that you want to read a record to the interface...hold on to it (forever?) until some action is taken...about right?



Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2003-09-18 : 16:59:10
Have a look to see if a transaction is being held.

instead of myTrans = MyCommand.Connection.BeginTransaction()
try
MyCommand.CommandText = "begin tran SELECT * FROM products WITH (XLOCK, HOLDLOCK) WHERE productID = 132"

then commit/rollback the transaction in the same way.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 18:20:07
[code]
'myTrans = MyCommand.Connection.BeginTransaction()
'MyCommand.Transaction = myTrans
MyCommand.CommandText = "begin tran SELECT * FROM products WITH (XLOCK, HOLDLOCK) WHERE productID = 132"
[/code]

Well, these are the changes I made. Same result though.

quote:
What is it you're trying to accomplish.
I'm just trying to see how locking works on SQL Server - not implement pessimistic concurrency.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts
Posted - 2003-09-18 : 18:27:54
I figure my test rig is ok because I tried it with TABLOCKX instead and it works - I couldn't read the record from QA.

Also, according to the manual, TABLOCKX and XLOCK don't need HOLDLOCK to persist till the end of a transaction.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2003-09-19 : 05:03:33
It's always worth running a simple test as in my previous post to verify things.
You should find that tablockx does perform a holdlock but that xlock doesn't.
It's worth stating the holdlock anyway just to make things clear.

Try the same thing with two spids in query analyser. If that works (it should) then there is something wrong with the VB code.

Indexes on the table can affect the action of this test as it will only lock the entities it reads. e.g. if there is an index on the filter column and the row being read doesn't exist the lock will probably not block the other spid. If it does exist it may depend on the index pages needed for the two queries.

For your query though I would expect the data page to be needed for both queries if that is what you are running.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site