> 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
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
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.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!