qsqlquery database locked with SQLITE
I'm using Qt5 with an QSqlDatabase with QSQLITE. I initialize the Database with
Code:
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
Code:
bool DataStorageHandler
::executeStmt(QString stmt
) { 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?
Re: qsqlquery database locked with SQLITE
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,
_
Re: qsqlquery database locked with SQLITE
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?
Re: qsqlquery database locked with SQLITE
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,
_
Re: qsqlquery database locked with SQLITE
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.
Re: qsqlquery database locked with SQLITE
Using transactions worked, thx for the help