Re: [sqlite] Windows 8: debugging "table is locked" errors

[Simon Slavin]

On 13 Sep 2012, at 8:54pm, Marcus Ilgner  wrote:

> Ok, I was under the impression that sqlite3_step would always return
> the same error code as in the db.

There's no good way to guess what sqlite3_step() will return, especially if you 
called it after ignoring another error result code from another API call.  It's 
normal for a program to deal with the results of sqlite3_step() of one of

SQLITE_ROW
SQLITE_DONE

accordingly, then treat any other result code as an error that must be 
reported/logged without worrying about exactly which result code is being 
returned.

>  If I understand you correctly, it
> could be expected behaviour that a locked table returns SQLITE_ERROR
> instead of SQLITE_LOCKED?


If you're finding a lot of results of SQLITE_LOCKED or SQLITE_BUSY then you 
probably forgot to set a timeout value, or have a network or hardware problem.  
That sort of thing happens all the time.  In contrast SQLITE_ERROR indicates 
something completely different, more like bad memory management in C, or having 
ignored one error result you continued to call other API functions as if 
everything was working fine it should never happen if your code is well written.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows 8: debugging "table is locked" errors

[Marcus Ilgner]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13.09.2012 17:50, Joe Mistachkin wrote:
> Marcus Ilgner wrote:
>> 
>> I'm one of the authors of the SQLite-WinRT component for Windows
>> 8.
> 
> Cool, the one on CodePlex?
> 
Oh, sorry, I forgot to include the link [1]. The one at Codeplex is
more targeted towards C# development while we developed our component
to use it from the new JavaScript/HTML-based framework in Windows 8.

>> Regarding this release, can you tell me what configuration was
>> used to compile it?
> 
> The default compilation options from the MSVC makefile were used,
> along with the extra options necessary for WinRT (e.g.
> SQLITE_OS_WINRT, WINAPI_FAMILY, etc).
> 
Ok, great to know, I'll have a look at the Makefile then.

>> I'm asking because we wondered why the binary is about double the
>> size of the one we had previously.
> 
> From where?  With what compilation options?
> 
We took the official release and included it in the component. The
precompiler defines were
SQLITE_OS_WINRT;SQLITE_ENABLE_UNLOCK_NOTIFY;SQLITE_TEMP_STORE=2

>> Also, there's a strange phenomenon occurring which is probably
>> some kind of race condition in the way that multi-threading is
>> done: sqlite3_step() returns a generic error code 1 (for a valid
>> statement) but when I proceed to call sqlite3_errmsg16(), the
>> error message for error 6 (table locked) is returned.
> 
> That is not necessarily a race condition.  It could be the
> sqlite3_step is returning SQLITE_ERROR and the last error code
> stored for the database could be something else, like
> SQLITE_LOCKED.  Also, the SQLite DLLs for WinRT are compiled with
> SQLITE_THREADSAFE=1.
> 

Ok, I was under the impression that sqlite3_step would always return
the same error code as in the db. If I understand you correctly, it
could be expected behaviour that a locked table returns SQLITE_ERROR
instead of SQLITE_LOCKED?

>> Generally speaking, is a locked table something that should be
>> handled or do you think there may be another problem at play
>> here? I see that SQLite does a lot of work to support seamless
>> multi-threading and since the one application is the only process
>> working on the database, I suspect that the problem may lie
>> elsewhere.
> 
> How many processes and/or threads are attempting to access the
> database simultaneously?  There can be only one writer at a time.
> Also, unless WAL is used, a writer may block a reader.
> 

I had activated WAL in this scenario. There should have only been one
writer at that point, but I just had an idea how to debug this further.

> -- Joe Mistachkin
> 

Thank you for the quick reply, it has already been very helpful!

All the best
Marcus Ilgner

[1] https://github.com/doo/SQLite3-WinRT
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Mozilla - http://www.enigmail.net/

iQEcBAEBAgAGBQJQUjoHAAoJELuOTdgGQnyZVhQH/RzFd+FmDrIdk5zCtOHOHEy8
NANmh4Uf4RqfpS2GZlCI0PI7Hyn4pczaPGBGKO1o8z0GJ0PjmqXcJKuKgxzOitaN
KBF+Ig8MkeecuwjIuxDLPYd6h7Urm8Srx8VzNhMPCj1e24uGOItkXQDe5MCgWUYY
S3kH1zyWYNfPstHsO2qims4YCnczHs49Eq0S2aEKWwLeSS8aqnZ/eY1+s4Iem1Qn
vvJT1o/rjzaIobVP1R022ChGMnLYr8rEvMb+1VKcBM/OQMo+6m+fD+DHQzSieB7w
V3dUkx8hx4HJbXh3d59qdAE5NLLQLJN789q+eY33eUzWB0cRV+slLl77TK+6EqQ=
=1GAj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows 8: debugging "table is locked" errors

[Joe Mistachkin]

Marcus Ilgner wrote:
>
> I'm one of the authors of the SQLite-WinRT component for Windows 8.
>

Cool, the one on CodePlex?

> 
> Regarding this release, can you tell me what configuration was used to
> compile it? 
> 

The default compilation options from the MSVC makefile were used, along
with the extra options necessary for WinRT (e.g. SQLITE_OS_WINRT,
WINAPI_FAMILY, etc).

>
> I'm asking because we wondered why the binary is about double the size
> of the one we had previously.
>

>From where?  With what compilation options?

> 
> Also, there's a strange phenomenon occurring which is probably some
> kind of race condition in the way that multi-threading is done:
> sqlite3_step() returns a generic error code 1 (for a valid statement)
> but when I proceed to call sqlite3_errmsg16(), the error message for
> error 6 (table locked) is returned.
> 

That is not necessarily a race condition.  It could be the sqlite3_step
is returning SQLITE_ERROR and the last error code stored for the database
could be something else, like SQLITE_LOCKED.  Also, the SQLite DLLs for
WinRT are compiled with SQLITE_THREADSAFE=1.

> 
> Generally speaking, is a locked table something that should be handled
> or do you think there may be another problem at play here? I see that
> SQLite does a lot of work to support seamless multi-threading and since
> the one application is the only process working on the database, I
> suspect that the problem may lie elsewhere.
> 

How many processes and/or threads are attempting to access the database
simultaneously?  There can be only one writer at a time.  Also, unless
WAL is used, a writer may block a reader.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows 8: debugging "table is locked" errors

[Marcus Ilgner]

Hello list,

I'm one of the authors of the SQLite-WinRT component for Windows 8. We
recently adapted it to use the official Windows 8 compatible release
through NuGet. Thank you for providing a current version of SQLite through
this channel!
Regarding this release, can you tell me what configuration was used to
compile it? I'm asking because we wondered why the binary is about double
the size of the one we had previously.
Also, there's a strange phenomenon occurring which is probably some kind of
race condition in the way that multi-threading is done:
sqlite3_step() returns a generic error code 1 (for a valid statement) but
when I proceed to call sqlite3_errmsg16(), the error message for error 6
(table locked) is returned.
Generally speaking, is a locked table something that should be handled or
do you think there may be another problem at play here? I see that SQLite
does a lot of work to support seamless multi-threading and since the one
application is the only process working on the database, I suspect that the
problem may lie elsewhere.

All the best
Marcus Ilgner

-- 
doo Windows Team
doo GmbH | Argelander Strasse 1 | D-53115 Bonn
https://doo.net | @ma_il  |
@doo|
mar...@doo.net
AG Bonn, HRB 18719 | GF Frank Thelen, Marc Sieberger, Alex Koch
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users