Click here to Skip to main content
15,439,117 members
Please Sign up or sign in to vote.
4.67/5 (2 votes)
See more:
i am developing an app with sqlite. i keep getting database is lock when i try to add data to the database sometimes. here is the pseudo cod am using


        Try
Dim conn As SQLiteConnection = New SQLiteConnection("Data Source=" & dbPath)
            conn.SetPassword(dbPassword)
            conn.Open()
            Dim cmd As SQLiteCommand = New SQLiteCommand(conn)
            cmd.CommandText = "Select * From directory WHERE url = '" & sUrl & "'"
            Dim reader As SQLiteDataReader = cmd.ExecuteReader()
            Return reader.HasRows 
 reader.Close()
        Catch ex As Exception
            strLastError = ex.Message
            Debug.Print(strLastError)
            Return False
        Finally
           
            conn.Close()
        End Try


the suggestion i have got so far is to make sure i dispose the command object 'cmd', connection 'con' and reader properly before exiting the function

how do i do this in this case, as this current code which i tried to modify gives error.
Posted
Comments
Orcun Iyigun 1-Jun-11 17:11pm    
can you able to read but cant write?

1 solution

So you wrote that sometimes you get this database error. The reasons for getting the database lock error are;
SQLITE_LOCKED error is distinct from SQLITE_BUSY . SQLITE_BUSY means that another database connection (probably in another process) is using the database in a way that prevents you from using it. SQLITE_LOCKED means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error.

Sother reasons are for getting an SQLITE_LOCKED error:

*Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
Sometimes people think they have finished with a SELECT statement because sqlite3_step() has returned SQLITE_DONE. But the SELECT is not really complete until sqlite3_reset() or sqlite3_finalize() have been called.
*Trying to write to a table while a SELECT is active on that same table.
*Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
*fcntl(3, F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to move the database away, and copy it back so that it has a new Inode value.

Here[^] is a documentation of the possible states of locking in SQLite that you can gather info about it.
 
Share this answer
 
Comments
Cool Smith 2-Jun-11 12:58pm    
so how do i resolve this error?
Orcun Iyigun 3-Jun-11 13:06pm     CRLF
1) Provide to export your tables (You can use "sqlite manager" on Firefox) 2) If the migration alter your database scheme delete the last failed migration 3) Rename your "database.sqlite" file 4) Execute "rake db:migrate" to make a new working database 5) Provide to give the right permissions to database for table's importing 6) Import your backed up tables 7) Write the new migration 8) Execute it with "rake db:migrate"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900