Can different connections of the same sqlite's database begin transactions concurrently?

Question

I met with a strange problem about sqlite3. I obtained different connections of the same database file using open() method. connection 1 begins a transaction, and connection 2 begins another transaction, which is to update several records of a table. Then connection 1 commit the transaction, followed by connection 2 commit its transaction. But I found that the update command of connection 2 is never actually update the record in the database. There is no exception throwed during this procedure. I don't know why the problem occurs. Can anyone explain the reason to me?

Thank you very much!

Answer 1

If you read the SQLite documentation, you will see that it supports multiple connections for reading only, you cannot write to the database from mulitple connections, because it's not designed for that.

http://www.sqlite.org/faq.html#q5

Answer 2

Unless you use BEGIN IMMEDIATE to initiate your transactions, you run the risk of having to rollback and retry them. A BEGIN does not do any locking; subsequent UPDATE or INSERT gets the lock, and you need to check the result code to see if they fail. See this page on transactions and this one on locks.