Re: [sqlite] Concurrency, MVCC

2004-04-16 Thread Christian Smith
On Thu, 15 Apr 2004, Doug Currie wrote:

>
>Thursday, April 15, 2004, 9:16:01 AM, Christian Smith wrote:
>
>> On Wed, 14 Apr 2004, Doug Currie wrote:
>
>>>One way to get table level locking without a great deal of pain is to
>>>integrate the shadow paging ideas with BTree management. Rather than
>>>using page tables for the shadow pages, use the BTrees themselves.
>>>This means that any change to a BTree requires changes along the
>>>entire path back to the root so that only free pages are used to store
>>>new data, including the BTree itself. Writing the root page(s) of the
>>>BTree(s) commits the changes to that table (these tables).
>
>> Actually, this gets my vote. Keeps the pager layer the same,
>
>The pager gets *much* simpler because it doesn't need to make a log
>file. The log file is not necessary because writes only go to free
>pages.
>
>Well, there would be one write-ahead log. It's needed to prevent
>partial updates to the page number pointers to the roots page(s) of
>the BTree(s) at commit. This log is created at commit time, and is
>much simpler and much smaller than the present log file.

I'd have thought it'd be better to preserve the pager layer as is. If it
ain't broke...


> [...]
>
>This design works well. It has the advantage (compared with shadow
>pager) that reads are not burdened with page table indirection. It has
>the potential disadvantage (compared with SQLite 2.8) that small
>writes can modify several pages (based on the depth of the BTree).

So for reads, there is basically no extra burden (other than the caching
of the initial tree roots,) and writing will be slightly slower, but with
decreasing penalty as updates get bigger, and probably insignificant
against dumping of the page cache when transactions are finished, and all
of course in parallel with reads, so overall performance should be improve
in many scenarios.

It would of course be limited, like shadow paging, to a single address
space (writes would block reads in other address spaces.)

>
>I used this design in a proprietary database in the late 1980s. The
>only reason I didn't consider modifying SQLite this way up until now
>is that I was anticipating BTree changes for 3.0, so I confined my
>efforts to the pager layer.

Given this design, if it is adopted, it would also be trivial (and free in
terms of IO) to maintain a running total of records in a given btree as
well, as was requested some weeks back, as any new/deleted records would
update the btree to the root anyway.

Is this design feasible given the time constraints on 3.0? I've not
studied the btree layer in much detail, so don't know how much existing
code would need to change.

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] Help with "SQL error: database is locked"

2004-04-16 Thread Juan Romano
Hi, I am in the process of porting sqlite (version 2.8.13) to vxworks. I
am running into some problems.
It seems to work when I invoke the shell with no database (:memory:). I
can then create tables, insert records, etc. In general I can submit SQL
queries at will.  However, when I invoke the shell with a database
filename, whether the file exists or not, I get the error:
SQL error: database is locked
It seems that the database is write-locked to start the transaction and
then an attempt to get a read lock fails. There is only one task
(thread) running which is running the shell application.
I should mention that I have made a number of changes, some temporary to
get it running.
1.- Shortened temporary file names.
2.- unlink does not do anything (I think this results in temporary files
being persistent).
3.- __DJGPP__ is defined so that fcntl is a nop (no file locking).
4.- The shell only takes one argument, the database name.

After I exit the shell, there are 3 files created: mydatabase,
mydatabase-journal, and the temporary file (normally sqlite_xy..).

A side question I have is: Can you disable transactions ? If so, how ?

Any help would be much appreciated.

Thanks.

Juan//

What follows is a trace from a bunch of debug statements I have added in
the process of debugging my port. I hope it helps:

>sqlite_shell_main("/tffs0/mydatabase")

sqlite> create table autos(year smallint, make varchar(32), model
varchar(32), id smallint);

