[sqlite] Re: Isolation level of deferred transactions

[Igor Tandetnik]

Alexander J. Kozlovsky
<[EMAIL PROTECTED]> wrote:

T2 will try to acquire RESERVED lock of its own, but T1 is already
holding PENDING. At this point SQLite will return to the caller with
SQLITE_BUSY return code. The only way out of this situation is for
T2 to ROLLBACK and retry the transaction from the beginning.


Suppose next:

1. Transaction T2 perform complex calculation

2. T1 have time to release EXCLUSIVE lock ***before*** T2 try
  to acquire RESERVED lock


T1 won't be able to even acquire EXCLUSIVE lock, let alone release it, 
while T2 holds SHARED. Remember that T2 holds a SHARED lock from the 
beginning. The rest of your scenario thereby becomes moot.


Igor Tandetnik 

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Reid Thompson]

So, if I'm reading your message right,  the 25 seconds for inserting 
200 (that is only 200 messsages)  into a database under win 2000 is 
correct???
Perhaps it's just my  uneducated opinion but your explanation sounds 
bogus...  Why would the folks who wrote SQLITE show it off as being 
so fast if, under win 2000, it only added 200 records every 25 
seconds
And, why does adding a begin: / end:commit: around the whole loop 
speed up the operations so drastically???


just realized also, clock() is probably NOT what you want to use for 
your test. 
You probably want to use time() or gettimeofday().

Clock determines processor time used:

*NAME*
  clock  - Determine processor time 


*SYNOPSIS*
  *#include *

  *clock_t clock (void);*

*DESCRIPTION*
  The *clock () *function returns an 
approximation of processor time  used by
  the program.

*RETURN VALUE*
  The value returned is the CPU time  
used so far as  a *clock_t*; to get the
  number   of  seconds  used,  
divide by *CLOCKS_PER_SEC*.   If the 
processor
  time  used is not available or 
its  value  cannot  be  represented,  the
  function returns the value (clock_t)-1.

As info -- a correlating test with   
---

PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3

on a Dell laptop 256MB RAM, hdparm -W0 ( write caching off).
dmesg | grep processor => Detected 697.025 MHz processor.

$ ./a.out
added 200 records to notes
time1: 1126994873 time2: 1126994876
elapsed time: 3
seconds: 3  end_microsecs:  813272 start_microsecs 905119 microsecs -91847
..takes between 3 and 4 seconds

Second run wrapped in transaction:
$ ./a.out
added 200 records to notes
time1: 1126994915 time2: 1126994915
elapsed time: 0
seconds: 0  end_microsecs:  237435 start_microsecs 180558 microsecs 56877
..takes 56877 microseconds


#include 
#include 
#include 
#include "libpq-fe.h"

static void
exit_nicely (PGconn * conn)
{
 PQfinish (conn);
 exit (1);
}

int
main (int argc, char **argv)
{
 time_t t1;
 time_t t2;
 struct timeval tb1;
 struct timeval tb2;
 const char *conninfo;
 PGconn *conn;
 PGresult *res;
 int nFields;
 int i;
 long t_diff;


 conninfo = "dbname = test";

 /* Make a connection to the database */
 conn = PQconnectdb (conninfo);

 /* Check to see that the backend connection was successfully made */
 if (PQstatus (conn) != CONNECTION_OK)
   {
 fprintf (stderr, "Connection to database failed: %s",
  PQerrorMessage (conn));
 exit_nicely (conn);
   }

 res = PQexec (conn, "drop table notes");
 if (PQresultStatus (res) != PGRES_COMMAND_OK)
   {
 fprintf (stderr, "drop table command failed: %s",
  PQerrorMessage (conn));
 //PQclear(res);
 //exit_nicely(conn);
   }
 PQclear (res);

 res = PQexec (conn, "create table notes(subject text)");
 if (PQresultStatus (res) != PGRES_COMMAND_OK)
   {
 fprintf (stderr, "create table command failed: %s",
  PQerrorMessage (conn));
 PQclear (res);
 exit_nicely (conn);
   }
 PQclear (res);

/* comment out BEGIN txn
 res = PQexec (conn, "BEGIN");
 if (PQresultStatus (res) != PGRES_COMMAND_OK)
   {
 fprintf (stderr, "BEGIN command failed: %s", PQerrorMessage (conn));
 PQclear (res);
 exit_nicely (conn);
   }
 PQclear (res);
*/

 (void) time ();
 (void) gettimeofday (, NULL);
 for (i = 1; i < 201; i++)
   {
 char statement[1024];
 char buffer[50];
 sprintf (buffer, "my subject%d", i);

 sprintf (statement, "%s %s%s", "insert into notes(subject) values('",
  buffer, "')");
 res = PQexec (conn, statement);
 if (PQresultStatus (res) != PGRES_COMMAND_OK)
   {
 fprintf (stderr, "insert command failed: %s",
  PQerrorMessage (conn));
 PQclear (res);
 exit_nicely (conn);
   }
 PQclear (res);
   }

/* comment out COMMIT txn
 res = PQexec (conn, "COMMIT");
 PQclear (res);
*/

 (void) time ();
 (void) gettimeofday (, NULL);

 printf ("added %d records to notes\n", i - 1);
 printf ("time1: %d time2: %d\n", t1, t2);
 printf ("elapsed time: %d\n", (t2 - t1));

 t_diff = (long) ((tb2.tv_sec - tb1.tv_sec) + (tb2.tv_usec - tb1.tv_usec));
 printf("seconds: %ld  end_microsecs:  %ld start_microsecs %ld 
microsecs %ld\n",

(tb2.tv_sec - tb1.tv_sec), tb2.tv_usec, tb1.tv_usec,
tb2.tv_usec - tb1.tv_usec);

 /* close the connection to the database and cleanup */
 PQfinish (conn);

 return 0;
}

