List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 20 2007 7:34pm
Subject:Re: Bug: Different data for different connections
View as plain text  
Yves,


> 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.

The transaction could very well be committed for writing the data, 
but you could be reading the data in a "snapshot" transaction, which
means you get the same data despite other transactions writing the
data.

Are your tables InnoDB? If so, the snapshot transaction is giving you
a static view on the data and your own changes, while your PHPMyAdmin
commits the NULL write. Your application keeps on seeing your own
changes, cause it did not end the snapshot transaction.

Hope this helps.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
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