sqlite_open: Enter
sqlite_open: Call sqliteBtreeFactory
sqliteBtreeFactory: Enter
sqliteBtreeFactory: Call sqliteBtreeOpen
sqliteBtreeOpen: Enter
sqlitepager_open: Enter (filename = /tffs0/mydatabase)
sqliteOsOpenReadWrite: Enter (filename = /tffs0/mydatabase)
sqliteOsOpenReadWrite: opened file /tffs0/mydatabase
sqliteOsOpenReadWrite: id = 0x1ed63e0
findLockInfo: nRef = 1
findLockInfo: cnt = 0
sqliteOsOpenReadWrite: SQLITE_OK
sqlitepager_open: sqliteOsOpenReadWrite returned 0
sqlitepager_open: SQLITE_OK
sqliteBtreeOpen: SQLITE_OK
sqliteInit: Enter
sqliteInitOne: Enter
sqliteStartTable: Call sqliteBtreeFactory
sqliteBtreeFactory: Enter
sqliteBtreeOpen: Enter
sqlitepager_open: Enter (filename = (null))
sqlitepager_opentemp: Enter
sqliteOsTempFileName: zBuf = /sql_
sqliteOsOpenExclusive: Enter (filename = /tffs0/sql_B3j)
sqliteOsOpenExclusive: opened /tffs0/sql_B3j (fd = 47).
findLockInfo: found pLock (nRef = 2)
sqliteOsOpenExclusive: delFlag. unlink file /tffs0/sql_B3j
sqliteOsOpenExclusive: SQLITE_OK
sqlitepager_opentemp: rc = 0
sqlitepager_open: sqlitepager_opentemp returned 0
sqlitepager_open: SQLITE_OK
sqliteBtreeOpen: SQLITE_OK
fileBtreeCursor: Enter
lockBtree: Enter
lockBtree: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: First page. Get a read lock !
sqliteOsReadLock: Enter
sqliteOsReadLock: id->pLock->cnt = 0
sqliteOsReadLock: id->locked = 0
sqliteOsReadLock: s = 0
sqliteOsReadLock: increment nLock
sqliteOsReadLock: pLock->cnt (0x1ecc65c) set to 1
sqliteOsReadLock: id->pLock->cnt = 1
sqliteOsReadLock: id->locked = 1
sqliteOsReadLock: returns Something
sqlitepager_pagecount: Enter (file = /tffs0/mydatabase)
sqlitepager_get: SQLITE_OK
sqlitepager_pagecount: Enter (file = /tffs0/mydatabase)
lockBtree: return 0
fileBtreeCursor: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
fileBtreeCursor: SQLITE_OK
fileBtreeGetMeta: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
fileBtreeGetMeta: SQLITE_OK
fileBtreeCursor: Enter
lockBtree: Enter
lockBtree: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: First page. Get a read lock !
sqliteOsReadLock: Enter
sqliteOsReadLock: id->pLock->cnt = 1
sqliteOsReadLock: id->locked = 0
sqliteOsReadLock: pLock->cnt (0x1ecc65c) incremented to 2
sqliteOsReadLock: id->pLock->cnt = 2
sqliteOsReadLock: id->locked = 1
sqliteOsReadLock: returns Something
sqlitepager_pagecount: Enter (file = /tffs0/sql_B3j)
sqlitepager_get: SQLITE_OK
sqlitepager_pagecount: Enter (file = /tffs0/sql_B3j)
lockBtree: return 0
fileBtreeCursor: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
fileBtreeCursor: SQLITE_OK
moveToRoot: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
sqliteOsUnlock: pLock->cnt (0x1ecc65c) decremented to 1
sqliteOsUnlock: return Something
fileBtreeCursor: Enter
fileBtreeCursor: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
fileBtreeCursor: SQLITE_OK
fileBtreeGetMeta: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
fileBtreeGetMeta: SQLITE_OK
moveToRoot: Calling sqlitepager_get
sqlitepager_get: Enter
sqlitepager_get: SQLITE_OK
sqliteOsUnlock: pLock->cnt (0x1ecc65c) set to 0
sqliteOsUnlock: return Something
sqliteInitOne: Final returns 0
sqliteInit: sqliteInitOne returned 0
sqliteInit: returns 0
sqlite_open: sqliteInit returned 0
sqlite_open: return db = 0x1ecc424
fileBtreeBeginTrans: Enter
lockBtree: Enter
lockBtree: Calling sqlitepager_get
sqlitepager_get: 

RE: [sqlite] Exists keyword and corelated subqueries

2004-04-16 Thread Thomas, Basil
I think once SQLite ver 3.0 stabilizes, all of the enhancements to the SQL
language
capabilities will be addressed then. Once the the file format and low level
engine
enhancements are in production, I am sure many of us will want more
compliant SQL
code similar to SQL Server/Oracle/Sybase/MySQL/etc... but streamlined using
the KISS
principle that SQLite follows...

-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED]
Sent: Friday, April 16, 2004 12:31 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Exists keyword and corelated subqueries


> Any chance of their being added ... ?

I doubt it; there have been lots of proposals for changes and
additions flying around, but these points haven't come up (as
best I can recall).

Regards


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


This e-mail may be
privileged and/or confidential, and the sender does not waive any related
rights and obligations. Any distribution, use or copying of this e-mail or the
information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or
otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.



Re: [sqlite] Exists keyword and corelated subqueries

2004-04-16 Thread Kurt Welgehausen
> Any chance of their being added ... ?

I doubt it; there have been lots of proposals for changes and
additions flying around, but these points haven't come up (as
best I can recall).

Regards


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Exists keyword and corelated subqueries

2004-04-16 Thread Kurt Welgehausen
> EXISTS
No.  Instead of 'where exists (select * ... )', use
'where (select count(*) ...) > 0'.

> cor[r]elated subqueries
No.  They're called variable subqueries on the
Features-not-supported page.

> range variables, or are they host variables ?
Both. (Neither is supported.)

Regards

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] Exists keyword and corelated subqueries

2004-04-16 Thread VTenneti




Hi,

Can anyone please tell me if 'EXISTS' is supported (for subqueries in the
WHERE clause) and also if corelated subqueries are supported ?

One more thing: on the 'features not supported' page, the second point
mentions variables not being supported in queries: are these 'corelation
variables' (also called range variables), or are they host variables ?

Thanks a lot,

Vijay


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] archive of this list

2004-04-16 Thread Andrew Shakinovsky
Does anyone know if there is an archive of this list anywhere?

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]