Record Locking Conflicts In SQL Server

Hello Community

In ACCESS I used to put an APPLICATION.LOCK around pages with database updates to help counter OPTIMISTIC record locking conflicts. This was primarily because two people updating DIFFERENT records in ACCESS could still get in each others way because of ACCESSes lame way of locking a page of data rather than a true record lock.

In SQL Server, is there any point in taking this type of approach if no two people will ever be updating the same record at the same time? Or put another way, will there ever be a record locking conflict if there's no chance of the same record being updated by multiple users at the same time?

Thanks for any input on this.

PeteB

 

Hi Bruce


Sorry, but I don't quite get your response in respect of where I was coming from.


Imagine this simple SQL Server database scenario:


1)A person would only ever be UPDATING a single record in a table at a time (no transaction as such)


2) That one record was THEIR OWN and no one else would ever be UPDATING it


3) Multiple people might be updating THEIR OWN single records at the same time but (see point 2 above) no one else would ever be touching that record one


4) All of these people (maybe up to 25) might be READING the same records from OTHER tables while this was going on, but only updating their own


In a SQL Server environment would you have to take any programatic action to ensure that no person's record locks would interfere with anybody elses?


In ACCESS you WOULD because although no two people would be UPDATING the SAME record at the same time, the PAGE on which the two records lived might be locked and cause a problem.


My understanding of SQL Server is that true RECORD level locking is in place so in the above scenario it wouldn't be a problem.


I hope I'm not being naive in my understanding.


Thanks


PeteB