October 1, 2010 at 7:32 am
First of all, Hi Everyone!
((Sorry for my bad englsh!))
Ill try to explain my problem the best way i could.
I want that several rows stay locked until the transaction ends.
Scenario.
I have a table witch i query, and a get several rows, i want that no one can query those rows until the transaction ends.
begin transaction
1 - query some table and returns some data
2 - do other querys to other tables (in base of the first query)
3 - do some inserts in the table i query first (the result of the query i mencioned before. (item 2))
commit transaction
What i want to do is, while the transaction is running, no one can query the data retrived by query in item 1.
The isolation level is read committed with ALLOW_SNAPSHOT_ISOLATION ON
I try some table hints with no results.
Thanks in advance!![:)][:)][:)][:)]
October 1, 2010 at 9:07 am
If they can access row versions through snapshot isolation, you're kind of in trouble. In fact, that's the whole idea of setting up snapshot isolation, allowing access to data while the updates are occurring.
The one thing that I know will stop the access is to have a schema modification lock on the table in question, but I think that will block everyone trying to access the entire table, not just the few rows, so I don't think that's what you want.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 1, 2010 at 9:33 am
SQL Server by default should be in pessimistic mode, which should give you the behavior you're looking for without table hints.
😀
October 1, 2010 at 9:41 am
In case I completely misunderstood your question, here is a link to BOL that discusses the HOLDLOCK table hint.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/8bf1316f-c0ef-49d0-90a7-3946bc8e7a89.htm
See if this is what you're looking for.
😀
October 1, 2010 at 12:04 pm
Tim Parker (10/1/2010)
SQL Server by default should be in pessimistic mode, which should give you the behavior you're looking for without table hints.
True but, they've got snapshot isolation going, which changes the rules a bit.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 1, 2010 at 12:05 pm
Tim Parker (10/1/2010)
In case I completely misunderstood your question, here is a link to BOL that discusses the HOLDLOCK table hint.ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/8bf1316f-c0ef-49d0-90a7-3946bc8e7a89.htm
See if this is what you're looking for.
I think you may be right. HOLDLOCK could work. I'd need to test it to be sure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 1, 2010 at 6:21 pm
The senior DBA that I work with gave me that idea. So I cheated a bit... 😀
😀
October 2, 2010 at 4:15 am
Tim Parker (10/1/2010)
The senior DBA that I work with gave me that idea. So I cheated a bit... 😀
No, no, no. We don't call it cheating. We call it "using our resources."
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 3, 2010 at 3:11 am
gheinze (10/1/2010)
The isolation level is read committed with ALLOW_SNAPSHOT_ISOLATION ON
At the default READ_COMMITTED isolation level, you would need to SET READ_COMMITTED_SNAPSHOT ON to get row versioning isolation.
ALLOW_SNAPSHOT_ISOLATION refers to full SNAPSHOT isolation, which requires a SET TRANSACTION ISOLATION SNAPSHOT command to activate it for a connection.
Querying the data in query one with the UPDLOCK hint would be enough to prevent concurrent writers modifying the data at the READ_COMMITTED isolation level. You would need an XLOCK hint to block readers that are taking shared locks.
edit: misread the original question, updated with correct info about UPDLOCK.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 4, 2010 at 4:33 am
Thanks all for the replies!!
At the default READ_COMMITTED isolation level, you would need to SET READ_COMMITTED_SNAPSHOT ON to get row versioning isolation.
ALLOW_SNAPSHOT_ISOLATION refers to full SNAPSHOT isolation, which requires a SET TRANSACTION ISOLATION SNAPSHOT command to activate it for a connection.
Querying the data in query one with the UPDLOCK hint would be enough to prevent concurrent readers accessing the data at the READ_COMMITTED isolation level.
Now i'm going to test with UPDLOCK and I post a example scenario.
thanks again!
October 4, 2010 at 5:02 am
Paul White NZ (10/3/2010)
gheinze (10/1/2010)
The isolation level is read committed with ALLOW_SNAPSHOT_ISOLATION ONAt the default READ_COMMITTED isolation level, you would need to SET READ_COMMITTED_SNAPSHOT ON to get row versioning isolation.
ALLOW_SNAPSHOT_ISOLATION refers to full SNAPSHOT isolation, which requires a SET TRANSACTION ISOLATION SNAPSHOT command to activate it for a connection.
Querying the data in query one with the UPDLOCK hint would be enough to prevent concurrent readers accessing the data at the READ_COMMITTED isolation level.
I tested this on a test Db with isolation level "read committed snapshot"
query 1
use database
begin transaction
--(TABLOCKX)
--(serializable)
--(xlock, rowlock)
SELECT * FROM table_tp with (updlock)
where ID = 106435
WAITFOR DELAY '00:00:10'; --simulate other transactions
rollback transaction
query 2
use database
begin transaction
select * from table_tp
where ID = 106435
commit transaction
Steps:
I run query 1
Inmediately I run query 2 (i want query 2 wait until query 1 ends)
Results:
query 2 finish while query 1 is running.
meanwhile in other connection I run sp_lock to see the locks and I see:
a lot of Intent update (IU locks)
64719905584250PAG1:758656 IUGRANT
64719905584250PAG1:758657 IUGRANT
64719905584250PAG1:758658 IUGRANT
64719905584250PAG1:758659 IUGRANT
64719905584250PAG1:758660 IUGRANT
64719905584250PAG1:758661 IUGRANT
64719905584250PAG1:758662 IUGRANT
64719905584250PAG1:758663 IUGRANT
64719905584250PAG1:758664 IUGRANT
64719905584250PAG1:758665 IUGRANT
64719905584250PAG1:758666 IUGRANT
64719905584250PAG1:758667 IUGRANT
64719905584250PAG1:758668 IUGRANT
64719905584250PAG1:758669 IUGRANT
64719905584250PAG1:758670 IUGRANT
64719905584250PAG1:758671 IUGRANT
64719905584250PAG1:758680 IUGRANT
64719905584250PAG1:758681 IUGRANT
64719905584250PAG1:758682 IUGRANT
64719905584250PAG1:758683 IUGRANT
64719905584250PAG1:758684 IUGRANT
64719905584250PAG1:758685 IUGRANT
64719905584250PAG1:758686 IUGRANT
64719905584250PAG1:758687 IUGRANT
64719905584250PAG1:758688 IUGRANT
64719905584250PAG1:758689 IUGRANT
64719905584250PAG1:758690 IUGRANT
64719905584250PAG1:758691 IUGRANT
64719905584250PAG1:758692 IUGRANT
64719905584250PAG1:758693 IUGRANT
64719905584250PAG1:758694 IUGRANT
64719905584250PAG1:758695 IUGRANT
64719905584250PAG1:758696 IUGRANT
64719905584250PAG1:758697 IUGRANT
64719905584250PAG1:758698 IUGRANT
64719905584250PAG1:758699 IUGRANT
64719905584250PAG1:758700 IUGRANT
64719905584250PAG1:758701 IUGRANT
64719905584250PAG1:758702 IUGRANT
64719905584250PAG1:758703 IUGRANT
64719905584250PAG1:758704 IUGRANT
64719905584250RID1:789866:8 UGRANT
64719905584250PAG1:758593 IUGRANT
64719905584250PAG1:758592 IUGRANT
64719905584250PAG1:758595 IUGRANT
64719905584250PAG1:758594 IUGRANT
64719905584250PAG1:758597 IUGRANT
64719905584250PAG1:758596 IUGRANT
64719905584250PAG1:758599 IUGRANT
64719905584250PAG1:758598 IUGRANT
64719905584250PAG1:758601 IUGRANT
64719905584250PAG1:758600 IUGRANT
64719905584250PAG1:758603 IUGRANT
64719905584250PAG1:758602 IUGRANT
64719905584250PAG1:758605 IUGRANT
64719905584250PAG1:758604 IUGRANT
64719905584250PAG1:758607 IUGRANT
64719905584250PAG1:758606 IUGRANT
64719905584250PAG1:758609 IUGRANT
64719905584250PAG1:758608 IUGRANT
64719905584250PAG1:758611 IUGRANT
64719905584250PAG1:758610 IUGRANT
64719905584250PAG1:758613 IUGRANT
64719905584250PAG1:758612 IUGRANT
64719905584250PAG1:758615 IUGRANT
64719905584250PAG1:758614 IUGRANT
64719905584250PAG1:758617 IUGRANT
64719905584250PAG1:758616 IUGRANT
64719905584250PAG1:758619 IUGRANT
64719905584250PAG1:758618 IUGRANT
64719905584250PAG1:758621 IUGRANT
64719905584250PAG1:758620 IUGRANT
64719905584250PAG1:758623 IUGRANT
64719905584250PAG1:758622 IUGRANT
64719905584250PAG1:758633 IUGRANT
64719905584250PAG1:758632 IUGRANT
64719905584250PAG1:758635 IUGRANT
64719905584250PAG1:758634 IUGRANT
64719905584250PAG1:758637 IUGRANT
64719905584250PAG1:758636 IUGRANT
64719905584250PAG1:758639 IUGRANT
64719905584250PAG1:758638 IUGRANT
64719905584250PAG1:758649 IUGRANT
64719905584250PAG1:758648 IUGRANT
64719905584250PAG1:758651 IUGRANT
64719905584250PAG1:758650 IUGRANT
64719905584250PAG1:758653 IUGRANT
64719905584250PAG1:758652 IUGRANT
64719905584250PAG1:758655 IUGRANT
64719905584250PAG1:758654 IUGRANT
64719905584250PAG1:758553 IUGRANT
64719905584250PAG1:758552 IUGRANT
64719905584250PAG1:758555 IUGRANT
64719905584250PAG1:758554 IUGRANT
64719905584250PAG1:758557 IUGRANT
64719905584250PAG1:758556 IUGRANT
64719905584250PAG1:758559 IUGRANT
64719905584250PAG1:758558 IUGRANT
64719905584250PAG1:758561 IUGRANT
64719905584250PAG1:758560 IUGRANT
64719905584250PAG1:758563 IUGRANT
64719905584250PAG1:758562 IUGRANT
64719905584250PAG1:758565 IUGRANT
64719905584250PAG1:758564 IUGRANT
64719905584250PAG1:758567 IUGRANT
64719905584250PAG1:758566 IUGRANT
64719905584250PAG1:758569 IUGRANT
64719905584250PAG1:758568 IUGRANT
64719905584250PAG1:758571 IUGRANT
64719905584250PAG1:758570 IUGRANT
64719905584250PAG1:758573 IUGRANT
64719905584250PAG1:758572 IUGRANT
64719905584250PAG1:758575 IUGRANT
64719905584250PAG1:758574 IUGRANT
64719905584250PAG1:758577 IUGRANT
64719905584250PAG1:758576 IUGRANT
64719905584250PAG1:758579 IUGRANT
64719905584250PAG1:758578 IUGRANT
64719905584250PAG1:758581 IUGRANT
64719905584250PAG1:758580 IUGRANT
64719905584250PAG1:758583 IUGRANT
64719905584250PAG1:758582 IUGRANT
64719905584250PAG1:758585 IUGRANT
64719905584250PAG1:758584 IUGRANT
64719905584250PAG1:758587 IUGRANT
64719905584250PAG1:758586 IUGRANT
64719905584250PAG1:758589 IUGRANT
64719905584250PAG1:758588 IUGRANT
64719905584250PAG1:758591 IUGRANT
64719905584250PAG1:758590 IUGRANT
64719905584250PAG1:790031 IUGRANT
64719905584250PAG1:790061 IUGRANT
64719905584250PAG1:790060 IUGRANT
64719905584250PAG1:790059 IUGRANT
64719905584250PAG1:790058 IUGRANT
64719905584250PAG1:790057 IUGRANT
64719905584250PAG1:790056 IUGRANT
64719905584250PAG1:789866 IUGRANT
64719905584250TAB IXGRANT
October 4, 2010 at 6:20 am
Try with exclusive lock (xlock) as an updlock can still be selected (but not updated).
October 4, 2010 at 6:28 am
same results with xlock
when I check sp_lock the type of the lock change to RID and mode X but i get the same results using updlock or xlock
October 4, 2010 at 6:43 am
I've read a bit more about it on BOL and here is what it says :
READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.
It looks like it knows that even though the record is held by an exclusive lock, it hasn't been modified yet so is not yet dirty and can then read it safely.
So i guess getting a lock is not a good trick to keep someone from doing a select.
I'll keep looking.
October 4, 2010 at 6:00 pm
gheinze (10/4/2010)
I tested this on a test Db with isolation level "read committed snapshot"
Right well that's different - now you are using the isolation level correctly.
The point of the row-versioning isolation levels (snapshot and read committed snapshot) is to allow readers to access data without taking Shared locks, which might block if someone else already holds an incompatible lock.
Because your query 2 is not taking shared locks, it won't block. There are a number of ways to workaround that - the most natural of which is to use a WITH (READCOMMITTEDLOCK) hint on query 2 so it does take shared locks. You would still need to take a incompatible lock in query 1 - XLOCK for example. (I misread your original question, so UPDLOCK is not what you need here.)
Example:
Query 1:
SELECT * FROM table_tp WITH (XLOCK) WHERE ID = 106435;
Query 2:
SELECT * FROM table_tp WITH (READCOMMITTEDLOCK) WHERE ID = 106435;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply