sqlite3 transaction locking iterating result set behavior modifying underlying DB

Sqlite3 Transaction

Submitted by Eus
on April 25, 2010 - 11:55pm

After some time into web application development during my undergraduate study, I had wondered about the effect of modifying the underlying DB tables when a query result set over the DB tables are being iterated by a PHP script making an HTML table. Well, I believe that the result set is contained in its own memory and is detached from the underlying DB tables so the iteration will not be affected by any change to the underlying DB. How about the use of a cursor or a similar mechanism in iterating the result set? Well, I believe that the cursor operates on the result set memory instead of on the underlying DB. Well, those assumptions can be wrong and the DB can choose to employ locking. If locking is employed, how does it affect the other writers or readers? I think the sure way to answer the posed questions is by conducting some tests to see the behavior of the DB when using its API. Below is the result of conducting some tests on Sqlite3 to answer the above questions when I was working on Service Publishing AP.