lock on select - SQL Server Forums

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

SQL Server 2008 Forums

SQL Server Administration (2008)

lock on select

Author

Topic

abit
Starting Member

6 Posts

Posted - 2010-10-01 : 09:32:53

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!!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-01 : 09:49:01

Try setting transaction level to serializable or using it as a query hint "SERIALIZABLE"

Alternate way, though not recommended is to use TablockX.

abit
Starting Member

6 Posts

Posted - 2010-10-01 : 10:01:11

quote:
Originally posted by pk_bohra

Try setting transaction level to serializable or using it as a query hint "SERIALIZABLE"

Alternate way, though not recommended is to use TablockX.

Thanks if I use transaction level to serializable I affect other queries.

Ill try with TablockX, and test if its fits in my scenario.

I'll comments my results.

Thanks!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-01 : 10:08:53

Instead of changing the trasaction isolation level setting, you can use it in your query. This will not affect other queries.

See example below:

BEGIN TRANSACTION
SELECT COUNT(*) FROM SalesHistory WITH(SERIALIZABLE)
-- Do other things.

Commit Transaction

abit
Starting Member

6 Posts

Posted - 2010-10-01 : 10:24:45

quote:
Originally posted by pk_bohra

Instead of changing the trasaction isolation level setting, you can use it in your query. This will not affect other queries.

See example below:

BEGIN TRANSACTION
SELECT COUNT(*) FROM SalesHistory WITH(SERIALIZABLE)
-- Do other things.

Commit Transaction

Thanks for your quick reply.

I discart that option because, dont work for me (or my example is bad)
Ill show you.

connection 1


use database

begin transaction 

SELECT * FROM table with (serializable)
where id = 100010

WAITFOR DELAY '00:00:10'; --dalay for simulate other queries

rollback transaction

connection 2


use database
select * from table
where id = 100010

When I test this, the second connection shows me the results inmediately.

When I use tablockx the second connection shows me the results as soon as the first connection rollbacks the transaction.

I think this happend becouse serializable use shared lock when is a select statement, so other transaction can read date.
When I put tablockx hint, with sp_lock i can see it use a exclusive lock.

Thanks again!!!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-01 : 11:53:33

You are welcome

In the example given by you for connection 2

use database
select * from table
where id = 100010

AFAIK since you are not using any transaction, it is allowing. Had it been between the transaction, it would not have allowed reading.

abit
Starting Member

6 Posts

Posted - 2010-10-01 : 15:06:25

quote:
Originally posted by pk_bohra

You are welcome

In the example given by you for connection 2

use database
select * from table
where id = 100010

AFAIK since you are not using any transaction, it is allowing. Had it been between the transaction, it would not have allowed reading.

ok, Ill try and I will post the results

thanks again!

abit
Starting Member

6 Posts

Posted - 2010-10-04 : 07:14:50

quote:
Originally posted by pk_bohra

You are welcome

In the example given by you for connection 2

use database
select * from table
where id = 100010

AFAIK since you are not using any transaction, it is allowing. Had it been between the transaction, it would not have allowed reading.

Tested the second quety in a transaction and get the same results
Thanks again!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-04 : 08:15:11

Glad to help

abit
Starting Member

6 Posts

Posted - 2010-10-04 : 12:34:20

jeje sorry for my english, I want to say that I get the same results that without putting the query in a transaction.

For now, I will use tablockx!

Thanks again!!

quote:
Originally posted by pk_bohra

Glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources