RowLock not working properly

Question

Post reply

RowLock not working properly

sunkrajesh

SSC Rookie

Points: 43
More actions
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.

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

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

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 1

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

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

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.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning