List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:December 20 2007 8:14pm
Subject:Re: Bug: Different data for different connections
View as plain text  
Hi,

On Dec 20, 2007 2:26 PM, Yves Goergen <nospam.list@stripped> wrote:
> On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> > But when I set that column to NULL
> > with phpMyAdmin, my application still reads the old data from the
> > database. phpMyAdmin keeps telling me that the value is actually NULL,
> > which I just entered. Whereas the persistent PHP connection doesn't see
> > the new data and keeps reading the previous one.
>
> Here's more facts: My application disables autocommit mode right at the
> beginning. I thought this would be a good compatibility measure to make
> MySQL more similar to the "big" DBMS where my app should also run later.
> And each of my writing operations is done in a separate transaction that
> is started and commited (or rolled back) with PDO's methods which should
> not be too much different from the corresponding SQL statements.
>
> I thought that disabling autocommit mode makes no difference at all,
> when I only write to the DB inside of transactions, but when I remove
> that line from my code, the bug seems to go away. Here's my theory:
>
> * Disabling autocommit starts a new transaction, according to the MySQL
> manual. When I then start my own transaction, I'm at level 2. MySQL
> needs to support nested transactions for this to work out. A COMMIT
> statement will only commit the innermost transaction.
>
> * When I start a transaction and then write something to a table which
> is later overwritten from another thread, I still see my own data as
> long as the outermost transaction is not finished. Also, data that I
> write in a transaction must not be locked and can be overwritten from
> another thread.
>
> If this is both true, I see that this is my fault. I write the new
> HtmlContent, then phpMyAdmin sets it NULL again but my app still sees
> what it has just written (because of the persistent transaction over
> multiple requests).
>
> However, if MySQL doesn't support nested transactions or data written in
> a transaction will be locked, this is not an explanation for what I
> experience.

It doesn't support nested transactions.  What you're seeing is the
effects of MVCC.  The InnoDB section of the MySQL manual explains it.
Thread
Bug: Different data for different connectionsYves Goergen20 Dec
  • Re: Bug: Different data for different connectionsYves Goergen20 Dec
    • Re: Bug: Different data for different connectionsBaron Schwartz20 Dec
      • Re: Bug: Different data for different connectionsYves Goergen20 Dec
        • Re: Bug: Different data for different connectionsBaron Schwartz20 Dec
          • Re: Bug: Different data for different connectionsYves Goergen20 Dec
  • Re: Bug: Different data for different connectionsMartijn Tonies20 Dec
    • Re: Bug: Different data for different connectionsYves Goergen20 Dec
  • Re: Bug: Different data for different connectionsMartijn Tonies20 Dec
    • Re: Bug: Different data for different connectionsYves Goergen20 Dec
  • Re: Bug: Different data for different connectionsMartijn Tonies20 Dec
    • Re: Bug: Different data for different connectionsYves Goergen20 Dec