Begumisa Gerald M <[EMAIL PROTECTED]> 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