Re: [sqlite] INSERTing a row only if it doesn't EXIST

[Puneet Kishor]

On Sep 17, 2005, at 4:35 PM, Alexander J. Kozlovsky wrote:


I want to avoid doing a two step process outside the db... I want to
insert a row only if it doesn't exist already.


IMHO, if you table T1 have a unique key, you may do this

INSERT OR IGNORE T1 VALUES(1, 2, 3);




ahhh! the conflict algorithm. Thanks for drawing my attention to it... 
it should do the trick.



--
Puneet Kishor

Re: [sqlite] INSERTing a row only if it doesn't EXIST

[Kurt Welgehausen]

If id in your example identifies a row, then by
definition it is unique (probably the primary
key). If you try to insert another row with the
same id, the insert will fail. Why not just
catch the exception or error code?

Regards

Re: [sqlite] INSERTing a row only if it doesn't EXIST

[Alexander J. Kozlovsky]

> I want to avoid doing a two step process outside the db... I want to
> insert a row only if it doesn't exist already.

IMHO, if you table T1 have a unique key, you may do this

INSERT OR IGNORE T1 VALUES(1, 2, 3);


Best regards,
 Alexandermailto:[EMAIL PROTECTED]

Re: [sqlite] Isolation level of deferred transactions

[D. Richard Hipp]

On Sun, 2005-09-18 at 01:03 +0400, Alexander J. Kozlovsky wrote:
> Hence, SQLite deferred transactions is not serializable ones.
> 

Your understanding is incorrect.  SQLite does *not* release
locks in the middle of a transaction - ever.  It is always
serializable.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Isolation level of deferred transactions

[Alexander J. Kozlovsky]

Yes, I understand now.

Thanks for explanation!

  

Best regards,
 Alexander  mailto:[EMAIL PROTECTED]

[sqlite] INSERTing a row only if it doesn't EXIST

[Puneet Kishor]

I want to avoid doing a two step process outside the db... I want to 
insert a row only if it doesn't exist already. REPLACE INTO seems to 
almost do the trick, however, seems like it will UPDATE if the row 
already exists. I want the row to be left alone if it exists.


Am trying to figure out if EXISTS could be creatively used to do this

SELECT EXISTS (SELECT id FROM t WHERE id = 5)

return 1 if 5 exists, and 0 if it doesn't exist. Is it possible to do 
an INSERT based on this? Or, do I have to use the logic in my 
application? My brain is not working, so any help on this would be 
appreciated.



--
Puneet Kishor

Re: [sqlite] Isolation level of deferred transactions

[Alexander J. Kozlovsky]

> SQLite transactions are always serializable.

Serializable mode requires two-phase locking protocol. In practice
it means all locks (for read and for write) hold until transaction
end.

Deferred transaction drop SHARED lock in middle of transaction
and replace it with RESERVED lock. Hence, it is not two-phase
transaction. AFAIK this behaviour is not allowed for true
serializable transactions.

