I noticed that the online books say the following:
Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary.
Also, at another place in online books, it says:
The table hints are ignored if the table is not accessed by the query plan.
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
sun21170 wrote:
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
That's true, thus lock hints are "hints" only.
|||
bitmask wrote:
sun21170 wrote:
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
That's true, thus lock hints are "hints" only.
I told you this a while back and you said I was wrong. Two in one week.
|||
I apologize Caddre, I don't remember the specifics.
If a lock makes no sense to SQL Server, it is free to ignore the hint. We can't rely on the server to apply a hint to a table that its optimized out of the query.
I imagine we were talking about a much more practical example, like using NOLOCK on the FROM clause table in a SELECT statement.
|||
bitmask wrote:
I apologize Caddre, I don't remember the specifics.
If a lock makes no sense to SQL Server, it is free to ignore the hint. We can't rely on the server to apply a hint to a table that its optimized out of the query.
I imagine we were talking about a much more practical example, like using NOLOCK on the FROM clause table in a SELECT statement.
Well let me refresh your memory, I said SQL Server over rides lock hints based on load through lock escalation and when the isolation level is Serializable and you said Lock Hints will override any isolation level in the thread below.
http://forums.asp.net/989230/ShowPost.aspx
|||
OK, I've refreshed my memory. :)
There are different types of locking hints. Some locking hints do override the isolation level. Let's take theNOLOCK hint as an example. Use query analyzer to follow along, and I think the examples will make more sense.
First, create a table, and insert a single row.
CREATE TABLE LockTest
(
MyField int
)
INSERT INTO LockTest VALUES(42)
Test 1
Open two windows in query analyzer. In window #1 execute:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT * FROM LockTest
We've left an open transaction in window #1. Because of the isolation level the transaction keeps locks on the LockTest table. Right?
In window #2 execute:
UPDATE LockTest SET MyField = 37
Query window #2 is going to block. The query cannot finish because the query in window #1 still has locks. As soon as we issue a COMMIT TRANSACTIONin window #1, the query in window #2 completes (so make sure to issue aCOMMIT TRANSACTION in window #2 also).
Test 2
This test will override the locks required by the isolation level by using a locking hint.
Issue the following query in window #1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT * FROM LockTest(NOLOCK)
The isolation level requires locks on the LockTest table, but the query hint told SQL Server not to use a lock. We can prove the query hint overrides the isolation level by trying anUPDATE query in window #2.
UPDATE LockTest SET MyField = 56
Query window #2 completes immediately, because NOLOCK told SQL Server not to take locks on the table. We can verify this by looking at the locks in the management section of enterprise manager, or with sp_lock.
Test 3
Given the above discussion, why did I call a locking hint just a "hint"? Because there are some scenarios where SQL Server can override the hint, but this doesn't have to be directly related to the isolation level. SQL usually overrides because of lock escalations, as you've pointed out.
As an example, let's add an index to our table and a bunch of rows.
CREATE CLUSTERED INDEX PK_LockTest
ON LockTest(MyField)
GO
DECLARE @.i int
SET @.i = 0
WHILE @.i < 10000
BEGIN
INSERT INTO LockTest VALUES(@.i)
SET @.i = @.i + 1
END
GO
Now we have roughly 10,000 records in our table.
Let's say we execute the following query.
BEGIN TRANSACTION
UPDATE LockTest WITH (PAGLOCK)
SET MyField = 1
WHERE MyField = 24
We've asked SQL Server to use page locks when updating one row. SQL should have no problem with this hint, and in my test I can verify usingsp_lock or the "Current Activity" node in Enterprise Manager to see there is a lock of type X (exclusive) on a page, but no X lock on the table. Issue aCOMMIT to finish the transaction.
In contrast, we can now issue the following query.
BEGIN TRANSACTION
UPDATE LockTest WITH (PAGLOCK)
SET MyField = 1
Now we've asked the database to update every single row, but use only page locks. To the server, the hint will probably be ignored, because it needs to update the entire table. Even though this is still a small table, I was able to verify this with sp_lock and saw there was an exclusive lock (X) on the table (TAB).
I think we were both correct, but arguing different points. I'm saying query hints can override the locks required for the current transaction isolation level, which is true as demonstrated in test 1 and 2. Your point was that SQL Server can ignore the lock hints when it needs to escalate to more granular locks, which is also true.
|||No we are not both correct because you came to that thread to correct me because you decided as most do
mistakenlyI don't know what I was saying about SQL Server.|||
Caddre,
I think you do a great job. I've seen you provide lots of solid advice, you obviouslydo know what you are talking about.
I apologize if I made you angry. However, I resent being sterotyped as someone dull and arrogant ("you decided as most do"). It's not fair to judge me because of a single post.
If you find some error in my post, kindly point it out so I can learn. I still stand by my assertion that lock hints override the query isolation level. All I did was execute the code in the post and observe the results.
|||If these hints are simply 'hints' thenwhat is the use of using them? I think I will never use a 'select ... with (holdlock)' again, because the intended lock may never be placed. It seems it's better to use isolation levels since they are guaranteed to be applied in every case.Right?|||
Well, if I want to hold a lock on some table, I'm probably holding the lock because I don't want any data to change while I do some processing. If the query optimizer can execute the query without even touching that other table, then I wouldn't be worried if there isn't a lock. Even if the data changes, SQL is getting the data from somewhere else (from an indexed view, perhaps). I'd say that behavior is reasonable.
|||
bitmask wrote:
Caddre,
I think you do a great job. I've seen you provide lots of solid advice, you obviouslydo know what you are talking about.
I apologize if I made you angry. However, I resent being sterotyped as someone dull and arrogant ("you decided as most do"). It's not fair to judge me because of a single post.
If you find some error in my post, kindly point it out so I can learn. I still stand by my assertion that lock hints override the query isolation level. All I did was execute the code in the post and observe the results.
You misunderstand me the point I was making which I did not get to explain to you is I have been in a bank where DB2 AS400 deposits populates first SQL Server 7.0, then SQL Server 2000. That is transaction at its finest so if lock hints over rides Isolation Level I would know because at the time we run both Embarcadero and MOM (Microsoft operation management) in that shop with 68 SQL Servers running 24/7. So your test is in a developer box while mine is live with government guaranteed deposits.
|||
sun21170 wrote:
If these hints are simply 'hints' thenwhat is the use of using them? I think I will never use a 'select ... with (holdlock)' again, because the intended lock may never be placed. It seems it's better to use isolation levels since they are guaranteed to be applied in every case.Right?
That is correct because we used Transaction Isolation level and everything worked even in SQL Server 7.0 on the web. The most important things in SQL Server transaction is to use transaction savepoints and isolation level.
|||
sun21170 wrote:
It seems it's better to use isolation levels since they are guaranteed to be applied in every case.Right?
Not in every case. I believe my code makes this point. The behavior in my code is also described in the documentation, which is pretty clear on this point.
If you scroll towards the example at the bottom of the documentation I linked to, you'll find:
For example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement,key-range locks typically used to maintain serializable transactionsare not taken. (emphasis mine).
The documentation goes into a sample, and produces the following conclusion:
The only lock taken that referencesauthors is a schema stability (Sch-S) lock. In this case,serializability is no longer guaranteed.
|||
Caddre wrote:
bitmask wrote:
You misunderstand me the point I was making which I did not get to explain to you is I have been in a bank where DB2 AS400 deposits populates first SQL Server 7.0, then SQL Server 2000. That is transaction at its finest so if lock hints over rides Isolation Level I would know because at the time we run both Embarcadero and MOM (Microsoft operation management) in that shop with 68 SQL Servers running 24/7. So your test is in a developer box while mine is live with government guaranteed deposits.
Are we trying to see who has the bigger SQL Server? I'm moving 2 TB of life and death clinical data across distributed healthcare databases in 3 countries. Who cares? If SQL Server didn't behave consistently in both test and production - we'd all be in trouble!
The behavior of my code matches the documentation.
FromLocking Hints:
if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range lockstypically used to maintain serializable transactions are not taken.
...
The only lock taken that referencesauthors is a schema stability (Sch-S) lock. In this case,serializability is no longer guaranteed.
FromSET TRANSACTION ISOLATION LEVEL:
Only one of the options can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behaviorunless an optimization option is specified at the table level in the FROM clause of the statement.
If you refuse to believe neither the code, nor the documentation, then there is nothing else I can do to convince you! I'm finished with the convesation.
|||My current application is a Pharmacutical Lab Informatics in Oracle 10g 64bits and I still say to be in control of SQL Server transactions use isolation level. If RDBMS (relational database management systems) transaction works as documented ANSI SQL experts will not take the time to quantify the Transaction matrix of SQL Server. Have a nice day.
