Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ROWLOCK Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 12:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
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
Post #1432479
Posted Tuesday, March 19, 2013 1:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1432497
Posted Tuesday, March 19, 2013 1:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 10:20 PM
Points: 45, Visits: 309
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.
Post #1432501
Posted Tuesday, March 19, 2013 1:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
In my application ı set the transaction first

BEGIN TRAN
UPDATE TESTROWLOCK SET ID=ID WHERE ID=2

SELECT ID FROM TESTROWLOCK WHERE ID=2

ı want to change

this

BEGIN TRAN

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

Post #1432505
Posted Tuesday, March 19, 2013 3:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1432540
Posted Tuesday, March 19, 2013 3:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 10:20 PM
Points: 45, Visits: 309
Thanks! Probably I misunderstood. thanks for clarification.
Post #1432542
Posted Tuesday, March 19, 2013 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736

I think ı can not change this sql script to lock that rows and then do something
Post #1432543
Posted Tuesday, March 19, 2013 4:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1432546
Posted Tuesday, March 19, 2013 4:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736

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 ı m trying to lock that records

Post #1432550
Posted Tuesday, March 19, 2013 4:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before the begin tran.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1432560
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse