sqlite only supports 1 transaction?

Question

While using ADO.NET (maybe i am wrong, i dont know what its called) i notice that i can only begin a transaction with a connection and a command seems to have command.Transaction which gets me the transaction data but doesnt start a transaction itself? Actually while looking i see this in System.Data.SQLite

// Summary:
    //     The transaction associated with this command. SQLite only supports one transaction
    //     per connection, so this property forwards to the command's underlying connection.
    [Browsable(false)]
    [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
    public SQLiteTransaction Transaction { get; set; }

So SQLite only supports one transaction period? i tried opening another connection but then my transaction threw an exception about the DB being locked. So i cannot have more then one connection concurrent as well?

Answer 1

One transaction per connection, yes, but it can have more than one connection (each with its own active transaction).

Update: interesting. I didn't know about shared-cache mode. If your connection is using that mode, only one transaction is available for all the connections using the same shared-cache. See SQLite shared-cache mode.

Answer 2

I'm not sure about multiple connections, it probably has to do with the fact that a connection locks the file since SQLite is a file-based DB and not a server-based DB (on a server-based DB the server keeps all of the files locked and deals with concurrent connections).

You can only have one transaction OPEN at a time. This should be make intuitive sense, since everything that happens after you begin a transaction is in that transaction, until either a rollback or commit. Then you can start a new one. SQLite requires all command to be in a transaction, so if you don't manually open a new one, it'll do so for you.

If you're worried about nested transactions, you can fake them with savepoint. Documentation