can't drop a temp table because database table is locked

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

can't drop a temp table because database table is locked

Nir
Hi,

I tried to change a couple of my select queries to precompiled, meaning I
prepare them once and bind an int/string each time for performance gain. I
only reset and clear bindings before binding and executing my query and I
only finalize the statement when my app is closed.

I have a temp table that is created using the same table I query above but
only part of it, this table doesn't use precompiled statements but prepares
them each time. When I try to drop the temp table if exists my app locks and
I see that my sqlite log has the next error:
statement aborts at 12: [drop table if exists TEMP_TABLE_NAME;] database
table is locked6

is this a known issue? is there a workaround for this? i'm using sqlite
version 3.17.0.

Thanks,
Nir




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: can't drop a temp table because database table is locked

Dan Kennedy-4
On 03/07/2018 07:44 PM, Nir wrote:

> Hi,
>
> I tried to change a couple of my select queries to precompiled, meaning I
> prepare them once and bind an int/string each time for performance gain. I
> only reset and clear bindings before binding and executing my query and I
> only finalize the statement when my app is closed.
>
> I have a temp table that is created using the same table I query above but
> only part of it, this table doesn't use precompiled statements but prepares
> them each time. When I try to drop the temp table if exists my app locks and
> I see that my sqlite log has the next error:
> statement aborts at 12: [drop table if exists TEMP_TABLE_NAME;] database
> table is locked6
>
> is this a known issue? is there a workaround for this? i'm using sqlite
> version 3.17.0.

You can't drop a table if there are any active readers - statement
handles on which sqlite3_step() has been called more recently than
sqlite3_reset(). Even if the active readers never touch the temp table.

Dan.


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

Re: can't drop a temp table because database table is locked

Nir
If I understand you correctly then all I need to do in order for the database
not to be locked would be to reset a prepared query right after I retrieve
the needed data. after reseting I can either drop a temp table or bind
another value to my prepared statement and rerun a query. right?

Is there any reason why we shouldn't reset and clear bindings right after we
are done with a query?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: can't drop a temp table because database table is locked

Dan Kennedy-4
On 03/07/2018 11:40 PM, Nir wrote:
> If I understand you correctly then all I need to do in order for the database
> not to be locked would be to reset a prepared query right after I retrieve
> the needed data. after reseting I can either drop a temp table or bind
> another value to my prepared statement and rerun a query. right?

Right.
> Is there any reason why we shouldn't reset and clear bindings right after we
> are done with a query?
No reason at all. You should call sqlite3_reset() when you are done with
a query - in some cases this is required to release database locks or to
close an open read-transaction. I don't think there is any real reason
to clear bound values though. Perhaps if they are very large and you
want to free up memory sooner rather than later. In any case, no harm in
doing so.

Dan.


>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> 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