Hi Shawn,
Thanks for taking time to respond to this.
> [...]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).
Actually this is a bit different: On one side of the database is a
telephony application which requires that certain information in the table
does not change for the period of time that a call is active - and the
only way such information would change is if it were modified from the web
interface. Because within the [multithreaded] telephony system there are
application level locks that enforce concurrency in this respect - testing
a mutex lock here would tell whether an account is available or
unavailable quite easily - without having to attempt to lock a particular
set of rows in the DB.
The issue comes in ensuring that the web application does not modify that
information in the table while a call is in progress. To me it looked
like a DB level solution of some sort would be the best.
> 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.
That's an interesting way to do it, actually - it'll be abit of work but I
think its not bad at all. I was just curious about whether you could
"test" for a lock in InnoDB - it'd save alot of trouble.
> 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,
Actually, as I explained its abit different - its not between page
requests that I want to open / close transactions but rather to make sure
the web application users do not modify table information while a call is
in progress (i.e while the telephony application is "holding" the lock to
a given user's information).
> you will be creating and destroying connections rather frequently.
Actually not, the web application architecture is abit as below:
[webserver]-{ENV}-[Small CGI Program]-{TCP/IP}-[Daemon]===[MySQL]
^^^
I.e there's a pool of persistent connections to the MySQL database that
are maintained and reused / shared.
> 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.
I see. What are some of the reasons why this would be so?
Thanks!
Rgds,
Gerald.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
