Database Experience

[Brian Yennie]

> Someone told me MySQL handled it, I'm open to the suggestion that
> the information may be incorrect.  That's why I was asking the 
> question.
>
> Many people have been trying to move away from FileMaker Pro to MySQL
> because of these issues and multi-threaded issues.  A decent database
> should handle these things without the programmer having to re-invent
> with his/her own schemes.

I don't believe MySQL has any built-in record locking functionality- 
that's the bad news. The good news is that even if it did, it would be 
about the same amount of work in the simple case.

Suppose you were to write a front-end in Revolution, and had no control 
over the back-end (other than that it was a MySQL server sitting 
somewhere).

All you would need to do would be to create extra fields in your MySQL 
database, something like:

RECORD_LOCKS
lockedBy: yennieb
lockTime: 278236472

Whenever you queried for the record, you could as part of your query 
check whether it is already locked by someone else. I don't know if SQL 
if useful to you at this point, but you would be able to do something 
like:

SELECT * FROM mytable WHERE (recID = 37) AND (lockedBy <> yennieb) AND 
(lockTime >= XXXXXXX)

IOW, check to see if the record in question is locked by someone other 
than you, and within the allowable stale time.

Every time someone wanted to edit a record, you would check for an 
existing entry in the table to see if a different user had locked the 
record within a certain amount of time.

If not, create the lock by filling out those fields.

When the user saves changes, remove any locks they have. So long as 
they are actively using your front-end, you can refresh the lock. 
Otherwise it goes stale and someone else can get it. You could also 
quickly release all of the locks for a single user with something like:

UPDATE mytable SET lockedBy = '', lockTime = 0 WHERE lockedBy = 
'yennieb'

There are a few gotchas- for example, what happens if a user locks a 
record, walks away for too long, loses their lock, and _then_ tries to 
save changes. But no built-in database feature solves this for you.

On the other hand, if you were willing to also write a backend in Rev 
(i.e. a CGI or a Rev app that sits on the server), you could solve 
record locking lots of different ways outside of the database, even 
just keep a global variable with locks recorded in it.

Hope this helps- if you take this route with Rev and MySQL, I'm sure 
the list will be able to help with much of this when the needs are more 
specific. Some of it may seem overwhelming if you are seeing SQL for 
the first time, etc- but as a piece of the transition, record locking 
really sounds worse than it (usually) is in practice.

- Brian