Begumisa Gerald M <beg_g@stripped> wrote on 02/22/2005 02:03:43 AM:
> Hi,
>
> I'm writing an application that uses InnoDB tables to provide
> transactional integrity. The front-end is a web-based interface.
>
> I'd like to know - is there a way one can issue a query to test whether
a
> particular set of rows (or row) has already been locked by another
session
> - rather than the default action of waiting for quite long for the lock
to
> be granted.
>
> The idea is so that a person using the front-end may know whether the
> information they are attempting to access is "unavailable" at the time.
> There will be situations where rows will be locked exclusively for
> prolonged times and I wouldn't want the web application users to be
> subjected to these lengthy delays but rather be told to try later.
>
>
> Regards,
> Gerald.
>
Are you saying that you want to use a transactional or table lock in place
of an application-level mutex object? I think you are trying to ask the
DB to do more than it is intended to do (not that it couldn't do what you
ask but it is unusual to hold a table lock or transaction open for any
intentional length of time. Usually the database sets and releases locks
like that in response to a series of statements on the order of
sub-seconds, not for the several seconds to minutes that may be required
of an application-level lock).
I have a similar situation (another user may already be "editing" a
particular record. only one user can edit a record at any one time) in one
of my web-based applications. I created a new field to hold the ID of the
application user that has the exclusive privileges on that record. That
way the application deals with application-level logic (no more than one
editor at a time) and I use db-level transactions to set and unset the
"editor" field as appropriate. The record is locked for the shortest
length of time and you can easily and quickly check the value of the field
so that your users can know immediately if they have rights to edit the
record or not.
It takes a bit more logic written into your application but the
performance gains and the speed of the user-feedback is well worth the
effort. Opening a transaction on one page request and closing it on
another (as when the results are submitted) will be VERY difficult for you
to manage as transactions are connection-specific and unless you are using
a pooled or global connection variable, you will be creating and
destroying connections rather frequently. It's generally not a good idea
to put a DB-level lock (TABLE lock or open transaction) on a record to
enforce an application-level rule, especially in the mostly-stateless,
asynchronous world of web-based development.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
