Re: [sqlite] What is RESERVED state for?
The RESERVED lock increases concurrency by preventing other processes from gaining an EXCLUSIVE lock while still permitting reads. A PENDING lock prevents read (SHARED) locks being acquired preparatory to setting an EXCLUSIVE lock. The thread or process which holds a RESERVED lock is eventually going to promote it to EXCLUSIVE and alter the database but until the commit is launched readers are still given access to the database. In some ways you could think of a RESERVED lock as being a lock on the journal file. Richard Klein wrote: Hello all, I'm in the process of upgrading from SQLite 2 to SQLite 3, and am trying to understand the new lock states that version 3 introduced. I need this understanding because I will be porting SQLite 3 to an embedded OS that has no file locking API, and I don't want to use the "old-school" mechanism of having a .lock file (too slow). I have a couple of questions: (1) Can more than one process have a PENDING lock on a database? (I'm guessing that the answer is "no". My guess is that if one process has a PENDING lock on a database, then all the other processes that have opened that database have either no lock or a SHARED lock on the database. I'm further guessing that when all the SHARED locks eventually drop away, the PENDING lock is automatically promoted to EXCLUSIVE. Do I have this right?) (2) What is the purpose of the RESERVED lock? Since it doesn't seem to lead (directly or indirectly) to the PENDING or EXCLUSIVE state, and since it doesn't prevent other processes from acquiring SHARED locks, what is it good for? Thanks in advance, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is RESERVED state for?
On 10/12/07, Richard Klein <[EMAIL PROTECTED]> wrote: > (1) Can more than one process have a PENDING > lock on a database? (I'm guessing that the > answer is "no". My guess is that if one > process has a PENDING lock on a database, > then all the other processes that have > opened that database have either no lock > or a SHARED lock on the database. I'm > further guessing that when all the SHARED > locks eventually drop away, the PENDING lock > is automatically promoted to EXCLUSIVE. > Do I have this right?) Correct. > (2) What is the purpose of the RESERVED lock? > Since it doesn't seem to lead (directly or > indirectly) to the PENDING or EXCLUSIVE state, > and since it doesn't prevent other processes > from acquiring SHARED locks, what is it good > for? A RESERVED lock indicates a process is planning to write to the database file sometime in the future, so everyone else must be read only. It blocks future writers. A process holds a RESERVED lock as long as it has changes to make (because it has executed an INSERT or similar), but those changes are still held within its own cache and no COMMIT has been executed, so it doesn't need to actually modify the file yet. New readers (SHARED) are therefore okay. It will be promoted to PENDING when it must modify the file to proceed. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is RESERVED state for?
Hello all, I'm in the process of upgrading from SQLite 2 to SQLite 3, and am trying to understand the new lock states that version 3 introduced. I need this understanding because I will be porting SQLite 3 to an embedded OS that has no file locking API, and I don't want to use the "old-school" mechanism of having a .lock file (too slow). I have a couple of questions: (1) Can more than one process have a PENDING lock on a database? (I'm guessing that the answer is "no". My guess is that if one process has a PENDING lock on a database, then all the other processes that have opened that database have either no lock or a SHARED lock on the database. I'm further guessing that when all the SHARED locks eventually drop away, the PENDING lock is automatically promoted to EXCLUSIVE. Do I have this right?) (2) What is the purpose of the RESERVED lock? Since it doesn't seem to lead (directly or indirectly) to the PENDING or EXCLUSIVE state, and since it doesn't prevent other processes from acquiring SHARED locks, what is it good for? Thanks in advance, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Any more file format changes planned?
As you know, when the SQLite developers moved from version 2 to 3, they had to change the database file format (to implement the new features that version 3 offered). My question is: Can the developers envision any future feature additions that would require the file format to change again? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_exec function error:database is locked
varunkumar <[EMAIL PROTECTED]> wrote: so two different processes cannot access the database at a time One process cannot access the database at the same instant in time that another process is modifying the database. -- D. Richard Hipp <[EMAIL PROTECTED]> To clarify further: Process A and process B can both have the same database *open* at the same time. However, if process A tries to access (read or write) the database while process B is modifying (writing) it, then process A will get a SQLITE_BUSY error code returned to it. Process A should be prepared to handle this SQLITE_BUSY error. Typically he will want to sleep for a little while, and then try again. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Making BEGIN IMMEDIATE the default.
On Fri, 12 Oct 2007 01:00:32 -0500, you wrote:
>On Thu, 11 Oct 2007 13:33:35 +0200, Kees Nuyt wrote
>> On Wed, 10 Oct 2007 22:10:38 -0500, you wrote:
>>> You might want to be a little bit more clear about the fact that
>>> [transaction] nests even though BEGIN does not.
>>
>> The TCL transaction{} can be nested, the SQL BEGIN can't.
>
>It looks like I'm the one who was unclear. I was asking DRH to consider
>expanding the documentation a bit to underscore the fact that [transaction]
>can be used in nested fashion despite the limitations of BEGIN--- I had
>incorrectly assumed that since BEGIN doesn't nest, [transaction] doesn't nest
>either. Also, I didn't explain my typographical convention: [bracketed] words
>are Tcl commands, CAPITALIZED words are SQL keywords.
In retrospect, I could have done a better job reading your
message.
Ok, all clear now ;)
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite3_exec function error:database is locked
Yes, provided you synchronize on the busy checks. varunkumar wrote: so two different processes cannot access the database at a time John Stanton-3 wrote: You need to handle the SQLITE_BUSY condition in your program. varunkumar wrote: is it possible more than one process can access the sqlite3 database at a time? I am using sqlite3 database , I have the situation like follows in my project. In my project one process(daemon) doing as follows it is opening the database using sqlite3_open() preparing sqlite3 insert statement using sqlite3_mprintf() for inserting values using sqlite3_exec() executing the sqlite3 insert statement . finally calling sqlite3_free(), sqlite3_close() API s The above steps are doing in my process(daemon) for every one minute. I am generating reports from different processes like opening the database usig sqlite3_open() preparing sqlite3 select statement using sqlite3_mprintf() using sqlite3_exec() executing the sqlite3 statement finally calling sqlite3_free(), sqlite3_close() APIS problem when i am generating reports at particular time ,at the same time if that daemon process access the database by calling sqlite3_exec() the daemon process getting the error like sqlite3_exec function error:database is locked. using sqlite3_step() API instead of sqlite3_exec() API verify the database whether database is busy or not using return value of SQLITE_BUSY. if SQLITE_BUSY i have to wait untill database is not busy. but i donot want to wait at that time because if i wait at that time our project would not synchronize with other processes, it should insert values at that time if any processes also using the database at the same time. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Accessing external applications from within SQLite triggers
Triggers won't help, either, because triggers run in the sqlite3 handle which, um, triggers. So if app A makes a change, the trigger runs in app A, but not in app B. You could have a custom function which the trigger invokes to send an IPC over to app B. -scott On 10/12/07, Vladimir Stokic <[EMAIL PROTECTED]> wrote: > > Hi there, > > I would like to know if there is any way to access some external application > from within an SQLite trigger. > > I have an application A that updates some rows in my SQLite database. What I > want is to be able to detect that the change has occured from the > application B, without having to use the polling mechanism (i.e. to > periodically poll the database and check if the change has occured). > Basically, I need an alarm mechanism that will notify the application B that > the change has occured on the particular table in the database. > > I have read the documentation on http://www.sqlite.org/capi3ref.html and I > have not found anything that would be useful in this particular situation. > At first I thought it could be done using sqlite3_update_hook, but then I > learned that sqlite3_update_hook does not work that way. > > If anyone could help me, I would be very grateful. > > Thanks in advance, > Vladimir Stokic > -- > View this message in context: > http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13177010 > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Accessing external applications from within SQLite triggers
Vladimir Stokic wrote: I would like to know if there is any way to access some external application from within an SQLite trigger. Create a custom function doing that, call it from the trigger. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_exec function error:database is locked
varunkumar <[EMAIL PROTECTED]> wrote: > so two different processes cannot access the database at a time > One process cannot access the database at the same instant in time that another process is modifying the database. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Accessing external applications from within SQLite triggers
Hi there, I would like to know if there is any way to access some external application from within an SQLite trigger. I have an application A that updates some rows in my SQLite database. What I want is to be able to detect that the change has occured from the application B, without having to use the polling mechanism (i.e. to periodically poll the database and check if the change has occured). Basically, I need an alarm mechanism that will notify the application B that the change has occured on the particular table in the database. I have read the documentation on http://www.sqlite.org/capi3ref.html and I have not found anything that would be useful in this particular situation. At first I thought it could be done using sqlite3_update_hook, but then I learned that sqlite3_update_hook does not work that way. If anyone could help me, I would be very grateful. Thanks in advance, Vladimir Stokic -- View this message in context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13177010 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Advice about a trigger
I believe it would be enough if you wrote something like this:
CREATE TRIGGER trigger_delete Before DELETE ON table1
BEGIN
INSERT INTO backup_table(oldid, sql, tablename, operation)
VALUES (old.rowid, "INSERT INTO table1 values
("||old.rowid||","||old.column1||","||...||old.lastColumn||")" , "table1",
1);
END
Basically, instead of "sql" you would put in a concatenated string formed
like the one above using the || operator.
Hope this helps
Vladimir Stokic
Marco Bambini wrote:
>
> I need to create a trigger that BEFORE a row is deleted from a table,
> the sql used to create that row (or a way to recreate it) should be
> saved to another backup table.
> For example:
>
> CREATE TRIGGER trigger_delete Before DELETE ON table1
> BEGIN
> INSERT INTO backup_table(oldid, sql, tablename, operation)
> VALUES (old.rowid, "sql", "table1", 1);
> END
>
> The missing field is "sql" ... do you have a smart idea to solve my
> problem?
> Thanks.
> ---
> Marco Bambini
> http://www.sqlabs.net
> http://www.sqlabs.net/blog/
> http://www.sqlabs.net/realsqlserver/
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/Advice-about-a-trigger-tf4568708.html#a13177002
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite3_exec function error:database is locked
so two different processes cannot access the database at a time John Stanton-3 wrote: > > You need to handle the SQLITE_BUSY condition in your program. > > varunkumar wrote: >> is it possible more than one process can access the sqlite3 database at a >> time? >> >> I am using sqlite3 database , I have the situation like follows in my >> project. >> >> In my project one process(daemon) doing as follows >> >> it is opening the database using sqlite3_open() >> preparing sqlite3 insert statement using sqlite3_mprintf() for >> inserting values >> using sqlite3_exec() executing the sqlite3 insert statement . >> finally calling sqlite3_free(), sqlite3_close() API s >> >> The above steps are doing in my process(daemon) for >> every >> one minute. >> >> I am generating reports from different processes like >> >> opening the database usig sqlite3_open() >> preparing sqlite3 select statement using sqlite3_mprintf() >> using sqlite3_exec() executing the sqlite3 statement >> finally calling sqlite3_free(), sqlite3_close() APIS >> >> >> >> >> >> problem >> >> when i am generating reports at particular time ,at the same time if >> that daemon process access >> the database by calling sqlite3_exec() the daemon process getting the >> error like >> sqlite3_exec function error:database is locked. >> >> using sqlite3_step() API instead of sqlite3_exec() API verify the >> database whether >>database is busy or not using return value of SQLITE_BUSY. if >> SQLITE_BUSY >>i have to wait untill database is not busy. but i donot want to wait >> at >> that time >>because if i wait at that time our project would not synchronize with >> other >>processes, it should insert values at that time if any processes also >> using the database at >>the same time. >> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/sqlite3_exec-function-error%3Adatabase-is-locked-tf4612181.html#a13176997 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] loadext bug
Ken wrote: Minor bug: Please add the prototype and defines for the following to sqlite3ext.h void (*result_error_toobig)(sqlite3_context*); #define sqlite3_result_error_toobigsqlite3_api->result_error_toobig Without these, externally loadded libs that would like to use error_toobig will not load into sqlite. Ken, You should file a bug report at http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew so this gets fixed. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
I am evaluating SQLite for a project I am working on. I have a question about the behavior of the INTEGER PRIMARY KEY auto increment feature. My platform uses 32 bit integers, so the valid values for an unsigned integer are 0 - 4294967296 and the valid values for a signed integer are -2147483648 - 2147483647. Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum positive value is 2147483648. If my table already has a row with the maximum positive value in the primary key field, and I insert a row using NULL as the value of the primary key field, the row is inserted and the primary key is assigned the value of -2147483648. That makes sense to me and I have no problem with that. The problem is that the next row I insert generates the error "SQL error: PRIMARY KEY must be unique". I suspect that this is because SQLite tries to use the next largest positive value when it increments the primary key field. Is there an easy way to cause the INTEGER PRIMARY KEY column to use an unsigned integer instead, or to roll over to 0 instead of the most negative value for the data type? I suspect that in practice I will not run into this issue. However, I would feel better knowing that there is no chance that I will encounter this problem. Thanks, Shawn
Re: [sqlite] sqlite3_exec function error:database is locked
You need to handle the SQLITE_BUSY condition in your program. varunkumar wrote: is it possible more than one process can access the sqlite3 database at a time? I am using sqlite3 database , I have the situation like follows in my project. In my project one process(daemon) doing as follows it is opening the database using sqlite3_open() preparing sqlite3 insert statement using sqlite3_mprintf() for inserting values using sqlite3_exec() executing the sqlite3 insert statement . finally calling sqlite3_free(), sqlite3_close() API s The above steps are doing in my process(daemon) for every one minute. I am generating reports from different processes like opening the database usig sqlite3_open() preparing sqlite3 select statement using sqlite3_mprintf() using sqlite3_exec() executing the sqlite3 statement finally calling sqlite3_free(), sqlite3_close() APIS problem when i am generating reports at particular time ,at the same time if that daemon process access the database by calling sqlite3_exec() the daemon process getting the error like sqlite3_exec function error:database is locked. using sqlite3_step() API instead of sqlite3_exec() API verify the database whether database is busy or not using return value of SQLITE_BUSY. if SQLITE_BUSY i have to wait untill database is not busy. but i donot want to wait at that time because if i wait at that time our project would not synchronize with other processes, it should insert values at that time if any processes also using the database at the same time. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_exec function error:database is locked
is it possible more than one process can access the sqlite3 database at a time? I am using sqlite3 database , I have the situation like follows in my project. In my project one process(daemon) doing as follows it is opening the database using sqlite3_open() preparing sqlite3 insert statement using sqlite3_mprintf() for inserting values using sqlite3_exec() executing the sqlite3 insert statement . finally calling sqlite3_free(), sqlite3_close() API s The above steps are doing in my process(daemon) for every one minute. I am generating reports from different processes like opening the database usig sqlite3_open() preparing sqlite3 select statement using sqlite3_mprintf() using sqlite3_exec() executing the sqlite3 statement finally calling sqlite3_free(), sqlite3_close() APIS problem when i am generating reports at particular time ,at the same time if that daemon process access the database by calling sqlite3_exec() the daemon process getting the error like sqlite3_exec function error:database is locked. using sqlite3_step() API instead of sqlite3_exec() API verify the database whether database is busy or not using return value of SQLITE_BUSY. if SQLITE_BUSY i have to wait untill database is not busy. but i donot want to wait at that time because if i wait at that time our project would not synchronize with other processes, it should insert values at that time if any processes also using the database at the same time. -- View this message in context: http://www.nabble.com/sqlite3_exec-function-error%3Adatabase-is-locked-tf4612181.html#a13171346 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
