I’m developing an application where sometimes two (or more) users will edit the same record practically at the same time. It happens that the change made by the first user will be lost without any notification to the second user.
I’m wondering what’s the best approach to a problem such as this?
It is now solved with the last modified date comparision. Thanks everyone. 
Very much should be a code-based problem – you really should not start locking records on SELECT statements waiting for an eventual update. You will melt your DB server at best.
It looks as if you and Rudy are talking about two different scenarios.
Rudy is talking about a physical record lock that occurs when the DBMS in question. This ensures that two sources cannot update the same data at the same time.
You are talking about a logical lock of the data where a record is “locked” once a user starts to edit the data on a screen, and unlocked once the user is done and an update occurs. This has nothing to do with the efficiency of the DBMS in question, more the manual process involved.
That being said, the last modified date comparison you mentioned would be effective to notify the user if someone else has modified the record since they started their process. Most implementations I’ve seen of that will show the fields (other than the ones changed by the user) that have been changed and ask the user to confirm.
That being said, that’s more of a logic/code based problem rather than a database problem.
It may take a while before the lock is released in this situation, so it is not pratical. I want to assure that the user is notified whenever the record is changed by another user during the process. I’ve just read about a “last modified” timestamp column, not sure if that’s the better method though.
unless you have a myisam table under mysql (or – ugh – ms access), your database will lock only that individual row, and other users can go ahead and edit other rows
of course, if another user goes to update that same row, she will be locked out until the first user finishes
this is what you want to happen, right?
If I get it right, the transaction would apply a lock to the record.
I think this is fine when the transaction finishes in a reasonable time, but I can’t assure that. There can be situations where the transaction remains active for a long time. The flow:
Form loads -> Transaction starts/SELECT statement -> User make the changes -> UPDATE statement/Transaction ends
Big difference between exactly at the same time and nearly at the same time – if it is exact, then Rudy is correct – transactions should shield you. If it is pretty close to exact, most of the default configurations in the default ORMs (linq 2 sql in particular) will check to see if the record has been changed since edited and throw an exception of some sort. Now, if the user is loading the changed record after it has been committed, then you have to cover it in code. Which probably isn’t a bad idea anyhow if it is a key feature.
place a transaction block around the SELECT and UPDATE statements
assuming you have a SELECT in front of your UPDATE
if not, you don’t really have a problem