Hi, On Dec 20, 2007 2:26 PM, Yves Goergen <[EMAIL PROTECTED]> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
