RE: [sqlite] Problem storing integers

[Richard Boulton]

> I'm running the latest sqlite 3.2.1 command line tool on Windows
> XP

I've just run some older versions of the command line tool and the last time
the value 281474976710655 was stored correctly was 3.0.8 I was missing a
couple of releases after 3.0.8 but saw the unexpected behaviour start in
3.1.2. Maybe it was introduced in 3.1?

Rich

[sqlite] Problem storing integers

[Richard Boulton]

Hi,

I'm running the latest sqlite 3.2.1 command line tool on Windows XP and have
noticed that I don't seem to be able to store 48bit integers anymore :-S

CREATE TABLE test (a INTEGER);
INSERT INTO test VALUES(4294967295);
INSERT INTO test VALUES(1099511627775);
INSERT INTO test VALUES(281474976710655);
INSERT INTO test VALUES(72057594037927935);
SELECT * FROM test;

Results in:

4294967295
1099511627775
-1
72057594037927935

i.e. 281474976710655 is stored as -1

Regards,
Rich

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Richard Boulton]

> > The point is that when two threads or
> > processes are trying to write at the same time, one of the two
> > must back off, abandon their transaction (using ROLLBACK) and let
> > the other proceed.
>
> And how can this be done? What if there are more threads involved? Who
> decides?
>

I found the document http://www.sqlite.org/lockingv3.html provides a very
good discussion of the locking for Version 3.

Only one thread can hold a PENDING lock, this is the thread that is trying
to commit it's transaction (i.e. get an EXCLUSIVE lock). Other threads
trying to update can only have SHARED locks and be trying to get RESERVED
locks. The change in 3.0.4 means that when using a busy handler (e.g.
sqlite3_busy_timeout) the threads trying to get RESERVED locks will not
retry, but instead will return immediately with SQLITE_BUSY. If these
transactions are rolled back the thread with the PENDING lock is free to
proceed when the busy handler retries the lock.

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Richard Boulton]

I ran into a similar problem, I used the sqlite3_busy_timeout so that SQLite
automatically retries the locks. In 3.0.4 a change was made so that
SQLite doesn't retry a RESERVED lock (to avoid the deadlock), therefore if I
get a SQLITE_BUSY
return code I rollback the offending transaction and retry it, the
sqlite3_busy_timeout takes care of the other thread/process that's trying to
get the EXCLUSIVE lock.

I use sqlite3_prepare/sqlite3_step so I'm guessing it'll be similar with
sqlite3_exec. Here's an example of the logic I'm using:

sqlite3_busy_timeout(db, 5000)
do {
  rc = execQuery(db, "BEGIN TRANSACTION;");
  if (rc == SQLITE_DONE) rc = execQuery(db, "UPDATE test SET num = 2 WHERE
num = 1;");
  if (rc != SQLITE_DONE) {
execQuery(db, "ROLLBACK TRANSACTION;");
  } else {
execQuery(db, "COMMIT TRANSACTION;");
  }
  if (rc = SQLITE_BUSY) sleep(1);
} while(rc = SQLITE_BUSY)

I'm new to SQLite so I don't know if this is the "right" way to handle this,
maybe someone can comment on this?

Hope this helps,

Richard

- Original Message - 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 2:12 AM
Subject: [sqlite] Deadlock when doing threaded updates and inserts


> I'm running into a deadlock, as the subject says, when doing updates on
> a table in one thread while another thread is inserting into the same
> table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)
>
> The update thread returns from its UPDATE command (within a
> transaction) with SQLITE_BUSY when it sees a pending lock. The insert
> thread returns SQLITE_BUSY from END TRANSACTION when it can't get an
> exclusive lock.
>
> Attached is a simple C program that demonstrates this. I open two
> database handles on the same file (with a table "test" with a single
> column "num") and do:
>
> db1: BEGIN TRANSACTION;
> db2: BEGIN TRANSACTION;
> db1: INSERT INTO test VALUES ( 1 );
>
> At this point, both of these return SQLITE_BUSY:
>
> db2: UPDATE test SET num = 2 WHERE num = 1;
> db1: END TRANSACTION;
>
> Is this a bug? Or do I have to do something with sqlite 3 I didn't with
> 2?
>
> Thanks,
> -Dave
>
>

Re: [sqlite] Multiple update processes

[Richard Boulton]

> Good point. I think the right fix for this is for me to change SQLite
> so that it does not invoke the busy callback when it is trying to
> acquire a RESERVED lock. If it fails to get a RESERVED lock, then
> it returns SQLITE_BUSY right away. The busy callback will then only
> be invoked when trying to get a PENDING lock.

I just tried the modification you suggested and it appears to make things
much better, thanks.

I'd just like to confirm I'm handling the SQLITE_BUSY correctly with this
modification:
When I receive a SQLITE_BUSY from sqlite3_step and I am in a transaction, I
am doing a rollback (I assume this releases any SHARED locks) before
retrying the entire transaction. For an automatically started transaction I
am just retrying the sqlite3_step.

Thanks,

Richard

[sqlite] Multiple update processes

[Richard Boulton]

Hi,

I have been experimenting with SQLite version 3.0 and am trying to implement
a simple publish - subscribe model where multiple processes can
publish/subscribe to messages stored in a SQLite table.

This works fine when a single process is updating the database at any one
time, however I am struggling to get this working with multiple processes
concurrently updating the database. Once multiple processes try to perform
updates on the database, most processes fail to update the database and I
get lots of SQLITE_BUSY errors.

I have tried using the sqlite3_busy_timeout routine, but this just seems to
make things worse as all processes remain locked out for the period of the
timeout and then return SQLITE_BUSY anyway.

I turned on the debugging in the locking code and ran a simple case where 2
processes were trying to update the database, it appears that one process is
trying to COMMIT it's transaction (it has a PENDING lock and is trying to
get an EXCLUSIVE lock), while the other process is attempting to BEGIN a
transaction (it has a SHARED lock and is trying to get a RESERVED lock).
This seems to cause a deadlock for the period of the sqlite3_busy_timeout.

I had a look back through the mailing list and found a thread with a
discussion in this area "A quick code review, analysis of locking model,
"fine-tuning" suggestions", are any changes being made in this area to
resolve this type of problem?

I am new to SQLite so maybe I'm not handling the SQLITE_BUSY return codes
correctly for this scenario. Does anybody know of a way to handle multiple
processes updating a database efficiently in version 3?

Many thanks,

Richard