With correct lock implementation serializable transactions
must not release READ lock in the middle. If two transaction
hold READ locks and then both of them try to acquire WRITE
locks it must lead to deadlock. It is the reason for special
UPDATE or UPGRADE lock mode which means "read, and then
possible write".

In previous mail I describe situation when two transactions
started, acquires SHARED mode and then (one-after-another)
update database and commit successfully. According
documentation (http://sqlite.org/lockingv3.html) this can
take place if first transaction release EXCLUSIVE lock
before second transaction try to acquire RESERVED lock.

Hence, SQLite deferred transactions is not serializable ones.


Best regards,
 Alexandermailto:[EMAIL PROTECTED]

Re: [sqlite] Version 3.2.6

[Jolan Luff]

On Sat, Sep 17, 2005 at 03:41:21PM -0400, D. Richard Hipp wrote:
> Version 3.2.6 is now available on the website

3.2.6 does not compile on FreeBSD and OpenBSD because they do not have
fdatasync().  AFAICT, NetBSD's fdatasync implementation does the same
thing as fsync.

[sqlite] Version 3.2.6

[D. Richard Hipp]

Version 3.2.6 is now available on the website

http://www.sqlite.org/

This version fixes a bug that might cause database
corruption if you VACUUM a database larger than 1GiB
but the vacuum aborts and tries to rollback.  

Also in this version:

   The ORDER BY and GROUP BY process was completely
   rewritten.

   COUNT(DISTINCT) is now supported.

   The LIKE operator might use indices to speed its
   search if the column being searched uses COLLATE NOCASE.

   Lots of smaller bug fixes and miscellaneous 
   enhancements.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Re: Isolation level of deferred transactions

[Igor Tandetnik]

Alexander J. Kozlovsky wrote:

I try to understand which standard SQL isolation level correspond with
transaction opened as BEGIN DEFERRED. I think this is "DIRTY READ",
(quite dangerous level) because deferred transactions allow
unrepeatable reads.


I believe SQLite provides the highest isolation level, known as 
SERIALIZABLE in MS SQL Server. That is, no dirty reads, repeatable, no 
phantom rows.



I have two question:

1. Is my understanding correct, and deferred mode really dirty read?


I believe you are incorrect.


2. Is deferred mode the default transaction mode in the current
  version of SQLite?


Yes.


The next is explanation of my point of view.
Consider this transaction:


1. BEGIN DEFERRED

2. SELECT Value FROM Deposits WHERE ClientID = 123

3. New deposit value calculated in C++ program: Value = Value - 100
  (Suppose this is very complex calculation and cannot be embedded
  directly in UPDATE query)

4. UPDATE Deposits SET Deposit = <>
  WHERE ClientID = 123

5. COMMIT


What happened if two such transactions T1 and T2 executed
in parallel with same ClientID? If I understand correct,
the next execution sequence is possible:


1. T1 executes BEGIN DEFERRED and get SHARED lock.

2. T2 executes BEGIN DEFERRED and get SHARED lock as well.

3. T1 executes SELECT and get deposit value (for example, 1000)

4. T2 executes SELECT and get the same deposit value (1000)

6. T1 acquires RESERVED lock, do UPDATE (set deposit value to
  1000 - 100 = 900) and then performs COMMIT


RESERVED lock is insufficient to perform an update. To actually write 
data to the database file, the transaction needs to acquire EXCLUSIVE 
lock. But this is not possible while there is a SHARED lock in place. So 
T1 will acquire a PENDING lock and sit there waiting while all readers 
clear. As soon as this happens, it will grab EXCLUSIVE lock.



7. T2 acquires RESERVED lock, do UPDATE (set deposit value to
  1000 - 100 = 900) and then performs COMMIT.
  Result of T1 transaction is lost.


T2 will try to acquire RESERVED lock of its own, but T1 is already 
holding PENDING. At this point SQLite will return to the caller with 
SQLITE_BUSY return code. The only way out of this situation is for T2 to 
ROLLBACK and retry the transaction from the beginning.


For more details, see http://www.sqlite.org/lockingv3.html

Igor Tandetnik 

Re: [sqlite] bug in SELECT DISTINCT ?

[Matt Wilson]

On Sat, Sep 17, 2005 at 03:23:04PM +0200, Miha Vrhovnik wrote:
> 
> both
> SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1;
> and
> SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 
> AS id FROM map WHERE d0 = 2);

sqlite> SELECT id, * FROM t1, map WHERE t1.id = map.idT1 GROUP BY id;
1|1|1|dd|1|2
2|2|1|dd|2|2
3|3|1|dd|3|2

Not sure if this is really what you want, because you're losing data
from the map as rows are excluded.
-- 
Matt Wilson
rpath, Inc.
[EMAIL PROTECTED]

[sqlite] Re: bug in SELECT DISTINCT ?

[Igor Tandetnik]

Miha Vrhovnik wrote:

So How can I select DISTINCT ON 1st column OR any other.
as sqlite does not support DISTINCT ON (column1, column2, )


What does it mean to select distinct on one column only? If you have a
table with two rows

1,1
1,2

and you want to select distinct on the first column, which one would you
want? How is the engine supposed to decide?

Igor Tandetnik 

[sqlite] Re: bug in SELECT DISTINCT ?

[Miha Vrhovnik]

"Igor Tandetnik" <[EMAIL PROTECTED]> je ob 17.9.2005 16:20:03 napisal(a):

>Why did you expect the latter? DISTINCT in SELECT DISTINCT detects
>identical rows, not identical first columns. The three first rows with
>an id of 1 differ in the columns you chose not to show (specifically in
>the map.d0 column), that's why all three are returned.
>
>The second query is equivalent to
>
>SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (1, 2, 3);
>
>Of course it returns all rows.
>

So How can I select DISTINCT ON 1st column OR any other.
as sqlite does not support DISTINCT ON (column1, column2, )

Regards,
Miha

[sqlite] bug in SELECT DISTINCT ?

[Miha Vrhovnik]

Hi!

I think there is a bug in SELECT DISTINC. I'm using sqlite 3.2.5 dll

CREATE TABLE t1(id INTEGER PRIMARY KEY, d0 INTEGER, d1 TEXT);
CREATE TABLE map(idT1 INTEGER , d0 INTEGER);

INSERT INTO t1 VALUES(1, 1, 'dd');
INSERT INTO t1 VALUES(2, 1, 'dd');
INSERT INTO t1 VALUES(3, 1, 'dd');

INSERT INTO map VALUES(1, 2);
INSERT INTO map VALUES(1, 3);
INSERT INTO map VALUES(1, 4);
INSERT INTO map VALUES(2, 2);
INSERT INTO map VALUES(3, 2);

both
SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1;
and
SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 AS 
id FROM map WHERE d0 = 2);

return
1, ...
1, ...
1, ...
2, ...
3, ...

instead of
1, ...
2, ...
3, ...

Regards,
Miha

Re: [sqlite] Thread safe in 3.2.5

[Guillaume Fougnies]

21 => SQLITE_MISUSE
You are using a db handle in multiple threads.

You can read this:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09910.html

Best regards,

Fri, Sep 16, 2005 at 05:34:37PM -0700: Rick Keiner wrote:
> I recently updated my system with the 3.2.5 version from 3.0.8. I configure 
> with --enable-threadsafe because I use mutliple threads to access a single 
> database. It had been working fine (it's the apr_dbd driver for the 
> apr.apache.org project), but after the upgrade I started seeing "error 21". 
> The library call was out of sequence. I noticed there was a change in the 
> 3.2.4 version related to configuring the threadsafe operation that stated the 
> threadsafe option wasn't working. I don't understand why it was changed. I 
> haven't had any problems until now. Any help would be appreciated.
>  
> Thanks,
> Rick Keiner
--
Guillaume FOUGNIES
Eulerian Technologies

Re: [sqlite] weird problem with windows 2000

[D. Richard Hipp]

On Thu, 2005-09-15 at 20:16 -0800, dan greene wrote:

> When I ran this program on the win2000 machine with NTFS, this loop took on 
> the order of 25 seconds as reported in the second printf!
> When I ran the same program on my win98 machine, it took 1.1 seconds.
> putting a begin; and end;commit; SQL around the entire loop, dropped the 
> execution on both machines into the tens of milliseconds.
> 
> Any thoughts as to what is happening on the WIN2000 NTFS machine to slow 
> down the single inserts so drastically?

On each COMMIT (which happens automatically after every SQL statement
unless you have an explicit BEGIN..COMMIT surrounding multiple
statements)
SQLite waits until all results are successfully stored on the disk
surface before continuing.  This is called being "Durable" and is the
"D" in "ACID".  

