SQLITE_LOCKED and SQLITE_BUSY

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

SQLITE_LOCKED and SQLITE_BUSY

Prajeesh Prakash
Hi members,

The SQLITE_LOCKED error will happen on same database connection when two thread trying to do read/write operation at same time. SQLITE_BUSY will get when one thread on one connection is doing read/write operation and another thread on another connection trying to read/write the DB. Is it correct?


Thank you
_______________________________________________
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: SQLITE_LOCKED and SQLITE_BUSY

Keith Medcalf

No.  It is not correct.  Have you read the documentation?

https://sqlite.org/rescode.html#locked

Multiple threads cannot perform operations at the same time on the same connection.  This is verboten.  Forbidden.  Does not work.  Will cause explosions and death of children.  Do not do it.  Ever.  Period.  End of Line.

"The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED indicates a conflict on the same database connection (or on a connection with a shared cache) whereas SQLITE_BUSY indicates a conflict with a different database connection"

So if you do something like this:

open database using connection1
on connection1 SELECT * FROM DATA
while got some rows:
  on connection1 DROP TABLE DATA

the DROP TABLE DATA will get a SQLITE_LOCKED because it cannot delete the table DATA because it is being read ON THE SAME CONNECTION.

conversely if you do something like this:

open database using connection1
open database using connection2
on connection1 SELECT * FROM DATA
while got some rows:
  on connection2 DROP TABLE DATA

the DROP TABLE DATA will get a SQLITE_BUSY because it cannot delete the table DATA because it is being read ON A DIFFERENT CONNECTION.

"threads" have nothing to do with anything whatsoever except that if you make any sqlite3_x call simultaneously using THE SAME CONNECTION (or something derived from the same connection, like a cursor) then the universe will explode and children will die!  Do not do that.  Ever.

NB:  different "connections" to the same "shared cache" are really the "same connection" ... for the purposes of the SQLITE_LOCKED / SQLITE_BUSY and different connections for the purposes of the universe exploding and children dying.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Prajeesh Prakash
>Sent: Wednesday, 28 November, 2018 08:36
>To: SQLite mailing list
>Subject: [sqlite] SQLITE_LOCKED and SQLITE_BUSY
>
>Hi members,
>
>The SQLITE_LOCKED error will happen on same database connection when
>two thread trying to do read/write operation at same time.
>SQLITE_BUSY will get when one thread on one connection is doing
>read/write operation and another thread on another connection trying
>to read/write the DB. Is it correct?
>
>
>Thank you
>_______________________________________________
>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: SQLITE_LOCKED and SQLITE_BUSY

Prajeesh Prakash
Hi Team,

I am writing to a table 1 and reading from table 2 both operation are from different DB connection i am getting SQLITE_LOCKED

and when i try to read and write the same table from different connection i am getting same error. (I know read and write are the incompatible at same time). Why this happening bu default sqlite will act on FULLMUTEXT.

Thank You
_______________________________________________
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: SQLITE_LOCKED and SQLITE_BUSY

Simon Slavin-3
On 29 Nov 2018, at 11:35am, Prajeesh Prakash <[hidden email]> wrote:

> I am writing to a table 1 and reading from table 2 both operation are from different DB connection i am getting SQLITE_LOCKED

SQLite locks the entire database.  It does not lock each table independently.

> and when i try to read and write the same table from different connection i am getting same error. (I know read and write are the incompatible at same time). Why this happening bu default sqlite will act on FULLMUTEXT.

Ignore MUTEX when using two different connections.  You need to set a timeout of a minute or two for each connection:

<https://sqlite.org/pragma.html#pragma_busy_timeout>

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