Question on locking records?

[Apollo6]

I am using MSAccess 2000 to run against MySQL 4.0.12. What I need to have happen is when a user pulls up a record and begins updating it, another user on another workstation cannot access that same record.

Where do I begin with this? Is it a simple setting in the .ini file or using MySQLAdmin?

Thanks for any help.

 

[TonyGroves]

You can either:
(1) use a transaction (if you're using InnoDB tables):

http://dev.mysql.com/doc/refman/5.0/en/commit.html

(2) use LOCK TABLES:

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

 

[Apollo6]

I am using InnoDB tables. I was reading about the AutoCommit setting. Where does this flag get switched? Again, I am using MSAccess forms. The user pulls up a record in the form, makes some edits, clicks save, and that is it.

I just have never had to lock records in a multi-user environment using MySQL for the backend. I know how to do it in MSAccess but wanted to get away from using that for a backend.

Thanks again.

 

[TonyGroves]

If you are proposing to lock the record for as long as the user has it displayed on his screen, then that would be a bad idea; locks should only be held for as short a length of time as possible, with no pauses for user input, as other queries could be forced to wait until the lock is released.

A better approach would be that, when the record is about to be written, it is re-read and the record's timestamp compared with that originally read, and if it has changed in the meantime, the user could be informed and asked what he wants to do.

AUTOCOMMIT is set using SQL; it's explained in the first link given above. However, since you are only updating one record at a time, ordinary table locking should be adequate.

 

[Apollo6]

Hmmm... I understand you point about locking the record for the whole time the user has it on the screen but that is what they are wanting. Basically that if a physician has a patient pulled up on the screen, no one should be able to even look at that patient's data.

I guess I need to think about this one a bit.

Thanks for the tips!

 

[TonyGroves]

Also, it's not just the record that you lock, but the whole table.