On windows, the API function to pause until all information has been
written to disk is FlushFileBuffers().  SQLite calls this function
during
each commit to force information out of the OS's internal cache and onto
the disk surface where it will be safe from lose due to power failures
or operating system crashes.  In order for an update to be atomic
(The "A" in "ACID"), two calls to FlushfileBuffers are required.  If
your disk drive is spinning at 7200 RPM, and each FlushFileBuffers
call requires one revolution of the disk, that means you can have at
most 120 FlushFileBuffer operations or 60 COMMITs per second.  Those
figures are the speed-of-light - most of the time COMMITs will be
slower.
But they appears to be faster than this on Win98.  This indicates to me
that FlushFileBuffers must not be working correctly on your Win98
machine.  I have heard that this was the case on many versions of
windows.

So to answer your question, COMMIT is much faster on Win98 because of
a bug in the operating system that can cause data loss or data
corruption
after a power failure or operating system crash.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Reid Thompson]

dan greene wrote:

So, if I'm reading your message right,  the 25 seconds for inserting 
200 (that is only 200 messsages)  into a database under win 2000 is 
correct???
Perhaps it's just my  uneducated opinion but your explanation sounds 
bogus...  Why would the folks who wrote SQLITE show it off as being so 
fast if, under win 2000, it only added 200 records every 25 seconds
And, why does adding a begin: / end:commit: around the whole loop 
speed up the operations so drastically???


Plase help!!!??

because w/o the begin-end/commit every insert must be committed to disk 
before the next insert begins.  Within a transaction( between the begin 
and end/commit ), all the inserts are 'posted', but there is only ONE 
commit at the the end.  All RDBMS systems will perform multiple 
consecutive db actions as you've described
within a transaction faster than they will without.  As previously 
noted, the time is spend syncing the data to your drive.


Results using mod/hack of your post(see below), Windows XP, athlon 
2500XP, 512MB RAM in CYGWIN..


$ ./a
added 200 records to notes
elapsed time: 265
cyberhome: /home/rthompso>
$ vi sqlitetest.c
cyberhome: /home/rthompso>
$ gcc -L/usr/local/lib  sqlitetest.c -lsqlite3
cyberhome: /home/rthompso>
$ ./a
added 200 records to notes
elapsed time: 15

The vi between the two invocations was to add the BEGIN/COMMIT calls.

$ cat sqlitetest.c
#include 
#include 
#include 
#include 
int
main()
{
   clock_t t1;
   clock_t t2;
   int i;
   int stat;
   sqlite3 *db;
   char *errmsg = 0;

   sqlite3_open("theDB", );
   (void) sqlite3_exec(db, "create table notes(subject)", 0, 0, );
   // time the additions
   t1 = clock();
   // add some rows
   (void) sqlite3_exec(db, "BEGIN", 0, 0, );
   for (i = 1; i < 201; i++)
   {
   char *statement;
   char buffer[50];
   sprintf(buffer, "my subject%d", i);
   statement =
   sqlite3_mprintf("insert into notes(subject) values('%q');", 
buffer);

   stat = sqlite3_exec(db, statement, 0, 0, );
   if (stat != SQLITE_OK)
   {
   printf("insert error at i=%1d: %s\n", i, errmsg);
   sqlite3_free(errmsg);
   break;
   }

   sqlite3_free(statement);
   }   // for
   (void) sqlite3_exec(db, "COMMIT", 0, 0, );

   t2 = clock();
   sqlite3_close(db);
   printf("added %d records to notes\n", i - 1);
   printf("elapsed time: %d\n", (t2 - t1));
}

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[Eugene Wee]

Hi Dan,

Perhaps it's just my  uneducated opinion but your explanation sounds 
bogus...  Why would the folks who wrote SQLITE show it off as being so 
fast if, under win 2000, it only added 200 records every 25 seconds
It doesnt take anywhere near that long when you wrap the inserts in a 
transaction, right?


> And, why does adding a begin: / end:commit: around the whole loop speed
> up the operations so drastically???
You need to read the wiki:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

Eugene Wee

Re: [sqlite] weird problem with windows 2000 PLEASE HELP!!!

[dan greene]

So, if I'm reading your message right,  the 25 seconds for inserting 200 
(that is only 200 messsages)  into a database under win 2000 is correct???
Perhaps it's just my  uneducated opinion but your explanation sounds 
bogus...  Why would the folks who wrote SQLITE show it off as being so fast 
if, under win 2000, it only added 200 records every 25 seconds
And, why does adding a begin: / end:commit: around the whole loop speed up 
the operations so drastically???


Plase help!!!??