briany at qldlearning.com
Tue Mar 23 00:49:00 CST 2004
> 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
> 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
All you would need to do would be to create extra fields in your MySQL
database, something like:
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
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 =
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.
More information about the use-livecode