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
It doesn't support nested transactions. What you're seeing is the
effects of MVCC. The InnoDB section of the MySQL manual explains it.