ROWLOCK

Question

ROWLOCK

ESAT ERKEC

SSCertifiable

Points: 5661
More actions
March 19, 2013 at 12:03 am

#269905

CREATE TABLE [dbo].[TESTROWLOCK](
[ID] [int] NULL,
[NAME] [varchar](100) NULL,
[SURNAME] [varchar](200) NULL
) ON [PRIMARY]
GO
TAB -1
I m trying
BEGIN TRAN
SELECT ID FROM TESTROWLOCK WITH(ROWLOCK) WHERE ID=2
TAB-2
SELECT ID FROM TESTROWLOCK WHERE ID=2
I want to ask that tab-2 select is give result but tab-1 is not commited why rowlock hint does not lock that rows

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

ESAT ERKEC (3/19/2013)
CREATE TABLE [dbo].[TESTROWLOCK](
[ID] [int] NULL,
[NAME] [varchar](100) NULL,
[SURNAME] [varchar](200) NULL
) ON [PRIMARY]
GO
TAB -1
I m trying
BEGIN TRAN
SELECT ID FROM TESTROWLOCK WITH(ROWLOCK) WHERE ID=2
TAB-2
SELECT ID FROM TESTROWLOCK WHERE ID=2
I want to ask that tab-2 select is give result but tab-1 is not commited why rowlock hint does not lock that rows

ROWLOCK just opens a shared lock which is compatible with other shared locks.

For locking a row based on a SELECT you would need to ask for WITH (UPDLOCK, TABLOCK) which takes an exclusive table lock, or you could use the SERIALIZABLE isolation level in the window with your transaction. Both options have high safety for your rows, but are very low on concurrency allowances.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

AJ@SQL SSC Eights! Points: 811 More actions · Answer 2

You may want to read more regarding ROWLOCK.

What I have read is: Row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 3

In my application i set the transaction first

BEGIN TRAN

UPDATE TESTROWLOCK SET ID=ID WHERE ID=2

SELECT ID FROM TESTROWLOCK WHERE ID=2

i want to change

this

BEGIN TRAN

SELECT ID FROM TESTROWLOCK (hint row base lock ?) WHERE ID=2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

AJ@SQL (3/19/2013)
What I have read is: Row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

That's not true. The Rowlock hint alone means that SQL will take whatever type of locks it needs (shared in this case) at the row level to start, potentially escalating to table if it needs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AJ@SQL SSC Eights! Points: 811 More actions · Answer 5

Thanks! Probably I misunderstood. thanks for clarification.

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 6

I think i can not change this sql script to lock that rows and then do something

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Not sure what you're trying to do. A select by default takes shared locks, those locks are released as soon as the row is read (in the default isolation level anyway). Also, shared locks don't block shared locks, so two selects against the same table won't block each other.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 8

I m trying to do that

I want to be sure

BEGIN TRANSACTION

SELECT * FROM SomeRecords where id=1

............

Some Select and if statment

..........

UPDATE SomeRecords set=somerow where id=1

UPDATE SomeRecords

COMMIT TRAN

I have to sure while the commit transaction no body change my records i m trying to lock that records

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before the begin tran.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 10

Isolation level setting comming from application only i can edit triger that s why i try to find a hint

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Right before the BEGIN TRANSACTION, put SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, since you are writing the begin tran, you can write that too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 12

I think this the same think

REPEATABLE isolation level and row base locking

SELECT ID FROM TESTROWLOCK (HOLDLOCK,ROWLOCK)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Leave out the hints, just set the isolation level before you start the transaction. There's no good reason to use hints over the explicit isolation level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 14

it is trigger in that s why i set the hints application before update set isolation level read commited

Is it true to change isolation level in triger

UPDATE isolation level read commited

triger isolation level in repeatable read ?

and i test it when i used that hints in transaction nobody can update that rows and i want it