![]() ![]() This has been improved somewhat with the addition of a WAL in newer SQLite versions. You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out.This is significantly faster than creating the index and then doing your inserts. If you have indices, consider calling CREATE INDEX after doing all your inserts.Note that more memory will be used for your database. Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Playing with page sizes makes a difference as well ( PRAGMA page_size).Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes. If your application crashes the data should be fine. There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. For older versions of SQLite - Consider a less paranoid journal mode ( pragma journal_mode).In addition, changing sql.isolation_level appears to make no difference to the behaviour.)Ĭan someone explain to me what's happening here? I need to understand this if I can't trust the transactions in the database, I can't make my application work… (I should also add that if I put the begin and commit inside the inner call to executescript then it behaves correctly in all cases, but unfortunately I can't use that approach in my application. However, if I replace the calls to c.execute() to c.executescript(), then it works (i remains at 99)! I get this: sqlite3.OperationalError: cannot rollback - no transaction is active Now I'm calling BEGIN and COMMIT explicitly: import sqlite3 This behaves in precisely the same way - i gets changed from 99 to 1. Here is another test program, which explicitly calls commit() and rollback(). I'm expecting it to remain at 99, because that first update should be rolled back. However, when I run it, I get the expected SQL error… but the value of i is set from 99 to 1. This causes the SQL script to fail on the second line, after the update has been executed.Īccording to the docs, the with sql statement is supposed to set up an implicit transaction around the contents, which is only committed if the block succeeds. You may notice the deliberate mistake in it. Here is the schema for my test database (to be fed into the sqlite3 command line tool). I'm really confused by this I've used sqlite a lot in other languages, because it's great, but I simply cannot work out what's wrong here. I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |