ROWLOCK

  • 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

  • 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

  • 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.

  • 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

  • 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
  • Thanks! Probably I misunderstood. thanks for clarification.

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

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

  • 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
  • Isolation level setting comming from application only i can edit triger that s why i try to find a hint

  • 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
  • I think this the same think

    REPEATABLE isolation level and row base locking

    SELECT ID FROM TESTROWLOCK (HOLDLOCK,ROWLOCK)

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

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

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