I think you're missing the concept of a transaction in the database sense.
The idea behind a transaction is that you can perform multiple steps and if
you don't complete all steps, any changes are reversed. The reversal process
is handled by the database.
A good example is moving money from bank account A to B. You specify how
much to move and that amount is debited from account A and credited to
account B, 2 steps. If the first step happens, but not the second, then the
first step needs to be reversed.
Until the transaction is complete, anything querying the data needs to see
bank account in it's state before any transaction started, a type of
You seem to be trying implement all this manually, which you would need to
do if you are using MyISAM based tables. But you may not need to use
transactions at all if your data does not have real time dependencies.
On 6/4/07, David T. Ashley <dashley@stripped> wrote:
> I decided to go with a simple paradigm for my web-based database. Rather
> than transactions, each process locks the entire database while it is
> changing something, then unlocks it. This just serializes access (all
> processes will block until the one modifying the database has finished).
> The method I was using is something like:
> LOCK TABLE thistable, thattable, theothertable,
> Do whatever is needed;
> UNLOCK TABLES;
> I probably botched the syntax above.
> Rather than enumerate every table in the database (which could get to be a
> long list), I wondered if it is possible to just lock one table, with the
> gentleman's agreement that in order to modify the database, every process
> must first lock that particular table.
> For example:
> #1)LOCK TABLE x;
> #2)Make modifications to tables x, y, and z;
> #3)UNLOCK TABLES;
> Are there any race conditions in just using one table for this purpose?
> For example, SQL guarantees that a given SQL statement is atomic.
> But it is guaranteed that #2 will complete before #3 above?
> If every process uses the same rule, can anything unexpected happen?
> One more note: I'm sure that many of the skilled users on this list will
> tempted to advocate more sophisticated methods. I appreciate all advice,
> but I'm just looking for an easy way to serialize access to my database
> guarantee mutual exclusion. Each operation I want to do would take at
> half a second, so another web process waiting that long won't make a
> difference. Simpler is easier for me.