Help with Innodb record locking please

[dalchri]

I am writing an order entry app and would like to implement multi user access as follows using Innodb:

1) The first person into an order (a record in tblOrder) gets read-write access.
2) Successive people get read-only access until first person gets out of the order and are warned about this before they try to open the order.

I've tried the following:

SELECT * 
FROM tblOrder 
WHERE ID = 1
LOCK IN SHARE MODE;

Problems:
1) Other records (other than ID = 1) seem to be blocked from updates so in other words, users editing other orders can't save their changes.
2) I don't know how I can tell that an order is locked before the user opens it. I know that updates are blocked but I need to warn the user about this before they make a bunch of changes with the expectation that they can save them.
3) I don't know how to release the lock on the record if the user makes no changes but exits the order.

It would be useful to be able to see what record locks are in place so I can learn the behavior a little better. Anyone know how to list what records are locked?

Any suggestions on a standard way of implementing this behavior?

 

[ericbrunson]

I'm not sure if InnoDB supports row level locking, I'm only sure that it supports table level. I'd check but ever since Google went public they seem to be a lot less reliable.

First ask yourself, how often are two people really going to be editing the same order?

If that's a real issue, the consider this tactic:

1) read the information and display it, allowing changes, but remembering the original values.
2) construct your update to only write the changed values back to the record in the database, then if someone changed something different in the order, it won't get overwritten.

But I've rarely had occasion to *truly* require locking.

 

[dalchri]

It happens all the time.

Sales will get on the phone with our drafting department or our costing department or our processing department and before you know it, several people are in the order at once. Then, someone walks away from their computer for 2 hours, comes back, and realizes that the changes they made that they can't remember exactly what they were can't be saved cause they left the order open and never saved it.

This is why our users "need" to know up front if they will be able to make changes or not. My current tactic is to mark the order record with the user id of the user that has read-write access combined with a timestamp. Once the order is closed, the user id is cleared from the order record. If more than an hour goes by without any activity (perhaps their computer crashed), other users are allowed back into the order.

I would prefer a solution from the DBMS though as I feel it would be more reliable. For example, I think that MySQL might be able to detect when a connection is broken due to a client crashing and would clear the locks associated with that connection. It would eliminate my "one hour timeout" that I have instituted.

 

[ericbrunson]

Check out the section on locks in this article:

http://mysqld.active-venture.com/InnoDB.html

Though, I really don't think it's a good design. If two people really need to update the order, then waiting an hour until someone gets back from lunch or the lock times before you can do your job seems extremly unproductive.

You really should consider a strategy like I outlined, the only time you have contention is when two people are updating the same field. If you have two people updating the same field with differing information ant the same time, then there's probably a major flaw in the schema or a serious lack of communication in the organization.

 

[Bastien]

innodb does support row locking, table locks should not be required for transactions

http://dev.mysql.com/doc/mysql/en/InnoDB_overview.html

Bastien

Cat, the other other white meat