[SQLite3] [Mono] [Linux] "db is locked"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[SQLite3] [Mono] [Linux] "db is locked"

Phani Rahul Sivalenka
Hello,

We have an ASP.NET Web application running with Mono on Linux. Uses
System.Data.SQLite library with an interop generated with a specific set
configuration to interact with the SQLite file.

When we do a read operation and write/update operation on the SQLite DB,
the write operation gets timed out saying the “db is locked”.

In addition to this, we have a few sync threads the try to update the DB at
regular intervals, this made it hard to use WAL journal mode, as the DB
does not have a window where it can sync the wal file with the DB.

The following are the main configurations that we applied on the DB:

*Via Connection string:*

   - Journal Mode : Memory
   - Busy Timeout : 30sec
   - Default Timeout : 30sec
   - Pooling : true
   - Synchronous : Off


*While generating the interop:*

   - Threading Mode : SQLITE_THREADSAFE: 1 (Serialized)


Please suggest what can be done to fix this issue.

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite3] [Mono] [Linux] "db is locked"

Simon Slavin-3
On 21 Jun 2018, at 10:01pm, Phani Rahul Sivalenka <[hidden email]> wrote:

> When we do a read operation and write/update operation on the SQLite DB,
> the write operation gets timed out saying the “db is locked”.

[...]

>   - Busy Timeout : 30sec
>   - Default Timeout : 30sec

I'm going to assume you're doing something like

SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();
        connBuilder.Version = 3;
        connBuilder.DataSource = filePath;
        connBuilder.BusyTimeout = 30;
        connBuilder.DefaultTimeout = 30;

If this isn't what you're doing please tell us.

SQLite itself uses busyTimeout.  The unit of busyTimeout is milliseconds.  Try setting the value to 30000 and see if this fixes your problem.  I don't know what defaultTimeout means to the library you're using but try doing the same thing with that one.

How are you disposing of your SQLiteDataReader object after the reading is finished ?  Once the object has locked the file, while it still exists it can maintain a lock on the file.  One way to make sure that the object has been disposed of is to use this structure:

using (SQLiteCommand cmd =
    new SQLiteCommand("SELECT * FROM sqlite_master;", cnn)) {
        using (SQLiteDataReader reader = cmd.ExecuteReader()) {
            // extract data from reader so you don't need it any more
        }
    }

You will need to dispose of your writing object too.  Possibly in a similar way.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite3] [Mono] [Linux] "db is locked"

Phani Rahul Sivalenka
In reply to this post by Phani Rahul Sivalenka
> SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();

We are setting these configs via the connection string in xml. This is same as the above. The following is the connection string that we are using:

“data source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal mode=Memory;Busy Timeout=30000;Default Timeout=30”


>   Try setting the value to 30000 and see if this fixes your problem.

“Busy Timeout” is set to 30000 as seen in the connection string.
“Default Timeout” has a units of seconds as per the library and is the timeout used for each command. This value is set to 30 sec.


> How are you disposing of your SQLiteDataReader object after the reading is finished ?

This is the format that we are following while using the reader or using ExecuteNonQuery.

using (SQLiteConnection connection = GetNewSQLiteConnection())
{
        try
        {
                connection.Open();
               
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                        // For Read Op ---------
                        using (var reader = command.ExecuteReader())
                        {
                                // ….
                                reader.Close();
                        }
                        // ---------------------------

                        // For Write Op --------
                        result = command.ExecuteNonQuery();
                        // --------------------------

                        command.Reset();
                }
        }
        catch(Exception ex)
        {
                // ….
        }
        finally
        {
                if (connection.State != ConnectionState.Closed)
                {
                        connection.Close();
                }
        }
}

Rahul.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite3] [Mono] [Linux] "db is locked"

Phani Rahul Sivalenka
As per our observation, the initial write operations on the sqlite db file
throw "db is locked" error. After a certain time (around an hour) write
operations start working and we are able to do all the operations as
required.

On Mon, Jun 25, 2018 at 7:37 PM Phani Rahul Sivalenka
<[hidden email]> wrote:

> > SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();
>
>
>
> We are setting these configs via the connection string in xml. This is
> same as the above. The following is the connection string that we are using:
>
>
>
> “data
> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
> mode=Memory;Busy Timeout=30000;Default Timeout=30”
>
>
>
>
>
> >   Try setting the value to 30000 and see if this fixes your problem.
>
>
>
> “Busy Timeout” is set to 30000 as seen in the connection string.
>
> “Default Timeout” has a units of seconds as per the library and is the timeout used for each command. This value is set to 30 sec.
>
>
>
>
>
> > How are you disposing of your SQLiteDataReader object after the reading is finished ?
>
>
>
> This is the format that we are following while using the reader or using ExecuteNonQuery.
>
>
>
> using (SQLiteConnection connection = GetNewSQLiteConnection())
>
> {
>
>                 try
>
>                 {
>
>                                 connection.Open();
>
>
>
>                                 using (SQLiteCommand command = new
> SQLiteCommand(query, connection))
>
>                                 {
>
>                                                 // For Read Op ---------
>
>                                                 using (var reader =
> command.ExecuteReader())
>
>                                                 {
>
>                                                                 // ….
>
>
> reader.Close();
>
>                                                 }
>
>                                                 //
> ---------------------------
>
>
>
>                                                 // For Write Op --------
>
>                                                 result =
> command.ExecuteNonQuery();
>
>                                                 //
> --------------------------
>
>
>
>                                                 command.Reset();
>
>                                 }
>
>                 }
>
>                 catch(Exception ex)
>
>                 {
>
>                                 // ….
>
>                 }
>
>                 finally
>
>                 {
>
>                                 if (connection.State !=
> ConnectionState.Closed)
>
>                                 {
>
>                                                 connection.Close();
>
>                                 }
>
>                 }
>
> }
>
>
>
> Rahul.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite3] [Mono] [Linux] "db is locked"

Simon Slavin-3
On 3 Jul 2018, at 8:08pm, Phani Rahul Sivalenka <[hidden email]> wrote:

> As per our observation, the initial write operations on the sqlite db file
> throw "db is locked" error. After a certain time (around an hour) write
> operations start working and we are able to do all the operations as
> required.  [...]

> “data
> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
> mode=Memory;Busy Timeout=30000;Default Timeout=30”

Test each of your timeouts by removing one, then the other, then both.  See if the removal of one of them changes the described behaviour.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite3] [Mono] [Linux] "db is locked"

Barry Smith
Some ideas:

Sqlite may return that the database is locked immediately if it detects a deadlock situation. Something like: a different connection holds a reserved lock (waiting for read connections to close so it can promote to exclusive), and the current connection tries to promote from a read lock to a reserved or exclusive lock. Busy timeout will never resolve this situation so the connection attempting to promote just returns that the database is locked.

You can also get guaranteed timeouts if you use multiple connections on the same thread, or if your threading logic causes a deadlock. An example might be:

//...
DataReader dataReader = outerCmd.ExecuteReader()
foreach (var row in dataReader)
{
    using (SQLiteConnection innerCon = GetNewConnection())
        //attempt to write with inner conn
}

The above might happen in a called function to obscure what is happening. Such things might be valid on a different dbms that does table or row level locking, but can't be used with SQLite's Db level locking (I got bitten by this when I thought 'oh yeah, change from sql server to sqlite? I'll just update the SQL and change the providers. The logic will translate fine.')

Finally, I have seen inexplicable timeouts if I mix SQLite and TransactionScope. In my case the sqlite connections didn't need to participate in the transaction so I fixed it by setting enlist=false on the connection string and not investigating further.

> On 4 Jul 2018, at 5:14 am, Simon Slavin <[hidden email]> wrote:
>
>> On 3 Jul 2018, at 8:08pm, Phani Rahul Sivalenka <[hidden email]> wrote:
>>
>> As per our observation, the initial write operations on the sqlite db file
>> throw "db is locked" error. After a certain time (around an hour) write
>> operations start working and we are able to do all the operations as
>> required.  [...]
>
>> “data
>> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
>> mode=Memory;Busy Timeout=30000;Default Timeout=30”
>
> Test each of your timeouts by removing one, then the other, then both.  See if the removal of one of them changes the described behaviour.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users