May 20, 2008 at 10:20 am
hi guys, I am creating a transaction in which it first does a select and then it does an update, my first question is : can a select create locks in a table, i read that a select never creates locks? , my second question, i need that when i am running my transaction in which first it does a select than no user can make modifications to the tables i am using in my transaction, is there a table hint or anything i could use in my transaction?
May 20, 2008 at 10:25 am
How about showing us what you are trying to do? Your code, table DDL, sample data, and expected output.
😎
May 20, 2008 at 10:55 am
A select creates shared locks on the table. If you want to limit access as part of a single transaction, a good idea, you need to put a UPDLOCK hint on the select query. Look up query hints in the Books Online for how to do it.
This will put the lock on the pages/rows retrieved by the select statement and keep them in place until the transaction commits or rolls back.
For more detailed help, you'll need to do what Lynn suggested.
----------------------------------------------------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
May 20, 2008 at 11:22 am
thank you, sounds exactly of what i need
UPDATE Production.Product
WITH (UPDLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE '00'
If i only do a select statement then i would need to use a
ROWLOCK ?
May 20, 2008 at 11:33 am
Depending on your concurrency requirements, you can set your transaction isolation to repeatable read, then the select will put shared locks on the rows to prevent updates.
If you are worried about inserts, use serializable isolation then no one will be able to insert into the range that you have selected.
Kyle
May 20, 2008 at 11:38 am
the serializable isolation should be put in my select transaction or in the transaction that does the insert?
May 20, 2008 at 11:41 am
You will want to set the transaction isolation level before you begin your transaction...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT something FROM somethingelse
UPDATE somethingdifferent
COMMIT TRANSACTION
You can go to Books Online for more details about isolation levels:
May 20, 2008 at 11:44 am
thank you, in your opinion this one is better than using with (rowlock) or WITH (TABLOCK)?
May 20, 2008 at 12:19 pm
I guess I am still lost on what you are trying do.
😎
May 20, 2008 at 12:20 pm
The isolation level is for every statement and object in the entire transaction. Using the hints can get you more granular control over specific objects.
I've not used the table hints you are suggesting, so I don't feel qualified to say "better", just different.
May 20, 2008 at 12:30 pm
Sorry, I guess I wasn't clear, you don't need an UPDLOCK on the UPDATE because that's what it does. For example, and this is a bit contrived, you need to select from a table and then based on that select, update some data in the table. You'd probably want the select to put locks on the row or pages it accessed.
DBA (5/20/2008)
thank you, sounds exactly of what i needUPDATE Production.Product
WITH (UPDLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE '00'
If i only do a select statement then i would need to use a
ROWLOCK ?
So no, don't use the ROWLOCK at all and take the UPDLOCK off the UPDATE query to put it on the SELECT query. You can use serializable transactions at the procedure level too. I really depends on what you're trying to do. You may have select statements that you don't want to be part of the transaction. Then you have to get granular on what you're doing.
----------------------------------------------------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
May 20, 2008 at 1:29 pm
thank you so much for your knowledge. 🙂
May 20, 2008 at 1:29 pm
So, trying to read minds here based on the posts so far, you want something like this?
begin transaction
select
*
from
Production.Product with (UPDLOCK)
where
ProductNumber like '00%';
update Production.Product set
ListPrice = ListPrice * 1.10
where
ProductNumber like '00%';
commit transaction;
May 20, 2008 at 9:04 pm
yes thank you!, i just need to figure out which one will work better with my transaction, your approach or the other one....
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
Select.....
Updates...
Thank you all!!!
May 20, 2008 at 9:36 pm
DBA (5/20/2008)
yes thank you!, i just need to figure out which one will work better with my transaction, your approach or the other one....SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
Select.....
Updates...
Thank you all!!!
The SELECT prior to the UPDATE in this case in unnecessary. All you need is the following:
begin transaction
update Production.Product set
ListPrice = ListPrice * 1.10
where
ProductNumber like '00%';
commit transaction
😎
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply