Getting SQLITE_LOCKED

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

Getting SQLITE_LOCKED

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: Getting SQLITE_LOCKED

Prajeesh Prakash
Sorry i was a wrong attempt.

>
>     On November 29, 2018 at 5:07 PM Prajeesh Prakash <[hidden email]> wrote:
>
>     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
>
_______________________________________________
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] Getting SQLITE_LOCKED

Hick Gunter
In reply to this post by Prajeesh Prakash
Locking (errors SQLITE_LOCKED and SQLITE_BUSY) are about **WHAT** you are doing to the DB. There can be at most 1 thread (same process or different process, it does not matter) with a write transaction on any DB file at any time. Typically, a transaction involves multiple calls to sqlite functions (sqlite3_prepare() to compile the statement, the first call to sqlite3_step() starts the transaction and calling sqlite3_reset() or sqlite3_finalize() ends the transaction).

FULLMUTEX has nothing to do with transactions. Using threads is about **HOW** you are doing things to the DB. FULLMUTEX is about making sure that your threads take turns when sharing a single connection (with a single transaction).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Prajeesh Prakash
Gesendet: Donnerstag, 29. November 2018 12:37
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Getting SQLITE_LOCKED

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


___________________________________________
 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] Getting SQLITE_LOCKED

Prajeesh Prakash
Hi Team,

Actually this is the case. From my main application i created two thread (One thread is for read from table 1 on the DB and another thread is for write into a separate table (Say table 2) on the DB). Similary i created two separate connection to DB (For these two thread to operate)using sqlite3 * and i used  sqlite3_open() to open the DB connection (That is two sqlite3_open() for two connection). Once i started the operation (writing and reading using sqlite3_exec()) the thread which trying to read getting SQLITE_BUSY (error code 5 and error message database is locked) and result writing to the DB was successfully done (Thread 1 operation).


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: Getting SQLITE_LOCKED

Keith Medcalf
In reply to this post by Prajeesh Prakash

Ya must be using shared cache as well?


---
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: Thursday, 29 November, 2018 04:37
>To: [hidden email]
>Subject: [sqlite] Getting SQLITE_LOCKED
>
>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



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

sqlite3_exec()

Prajeesh Prakash
Hi Team,
Is this sqlite3_exec() function is a blocking call in the case of writing.I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point of few there is no multiple thread every thing is serialized). Reader thread starts first and continuously reading 500 records from a table (I kept the sqlite3_exec() function call inside a loop for continues reading ie, on one iteration it will print 500 records and it is printing successfully using callback function). After 1sec of delay writer thread starts its execution and tries to write 500 records into the same table,Because of the small loop iteration gap in the reader thread the writer will get a chance to update the table hence my reader thread is blocked may be the mutex on the sqlite3* is acquired by the writer thread. After around 45 sec reader start its operation but one thing i observed is that the newly added entry is not printing
  instead of that old data is printing so i increased the reader thread looping iteration. After few iteration is over the newly added records also printing along with the old data. Why this delay is happening? (even though the writer thread is not coming out from the sqlite3_exec which i used to write the data but after a few sec its came out at that time onward i am getting the updated data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION and COMMIT.
Please give a suggestion for this.

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: sqlite3_exec()

Simon Slavin-3
On 1 Dec 2018, at 1:50pm, Prajeesh Prakash <[hidden email]> wrote:

> I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection

One connection can only execute one operation at one time.  If you want simultaneous operations, use two connections.

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: [EXTERNAL] sqlite3_exec()

Hick Gunter
In reply to this post by Prajeesh Prakash
An SQLite connection is not a unix standard file handle. By using only one connection for two concurrent tasks, you are getting interference from operations which would usually be isolated from each other. Sharing a connection between threads is there because SQlite also runs on embedded systems that may have low limits on the number of file handles a process may open simultaneously.

Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Prajeesh Prakash
Gesendet: Samstag, 01. Dezember 2018 14:51
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] sqlite3_exec()

Hi Team,
Is this sqlite3_exec() function is a blocking call in the case of writing.I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point of few there is no multiple thread every thing is serialized). Reader thread starts first and continuously reading 500 records from a table (I kept the sqlite3_exec() function call inside a loop for continues reading ie, on one iteration it will print 500 records and it is printing successfully using callback function). After 1sec of delay writer thread starts its execution and tries to write 500 records into the same table,Because of the small loop iteration gap in the reader thread the writer will get a chance to update the table hence my reader thread is blocked may be the mutex on the sqlite3* is acquired by the writer thread. After around 45 sec reader start its operation but one thing i observed is that the newly added entry is not printing
  instead of that old data is printing so i increased the reader thread looping iteration. After few iteration is over the newly added records also printing along with the old data. Why this delay is happening? (even though the writer thread is not coming out from the sqlite3_exec which i used to write the data but after a few sec its came out at that time onward i am getting the updated data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION and COMMIT.
Please give a suggestion for this.

Thank you
_______________________________________________
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] sqlite3_exec()

Prajeesh Prakash
Thank you for the response.

Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.

If this is the case the reader thread on connection 1 will get a SQLITE_BUSY if the writer thread is writing on DB using connection 2. Because when i tested the scenario (its Doesn't matter which table i am writing/reading) like writing and reading using 2 different connection i  am getting BUSY error status (Writing and reading same table, Writing and reading two different table) .

>
>     On December 3, 2018 at 12:56 PM Hick Gunter <[hidden email]> wrote:
>
>     An SQLite connection is not a unix standard file handle. By using only one connection for two concurrent tasks, you are getting interference from operations which would usually be isolated from each other. Sharing a connection between threads is there because SQlite also runs on embedded systems that may have low limits on the number of file handles a process may open simultaneously.
>
>     Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.
>
>     -----Ursprüngliche Nachricht-----
>     Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Prajeesh Prakash
>     Gesendet: Samstag, 01. Dezember 2018 14:51
>     An: SQLite mailing list <[hidden email]>
>     Betreff: [EXTERNAL] [sqlite] sqlite3_exec()
>
>     Hi Team,
>     Is this sqlite3_exec() function is a blocking call in the case of writing.I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point of few there is no multiple thread every thing is serialized). Reader thread starts first and continuously reading 500 records from a table (I kept the sqlite3_exec() function call inside a loop for continues reading ie, on one iteration it will print 500 records and it is printing successfully using callback function). After 1sec of delay writer thread starts its execution and tries to write 500 records into the same table,Because of the small loop iteration gap in the reader thread the writer will get a chance to update the table hence my reader thread is blocked may be the mutex on the sqlite3* is acquired by the writer thread. After around 45 sec reader start its operation but one thing i observed is that the newly added entry is not printing
>     instead of that old data is printing so i increased the reader thread looping iteration. After few iteration is over the newly added records also printing along with the old data. Why this delay is happening? (even though the writer thread is not coming out from the sqlite3_exec which i used to write the data but after a few sec its came out at that time onward i am getting the updated data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION and COMMIT.
>     Please give a suggestion for this.
>
>     Thank you
>
>     _______________________________________________
>     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] sqlite3_exec()

Hick Gunter
This is exactly the expected behaviour of „journal mode“ with an insufficient timeout value in the reader connection (or none set at all). This is technically not an error condition, just a notification that the requested operation cannot be done „just right now“ and needs to be retried „later“.

Set or increase the timeout value on both connections to resolve the issue.

Alternatively, switch into „WAL mode“. This will make the writer save it‘s changes in a Write Ahead Logfile without interfering with readers, who will still see the state of the DB at the time their transaction started.

Von: Prajeesh Prakash [mailto:[hidden email]]
Gesendet: Montag, 03. Dezember 2018 08:39
An: SQLite mailing list <[hidden email]>; Hick Gunter <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec()


Thank you for the response.

Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.

If this is the case the reader thread on connection 1 will get a SQLITE_BUSY if the writer thread is writing on DB using connection 2. Because when i tested the scenario (its Doesn't matter which table i am writing/reading) like writing and reading using 2 different connection i  am getting BUSY error status (Writing and reading same table, Writing and reading two different table) .

On December 3, 2018 at 12:56 PM Hick Gunter <[hidden email]<mailto:[hidden email]>> wrote:

An SQLite connection is not a unix standard file handle. By using only one connection for two concurrent tasks, you are getting interference from operations which would usually be isolated from each other. Sharing a connection between threads is there because SQlite also runs on embedded systems that may have low limits on the number of file handles a process may open simultaneously.

Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Prajeesh Prakash
Gesendet: Samstag, 01. Dezember 2018 14:51
An: SQLite mailing list <[hidden email]<mailto:[hidden email]>>
Betreff: [EXTERNAL] [sqlite] sqlite3_exec()

Hi Team,
Is this sqlite3_exec() function is a blocking call in the case of writing.I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point of few there is no multiple thread every thing is serialized). Reader thread starts first and continuously reading 500 records from a table (I kept the sqlite3_exec() function call inside a loop for continues reading ie, on one iteration it will print 500 records and it is printing successfully using callback function). After 1sec of delay writer thread starts its execution and tries to write 500 records into the same table,Because of the small loop iteration gap in the reader thread the writer will get a chance to update the table hence my reader thread is blocked may be the mutex on the sqlite3* is acquired by the writer thread. After around 45 sec reader start its operation but one thing i observed is that the newly added entry is not printing
instead of that old data is printing so i increased the reader thread looping iteration. After few iteration is over the newly added records also printing along with the old data. Why this delay is happening? (even though the writer thread is not coming out from the sqlite3_exec which i used to write the data but after a few sec its came out at that time onward i am getting the updated data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION and COMMIT.
Please give a suggestion for this.

Thank you

_______________________________________________
sqlite-users mailing list
[hidden email]<mailto:[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]<mailto:[hidden email]>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH<http://www.scigames.at> | 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] sqlite3_exec()

Prajeesh Prakash
Thank you,

How we can find the number of record that newly added to the table once after the new recorded added(Just for a verification). Does sqlite3 provide any library for that.

> On December 3, 2018 at 1:19 PM Hick Gunter <[hidden email]> wrote:
>
>
>      
>
>     This is exactly the expected behaviour of „journal mode“ with an insufficient timeout value in the reader connection (or none set at all). This is technically not an error condition, just a notification that the requested operation cannot be done „just right now“ and needs to be retried „later“.
>
>      
>
>     Set or increase the timeout value on both connections to resolve the issue.
>
>      
>
>     Alternatively, switch into „WAL mode“. This will make the writer save it‘s changes in a Write Ahead Logfile without interfering with readers, who will still see the state of the DB at the time their transaction started.
>
>      
>
>     Von: Prajeesh Prakash [mailto:[hidden email]]
>     Gesendet: Montag, 03. Dezember 2018 08:39
>     An: SQLite mailing list <[hidden email]>; Hick Gunter <[hidden email]>
>     Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec()
>
>      
>
>     Thank you for the response.
>
>     Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.
>
>     If this is the case the reader thread on connection 1 will get a SQLITE_BUSY if the writer thread is writing on DB using connection 2. Because when i tested the scenario (its Doesn't matter which table i am writing/reading) like writing and reading using 2 different connection i  am getting BUSY error status (Writing and reading same table, Writing and reading two different table) .
>
>         > >
> >         On December 3, 2018 at 12:56 PM Hick Gunter <[hidden email] mailto:[hidden email] > wrote:
> >
> >         An SQLite connection is not a unix standard file handle. By using only one connection for two concurrent tasks, you are getting interference from operations which would usually be isolated from each other. Sharing a connection between threads is there because SQlite also runs on embedded systems that may have low limits on the number of file handles a process may open simultaneously.
> >
> >         Using 1 connection per thread will allow the reader thread to read all of the "old" records (and none of the "new" records). Then, the writer can add the "new" records. A subsequent read will return both "old " and "new" records.
> >
> >         -----Ursprüngliche Nachricht-----
> >         Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Prajeesh Prakash
> >         Gesendet: Samstag, 01. Dezember 2018 14:51
> >         An: SQLite mailing list <[hidden email] mailto:[hidden email] >
> >         Betreff: [EXTERNAL] [sqlite] sqlite3_exec()
> >
> >         Hi Team,
> >         Is this sqlite3_exec() function is a blocking call in the case of writing.I have two thread one is for reading and other is for writing the DB. Both thread have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point of few there is no multiple thread every thing is serialized). Reader thread starts first and continuously reading 500 records from a table (I kept the sqlite3_exec() function call inside a loop for continues reading ie, on one iteration it will print 500 records and it is printing successfully using callback function). After 1sec of delay writer thread starts its execution and tries to write 500 records into the same table,Because of the small loop iteration gap in the reader thread the writer will get a chance to update the table hence my reader thread is blocked may be the mutex on the sqlite3* is acquired by the writer thread. After around 45 sec reader start its operation but one thing i observed is that the newly added entry is not printing
> >         instead of that old data is printing so i increased the reader thread looping iteration. After few iteration is over the newly added records also printing along with the old data. Why this delay is happening? (even though the writer thread is not coming out from the sqlite3_exec which i used to write the data but after a few sec its came out at that time onward i am getting the updated data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION and COMMIT.
> >         Please give a suggestion for this.
> >
> >         Thank you
> >
> >         _______________________________________________
> >         sqlite-users mailing list
> >         [hidden email] mailto:[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] mailto:[hidden email]
> >         http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >     >
>      
>
>      
>
>     ___________________________________________
>     Gunter Hick | Software Engineer | Scientific Games International GmbH http://www.scigames.at | 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] sqlite3_exec()

Simon Slavin-3


On 4 Dec 2018, at 4:57am, Prajeesh Prakash <[hidden email]> wrote:

> How we can find the number of record that newly added to the table once after the new recorded added(Just for a verification).

Either as an API function or as a SQL function:

<https://sqlite.org/c3ref/changes.html>

<https://sqlite.org/lang_corefunc.html#changes>

If changes have consequences you might prefer

<https://sqlite.org/c3ref/total_changes.html>

<https://sqlite.org/lang_corefunc.html#total_changes>

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