List:General Discussion« Previous MessageNext Message »
From:David T. Ashley Date:June 4 2007 7:26pm
Subject:Lock Tables Question
View as plain text  
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 other
processes will block until the one modifying the database has finished).

The method I was using is something like:

LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable;
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 be
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 and
guarantee mutual exclusion.  Each operation I want to do would take at most
half a second, so another web process waiting that long won't make a
difference.  Simpler is easier for me.

Thanks.

Thread
Lock Tables QuestionDavid T. Ashley4 Jun
  • Re: Lock Tables QuestionGerald L. Clark4 Jun
    • Re: Lock Tables QuestionDavid T. Ashley4 Jun
      • RE: Lock Tables QuestionJerry Schwartz4 Jun
        • Re: Lock Tables QuestionDavid T. Ashley4 Jun
          • Re: Lock Tables QuestionGerald L. Clark4 Jun
            • Diff between restore from tar and recover from power outagemurthy gandikota4 Jun
              • Re: Diff between restore from tar and recover from power outageWm Mussatto4 Jun
                • Re: Diff between restore from tar and recover from power outageScott Tanner5 Jun
                  • Re: Diff between restore from tar and recover from power outagemos5 Jun
                    • Re: Diff between restore from tar and recover from power outageBaron Schwartz5 Jun
            • Re: Lock Tables QuestionDavid T. Ashley4 Jun
  • Re: Lock Tables QuestionBrent Baisley5 Jun
    • Re: Lock Tables QuestionDavid T. Ashley5 Jun
      • Re: Lock Tables QuestionBaron Schwartz5 Jun
        • Re: Lock Tables QuestionDavid T. Ashley5 Jun
          • Re: Lock Tables QuestionPaul McCullagh5 Jun