Post reply

RowLock not working properly

  • May 20, 2010 at 5:31 am

    #386975

    we are developing one gate pass application in one project. In this there is a requirement that when gate pass is entered for a person, if he comes 2 hours late, that record should be made read only. For this we are running an update query every 1 minute using sql job. This query is checking the start time of the gate pass and changing the state of that record to Late. Based on this we are checking in the UI, to make the record read only. But the query is not working properly i.e.. records are not becomming late.

    I thought this could be a problem of table locking and made all the update queries and insert queries based on the Gate pass table to include with Rowlock. Even then records are not updating properly. Do i need to include with Rowlock in select statements also. Or there is another way to do this.

  • May 20, 2010 at 6:03 am

    #1170036

    Can you please post your quires with some dummy names?

    Thanks,

    Nagesh S

    Nag
    ------------------------------------------------
    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • May 20, 2010 at 6:10 am

    #1170037

    rowlock is just a hint that tells SQL Server what kind of locking granularity it should use when running the SELECT, UPDATE or INSERT, it has nothing to do with the fact that your rows are not being updated. That part has to be caused by a logical mistake in you UPDATE statement.

  • May 20, 2010 at 6:47 am

    #1170067

    You should not be using ROWLOCK like that. There is no way to make a single row read only within the structure of the table. You have to make it logically read only, meaning, in your update statement you can include a WHERE statement that will prevent records that have been marked as late from being updated. Trying to use ROWLOCK like you are is just placing locks on the row for the duration of the query. It doesn't affect other queries coming along later.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

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