qsqlquery database locked with SQLITE

[Cerberus]

I'm using Qt5 with an QSqlDatabase with QSQLITE. I initialize the Database with


db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("test.db");
if (!db.open()) { ...}


Two instances of the program or running on the same machine therefore both or using the same test.db database file. The initialization of the database works fine, but when executing



bool DataStorageHandler::executeStmt(QString stmt) {
QSqlQuery qry(this->db);
qry.prepare(stmt);

if (!qry.exec()) {
qDebug() << stmt;
this->dbError = qry.lastError().text();
return false;
} else
return true;
}


Both instances at this point are executing an INSERT-Stmt but one of them gets an error

database is locked Unable to fetch row

I don't understand why? Shouldn't sqlite handle different accesses to the DB?

[anda_skoa]

The instance that writes first will likely employ some file locking to ensure data integrity.
If the second instance's write happens at the same time, it will of course fail and you'll have to retry later.

On Windows that will of course always fail, since it has single-process exlusive file locking by default.

Cheers,
_

[Cerberus]

Ok what would be the right approach to solve this problem?
Seperate .db file? But then how to name them since it is the same application running on the same machine and when starting the next time the application should access the right file
Or is there an option to wait till the first process finished its access and then execute the second INSERT-Stmt?

[anda_skoa]

There are a couple of options:

1) Close the database connection when you are done with a query. Will still require some retry loop
2) Communicate between the instances so each knows when it can access the database
3) Have one process for accessing the database, at least for writing, which the application instances communicate with
4) Use a database engine that implements (3)

Cheers,
_

[ChrisW67]

Sqlite will lock the file during write transactions and free it when the changes are committed. Assuming the insert is a whole transaction, try calling transaction() on the database before executing the insert and commit() (or rollback()) afterward.

[Cerberus]

Using transactions worked, thx for the help