List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:May 10 2007 7:51pm
Subject:Re: Which is a better design?
View as plain text  
If you are dong as two seperate queries, I recommend using a
transactional table type setting the read isolation mode to repeatable
read and doing both your queries within a single transaction.

(David, sorry about the double send)

 - michael

On 5/10/07, David T. Ashley <dashley@stripped> wrote:
> On 5/10/07, James Tu <jtu@stripped> wrote:
>
> > David:
> > I definitely can get the result set using one query, but what I do
> > with the result set has me thinking about breaking it up into two
> > queries.
>
>
> <Technical Details Omitted>
>
> Ah, OK, I misunderstood.  You want to (get two results, each of which is
> useful individually) rather than (issue two queries, then iterate in PHP to
> combine the query results).
>
> Two queries seem fine in that case.
>
> Just one caution:  be aware that another process (such as a web user) can
> sneak in in between your two queries and modify the database and render the
> two sets of query results inconsistent with one another.
>
> To give you an example, suppose you issue three queries in order (I'm going
> to botch the syntax here):
>
> SELECT COUNT(*) FROM USERS;  (call this A)
>
> SELECT COUNT(*) FROM USERS WHERE IDX <= 10;  (call this B)
>
> SELECT COUNT(*) FROM USERS WHERE IDX > 10;  (call this C)
>
> It is very possible (in the presence of other simultaneous database
> activity) that A != B + C.
>
> It depends on the application whether this is significant.
>
> Table locking is the easiest way to prevent this if it matters.
>
> Dave.
>


-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
Thread
Which is a better design?James Tu9 May
  • Re: Which is a better design?John Meyer10 May
  • Re: Which is a better design?David T. Ashley10 May
    • Re: Which is a better design?James Tu10 May
      • Re: Which is a better design?David T. Ashley10 May
        • Re: Which is a better design?James Tu10 May
          • Re: Which is a better design?David T. Ashley10 May
            • Re: Which is a better design?Michael Dykman10 May