List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 22 2005 3:23pm
Subject:Re: InnoDB Row Lock test (A query maybe?)
View as plain text  
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 
> particular set of rows (or row) has already been locked by another 
> - rather than the default action of waiting for quite long for the lock 
> 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

InnoDB Row Lock test (A query maybe?)Begumisa Gerald M22 Feb
  • Re: InnoDB Row Lock test (A query maybe?)SGreen22 Feb
    • Re: InnoDB Row Lock test (A query maybe?)Begumisa Gerald M23 Feb
RE: InnoDB Row Lock test (A query maybe?)Philipp Snizek22 Feb
  • Re: InnoDB Row Lock test (A query maybe?)Rhino22 Feb