Vagif


Hello,

I have a stored procedure where I select data using a lock hint, e.g.:

select * from MyTable with (UPDLOCK)

Depending on arguments passed to a procedure I need to be able to turn locking on and off, otherwise I will have to create two versions of a stored procedure (or two SELECT blocks in one) with duplicate contents that differ only in lock directive.

Is there any way in SQL server to parameterize locking

Thanks in advance





Re: Is there a way to parameterize lock type?

Umachandar Jayachandran - MS


No. You will have to use different SELECT statements. But what are you trying to do by having different locking behavior for the SELECT statement. Typically you want to avoid locking data for update since that can have serious performance penalties since you need to do it in a transaction for it to have any effect and depending on how long you keep the transaction open the blocking effect can be bad.





Re: Is there a way to parameterize lock type?

Louis Davidson

This seems to work at a statement level.

create table test
(
testId int
)
go
create procedure test$select
(
@isolationLevel varchar(30)
) as
begin
if @isolationLevel = 'READUNCOMMITTED'
set transaction isolation level read uncommitted
else if @isolationLevel = 'READCOMMITTED'
set transaction isolation level read committed
else if @isolationLevel = 'REPEATABLEREAD'
set transaction isolation level repeatable read
else if @isolationLevel = 'SERIALIZABLE'
set transaction isolation level serializable

begin transaction

select *
from test

waitfor delay '00:01:00'

commit transaction
end
go
test$select 'REPEATABLEREAD'

Checking this in the sys.dm_exec_sessions shows that the isolation does get set, but will this hurt the plan that is created by the stored procedure







Re: Is there a way to parameterize lock type?

Mugambo

Louis:

From what I remember of testing something like this it slows it down a bunch when the isolation is changed from current setting to a different setting. From what I remember if the setting stays the same it didn't seem like there was a slowdown. This is definitely something I will re-test when I get a chance.


Dave





Re: Is there a way to parameterize lock type?

Vagif

Basically I want to avoid SQL code duplicates. The stored procedure needs to select some stuff before updating data. Selection can be done using other stored procedure, but to avoid deadlock I want to select data with UPDLOCK hint. I know that data are going to be updated, so locking them earlier just helps against deadlock.

And if I can't parameterize it, I need to have two versions of SP with data selection: one that just selects data (for readonly purposes) and the other one that selects with update lock.






Re: Is there a way to parameterize lock type?

Vagif

Lois,

Thanks for the recommendation. Unfortunately this is not what I am trying to achieve. I don't want to change isolation level - just to set a lock hint (see my other comment that describes why I want to do this).






Re: Is there a way to parameterize lock type?

Louis Davidson

I would pretty much suggest that that would be the best T-SQL implementation. There is no way to parameterize a hint on an individual object in a query.

T-SQL is not a flexible language because one of the most important parts of a T-SQL query is the plan that it needs to save to make it faster to reexecute the same statement. So lots of design considerations have clearly been made to facilitate reuse of plan, not code.






Re: Is there a way to parameterize lock type?

Mugambo

( I did test this after the last post, but despite what I previously said, I did NOT see any measureable slowdown because of changing the isolation levels. I was wrong. Sorry about that. )



Re: Is there a way to parameterize lock type?

Vishnu Chauhan

I think you can also use dynamic sql,

store the statement in a variable as per your req. and use exec(@varname) to run the statement.