![]() ![]() I have several multi-GB SQLite DBs and I usually get better performance with it than with a conventional client-server RDBMS due to the lack of network overhead, etc. I see SQLite has a super-efficient heavy duty DB, specially useful for storing and operating on a large amount of records. As it is, it's making too many assumptions that will often times be wrong and I'd like to try to change your mind about this, so let me explain where I'm coming from. It would be great if you would reconsider unblocking this behavior and leave isolation concerns to the users. For the purposes of the previous two paragraphs, two database connections that have the same shared cache and which have enabled PRAGMA read_uncommitted are considered to be the same database connection. And the application can UPDATE the current row or any prior row, though doing so might cause that row to reappear in a subsequent sqlite3_step().Īnd the docs finish with the same warning/recommendation to developers:Īs long as the application is prepared to deal with these ambiguities, the operations themselves are safe and will not harm the database file. The application can also INSERT new rows into the table while the SELECT statement is running, but whether or not the new rows appear in subsequent sqlite3_step()s of the query is undefined. If a future row is deleted, however, it might happen that the row turns up after a subsequent sqlite3_step(), even after it has allegedly been deleted. ![]() It is also safe (in the sense that it will not harm the database) for the application to delete a row that expected to appear later in the query but has not appeared yet. The undefined outcome seems to only refer to rows written ahead of the cursor: So writing to current or previous rows of the cursor is safe and well-defined. ![]() " and starts stepping through the output of that statement using sqlite3_step() and examining each row, then it is safe for the application to delete the current row or any prior row using "DELETE FROM table WHERE rowid=?". If an application issues a SELECT statement on a single table like "SELECT rowid, * FROM table WHERE. I would prefer being responsible enough to choose when I might need to execute a UPDATE inside a SELECT (and deal with the potential problems that might create) than not be able to do that at all.Ī simple, safe example would be updating a table not referenced by the current SELECT query. And hence, developers should diligently avoid writing applications that make assumptions about what will occur in that circumstance. There is no good way to know whether or not a SELECT statement will see changes that were made to the database by the same database connection after the SELECT statement was started. In some cases, it might depend on the content of the database file, too. In particular, whether or not the SELECT statement sees the concurrent changes depends on which release of SQLite is running, the schema of the database file, whether or not ANALYZE has been run, and the details of the query. But what about changes that occur while the SELECT statement is running? What if a SELECT statement is started and the sqlite3_step() interface steps through roughly half of its output, then some UPDATE statements are run by the application that modify the table that the SELECT statement is reading, then more calls to sqlite3_step() are made to finish out the SELECT statement? Will the later steps of the SELECT statement see the changes made by the UPDATE or not? The answer is that this behavior is undefined. ![]() And the SELECT statement obviously does not see any changes that occur after the SELECT statement completes. Within a single database connection X, a SELECT statement always sees all changes to the database that are completed prior to the start of the SELECT statement, whether committed or uncommitted. Regarding your rationale and after reading the documentation, I would say that undefined != unsafe. Sorry for only following up now on this but I was on holidays with limited internet availability. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |