QSqlite "database is locked"

[flob]

Hi All,

I have a database class that accesses a local file using Qt's Sqlite3 drivers. The database class is implemented as a Singleton model and is accessible in that manner by the rest of the application, and thus guaranteeing that only one process is accessing the database at any given time.

My problem is that when I use a QSqlQueryModel and call its setQuery() method, I get a QSqlError of "database is locked." This error is thrown on my FIRST attempt to query the database, which happens to be a SELECT statement.

One more caveat is that this code works perfectly well under Linux (Ubuntu) and Mac OS X, but fails with the above error on an embedded ARM920t platform.

On the same target hardware, I have been able to successfully run a few simple "Hello World" style applications, so I know that at least the basic cross-compiled libraries are correct.

Cross compiled under Ubuntu, using the Qt 4.5.3 embedded source.

Thanks in advance.

[Tanuki-no Torigava]

Hi. I've got the same issue a couple of months ago. Let me check in code how I solved that. I'll promise to post the workaround right after I remember that one. Also the description of this project is on SQLite site. So you can check that there too.

[flob]

If you could, that would be GREATLY appreciated! Thanks!

[NoRulez]

@Tanuki-no Torigava: Thank you very much. Did you mean this site?

Best Regards
NoRulez

[Lykurg]

Do you use threads which access the database? If not, no singleton pattern is really needed and I'd skip it, since the global, static QSqlDatabase::database() is good enough.

And can you show us some code of your singleton database class and how do you access the database form outside that class. So we could eventually see an error. (Even if it is strange that it works on Linux and Mac OSX...)

[Tanuki-no Torigava]

Ok. Here it is the details. I'm using that class from the threads. So, what I actually did was:
- Use pragmas to change the default SQLite behaviour. See details here;

- Use transactions (See QSqlDatabase::transaction () and QSqlDatabase::commit()).That let me don't really lock the write to the database for a long time;

- Play with QSQLITE_BUSY_TIMEOUT.

My code sample

const static char* SESSION_NAME = "MySession";
 
static const char* s_db_pragma[] = {
    "synchronous=OFF",
    "count_changes=OFF",
    "temp_store=MEMORY",
    NULL
};
 
// Check if SQLite driver is available
if (!QSqlDatabase::isDriverAvailable("QSQLITE"))
{
   qDebug <<  "SQLite 3.x driver unavailabe. Check Qt build!";
   return;
}
 
   // Note, db_path is a path to your SQLite database
   QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE", SESSION_NAME);
    m_db.setDatabaseName(db_path);
    if (!m_db.isValid() || !m_db.open())
    {
       qDebug << "Unable to open SQLite 3.x database from " << db_path;
        return;
    }
 
    // Enable SQLite pragmas
    int i = 0;
    do {
        QString sql = QString("PRAGMA %1;").arg(s_db_pragma[i]);
        QSqlQuery query(m_db);
        query.prepare(sql);
        query.exec();
 
        if (query.lastError().isValid())
        {
           qDebug << "Query: \"" << query.lastQuery()
                     << "\"; Error: \"" << query.lastError().text() << "\"."
           return;
        }
    } while (s_db_pragma[++i]);
 
    // Continue from here with your queries

const static char* SESSION_NAME = "MySession";

static const char* s_db_pragma[] = {
    "synchronous=OFF",
    "count_changes=OFF",
    "temp_store=MEMORY",
    NULL
};

// Check if SQLite driver is available
if (!QSqlDatabase::isDriverAvailable("QSQLITE"))
{
   qDebug <<  "SQLite 3.x driver unavailabe. Check Qt build!";
   return;
}
 
   // Note, db_path is a path to your SQLite database
   QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE", SESSION_NAME);
    m_db.setDatabaseName(db_path);
    if (!m_db.isValid() || !m_db.open())
    {
       qDebug << "Unable to open SQLite 3.x database from " << db_path;
        return;
    }

    // Enable SQLite pragmas
    int i = 0;
    do {
        QString sql = QString("PRAGMA %1;").arg(s_db_pragma[i]);
        QSqlQuery query(m_db);
        query.prepare(sql);
        query.exec();

        if (query.lastError().isValid())
        {
           qDebug << "Query: \"" << query.lastQuery()
                     << "\"; Error: \"" << query.lastError().text() << "\"."
           return;
        }
    } while (s_db_pragma[++i]);

    // Continue from here with your queries

To copy to clipboard, switch view to plain text mode 

Best regards,

-- Tanuki

P.S. It's quite easy to use transactions. Place QSqlDatabase::transaction () before query block and use QSqlDatabase::commit() after it. Also you can use QSqlDatabase::rollback(). Since SQLite 3.x this approach is very usefull and works good.

[flob]

Hi All!

Thank you all very much for your responses!

I believe I tracked down the issue:

The resolution lay in an incorrect configuration within the file system of my target machine. Apparently, I had to select the configuration that allowed for "standard file locking behavior." Once I selected that option and recompiled, and placed the new image onto the ARM board, I got past my database calls.

I am now seeing a segmentation fault after the main GUI is initialized, on the QApplication.exec call from main. The QWindow gets past the show() call, but then seg faults before anything appears on the display. Oi. Guess it's time to post another thread...

Thanks again!