"Database is locked" in diagnostic tools C#

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

"Database is locked" in diagnostic tools C#

Wojciech Slabik (Voitec)
Hi everyone,

I have managed  to successfully replace SQLCE with System.Data.SQLite in a mid-size application. Everything works fine but now that I started optimizing the old code and replaced own connection pooling with SQLite connection pooling I am getting an occasional Program Output "Database is locked" from the C++ dll being displayed in the VS Diagnostic Tools. This is not throwing an exception in C# though, so I am wondering if this just means that the transaction has slept and was re-executed successfully? I tried to replicate this issue in a small app but transaction just waits 30 seconds (default busy timeout?) and throws an exception, there is nothing showing up in the Diagnostic Tools.

Thanks in advance,
Wojciech

[Micromine PL]<http://www.micromine.com>

Micromine PL
_______________________________________________
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: [EXTERNAL] "Database is locked" in diagnostic tools C#

Hick Gunter
The "database is locked" message means that the transaction was unable to complete within the designated busy timeout and was therefore rolled back. Getting this message implies that you are running multiple processes. Depending on the journal mode, a writer process may block both readers and writers and vice versa (readers never block readers); or for WAL mode, a writer process only blocks other writers.

In any case, you need to set a sufficiently large timeout value OR establish a busy handler to deal with the situation. Try to keep transactions small; maybe you are beginning a transaction and never ending (commit/rollback) it?

What do you mean by "connection pooling"? Are you using the same connection from multiple threads?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Wojciech Slabik (Voitec)
Gesendet: Freitag, 16. Februar 2018 10:17
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] "Database is locked" in diagnostic tools C#

Hi everyone,

I have managed  to successfully replace SQLCE with System.Data.SQLite in a mid-size application. Everything works fine but now that I started optimizing the old code and replaced own connection pooling with SQLite connection pooling I am getting an occasional Program Output "Database is locked" from the C++ dll being displayed in the VS Diagnostic Tools. This is not throwing an exception in C# though, so I am wondering if this just means that the transaction has slept and was re-executed successfully? I tried to replicate this issue in a small app but transaction just waits 30 seconds (default busy timeout?) and throws an exception, there is nothing showing up in the Diagnostic Tools.

Thanks in advance,
Wojciech

[Micromine PL]<http://www.micromine.com>

Micromine PL
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] "Database is locked" in diagnostic tools C#

Wojciech Slabik (Voitec)
Thank you for your answer.

I am currently using connection time pooling using " Pooling=True; Min Pool Size=1; Max Pool Size=100;". The timeout is set to 30 seconds and none of the operations should take more than a second..
It is confusing as in a demo project I do get an exception showing after 30 seconds (start a transaction in one thread, then sleep for >30 seconds while transaction in other thread is waiting and throwing exception after 30 seconds). In this case I can only see Program Output in Diagnostic Tools.

Regards,
Wojciech

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: Friday, 16 February 2018 5:36 PM
To: 'SQLite mailing list' <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

The "database is locked" message means that the transaction was unable to complete within the designated busy timeout and was therefore rolled back. Getting this message implies that you are running multiple processes. Depending on the journal mode, a writer process may block both readers and writers and vice versa (readers never block readers); or for WAL mode, a writer process only blocks other writers.

In any case, you need to set a sufficiently large timeout value OR establish a busy handler to deal with the situation. Try to keep transactions small; maybe you are beginning a transaction and never ending (commit/rollback) it?

What do you mean by "connection pooling"? Are you using the same connection from multiple threads?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Wojciech Slabik (Voitec)
Gesendet: Freitag, 16. Februar 2018 10:17
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] "Database is locked" in diagnostic tools C#

Hi everyone,

I have managed  to successfully replace SQLCE with System.Data.SQLite in a mid-size application. Everything works fine but now that I started optimizing the old code and replaced own connection pooling with SQLite connection pooling I am getting an occasional Program Output "Database is locked" from the C++ dll being displayed in the VS Diagnostic Tools. This is not throwing an exception in C# though, so I am wondering if this just means that the transaction has slept and was re-executed successfully? I tried to replicate this issue in a small app but transaction just waits 30 seconds (default busy timeout?) and throws an exception, there is nothing showing up in the Diagnostic Tools.

Thanks in advance,
Wojciech

[Micromine PL]<http://www.micromine.com>

Micromine PL
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] "Database is locked" in diagnostic tools C#

Hick Gunter
I am not familiar with System.Data.SQLite or what the pooling parameters make it do. Starting a transaction and then going away for a cup of coffee will definitely make any busy timeout expire. This should be avoided.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Wojciech Slabik (Voitec)
Gesendet: Freitag, 16. Februar 2018 10:43
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

Thank you for your answer.

I am currently using connection time pooling using " Pooling=True; Min Pool Size=1; Max Pool Size=100;". The timeout is set to 30 seconds and none of the operations should take more than a second..
It is confusing as in a demo project I do get an exception showing after 30 seconds (start a transaction in one thread, then sleep for >30 seconds while transaction in other thread is waiting and throwing exception after 30 seconds). In this case I can only see Program Output in Diagnostic Tools.

Regards,
Wojciech

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: Friday, 16 February 2018 5:36 PM
To: 'SQLite mailing list' <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

The "database is locked" message means that the transaction was unable to complete within the designated busy timeout and was therefore rolled back. Getting this message implies that you are running multiple processes. Depending on the journal mode, a writer process may block both readers and writers and vice versa (readers never block readers); or for WAL mode, a writer process only blocks other writers.

In any case, you need to set a sufficiently large timeout value OR establish a busy handler to deal with the situation. Try to keep transactions small; maybe you are beginning a transaction and never ending (commit/rollback) it?

What do you mean by "connection pooling"? Are you using the same connection from multiple threads?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Wojciech Slabik (Voitec)
Gesendet: Freitag, 16. Februar 2018 10:17
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] "Database is locked" in diagnostic tools C#

Hi everyone,

I have managed  to successfully replace SQLCE with System.Data.SQLite in a mid-size application. Everything works fine but now that I started optimizing the old code and replaced own connection pooling with SQLite connection pooling I am getting an occasional Program Output "Database is locked" from the C++ dll being displayed in the VS Diagnostic Tools. This is not throwing an exception in C# though, so I am wondering if this just means that the transaction has slept and was re-executed successfully? I tried to replicate this issue in a small app but transaction just waits 30 seconds (default busy timeout?) and throws an exception, there is nothing showing up in the Diagnostic Tools.

Thanks in advance,
Wojciech

[Micromine PL]<http://www.micromine.com>

Micromine PL
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users