1

I have found similar question on stackoverflow but it is solely focused on performance and answer is pretty obvious: creating new connection for each query = slower performance (how much slower? it depends)

I am more worried about transaction isolation aspect. In SQLite documentation I have found out that there is no isolation within a single connection. I am using sqlite3 library in my electron desktop app and I was planning on sharing a single connection throught the whole time that my app is running (to make it a little faster) but now I am wondering if it is safe. If there is no isolation within a single connection then is this scenario possible?:

Client triggers 2 unrelated processes
1.

db.serialize(()=>{
  db.run("BEGIN");
  try{
    db.run("foo");
    db.run("bar");
  }catch(e){
    db.run("ROLLBACK")
  }
  db.run("COMMIT")
});

2.

db.run("another foobar")

1. and 2. are ran parallel so it is possible that 2. finishes somewhere in between of "begin" and "commit"/"rollback" from 1. Does that mean that it is possible for queries from 2. to be rolledback or commited by 1. even though they are entierly separate or is 2. using some implicit transaction to prevent this?

I think it is possible since there is no isolation within single connection but I might be missing something because I have never worked with SQLite and sqlite3 (or I might have missed something more basic) so I would like to confirm if this scenario is a potential danger of using single sqlite3 connection.

hdw3
  • 871
  • 10
  • 28
  • Opening the database for a single query and then closing it, and repeating for every query is incredibly inefficient. I don't know node or javascript, but in the languages I use, I always use one connection per thread/process for the lifetime of that thread/process (Using the appropriate sqlite threading mode), and don't try to have multiple queries running at once in a thread/process. – Shawn Jun 29 '21 at 20:18
  • I have exaggerated with new connection for every query but I am still worried about transaction isolation within single connection. Let's say that there are simultanous writes within single connection, is that dangerous? Can my explicit "ROLLBACK" roll the other write that was performed paralelly or are there some mechanisms that prevent this? Threading in node.js is awkward because js itself is singlethreaded but C++ APIS that node is using are different story but thats hidden. So I am wondering if there is even a scenario where parallel writes within single connection are possible. – hdw3 Jun 29 '21 at 21:09

0 Answers0