RE: [sqlite] Help with "SQL error: database is locked"
I have found the problem. The lock hash table uses inodes as a hash
key. Well, we are using DOS (I know, I know ...) so that did not work
and all files were using the same lock.
Thanks,
Juan//
-Original Message-
From: Juan Romano [mailto:[EMAIL PROTECTED]
Sent: Friday, April 16, 2004 10:06 AM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] Help with "SQL error: database is locked"
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 sqlitepage
Re: [sqlite] Concurrency, MVCC
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"
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: Ente
RE: [sqlite] Exists keyword and corelated subqueries
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
> 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
Thanks a lot for that, Kurt. Any chance of their being added to SQLite anytime soon ? Vijay Kurt Welgehausen <[EMAIL PROTECTED]> To 16/04/2004 16:44 [EMAIL PROTECTED] cc Subject Re: [sqlite] Exists keyword and corelated subqueries > 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] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] archive of this list
http://www.mail-archive.com/[EMAIL PROTECTED]/ http://www-lnc.usc.edu/~brannon/sqlite-mail-archive/gzip-file/ (old) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Exists keyword and corelated subqueries
> 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
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
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]
