[sqlite] Re: Re: Re: Re: Re: SQLite3 Concurrency
John Stanton <[EMAIL PROTECTED]> wrote: More correctly: BEGIN Thread one BEGIN Thread two INSERT Thread one sets reserved lock INSERT Thread two, fails to set reserved lock SELECT Thread two, set shared lock COMMIT on thread one promotes reserved lock to pending SELECT Thread two fails to set shared lock Thread two already has its shared lock, left over from the first SELECT statement. While a transaction is in progress, locks it holds can only be promoted (that is, ever more and stricter locks acquired), never demoted. COMMIT on thread one continues, promoting pending lock to exclusive and commits journal, then releases lock INSERT Thread two, retries and gets reserved lock ... I am not clear on the mechanism of promoting a pending lock to exclusive. Does it block until all shared locks are cleared or does it return? I'm not sure either. Does an attampt to set a shared lock when a pending lock is set return a BUSY or block? Returns BUSY. In a threaded environment what we do is equivalent to a BEGIN IMMEDIATE except that it offers greater concurrency by permitting multiple concurrent read-only transactions: I seem to remember there was a discussion of introducing something like BEGIN SHARED to SQLite: a statement that attempts to start a transaction and immediately acquire a SHARED lock, somewhat similar to BEGIN IMMEDIATE. You can fake it programmatically, by issuing BEGIN and then a dummy SELECT statement (e.g. select 1 from sqlite_master limit 1; ). a. Read-Only Transaction - set pthread_rwlock to read BEGIN SQL COMMIT reset pthread-rwlock b. Transaction which modifies DB - set pthread_rwlock to write BEGIN SQL... COMMIT reset pthread_rwlock It looks like you can achieve the same effect by using BEGIN SHARED (or its programmatic equivalent) for readers, and BEGIN EXCLUSIVE for writers. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
Travor, absolutely correct. my apology for misleading everyone. Trevor Talbot wrote: You're confused about the locking; see http://sqlite.org/lockingv3.html On 8/26/07, John Stanton <[EMAIL PROTECTED]> wrote: How about the case of: BEGINsets reserved lock on thread one No lock. SELECT promotes lock to shared on thread one Thread one acquires SHARED lock. BEGIN sets reserved lock from thread two No lock. SELECT promotes reserved lock in thread two to shared Thread two acquires SHARED lock. ...at this point two threads are simultaneously processing SELECTs INSERT tries to promote shared lock to exclusive on thread one Thread one acquires RESERVED lock (upgrade from SHARED). but fails because second thread holds a shared lock Does not fail, as this is merely an intent to write and changes are currently buffered. My understanding is that once a reserved lock has been promoted to shared, a further reserved lock can be set. That opens the possibility that the concurrent transactions can conflict when they try to each set a write lock. All active readers have SHARED (reading) locks. A writer acquires a RESERVED (intent to write) lock, which blocks all future writers. Readers are not blocked. The writer buffers changes in memory. When a writer needs to commit (or spill) changes, it acquires a PENDING (need to write ASAP) lock while other SHARED locks exist. A PENDING lock blocks all future readers. When all SHARED locks are gone, it acquires an EXCLUSIVE (writing now) lock long enough to commit the changes. For two reading transactions that decide to write at the same time, one will fail with SQLITE_BUSY. The successful writer will not be able to commit until the failed writer ends the transaction. The failed writer will not be able to write until it ends the transaction and starts a new one. The successful writer may later encounter SQLITE_BUSY if it has to spill or commit changes while readers still exist. It is safe to retry under the assumption that the readers will eventually finish. However, the _first_ writing failure must not be retried due to the above, which will lead to deadlock. BEGIN IMMEDIATE replaces the first writing failure: if BEGIN IMMEDIATE succeeds, all writes that fail with SQLITE_BUSY (due to cache spill or commit) are safe to retry without deadlock, since only readers are present and they will eventually finish. If BEGIN EXCLUSIVE succeeds, there will never be SQLITE_BUSY failures. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Re: SQLite3 Concurrency
You are correct about the reserved lock. I looked back at my notes instead of using memory. It is only set when the database is about to have something written to it, and it stops further reserved locks from being set. A reserved lock is promoted to a pending lock which stops further shared locks being set. A pending lock can be promoted to exclusive when all shared locks are reset. When an exclusive lock is held it is safe to modify the database. The pending lock phase limits write starvation by forcing a gap in reads. BEGIN doesn't do any locking. BEGIN IMMEDIATE does. More correctly: BEGIN Thread one BEGIN Thread two INSERT Thread one sets reserved lock INSERT Thread two, fails to set reserved lock SELECT Thread two, set shared lock COMMIT on thread one promotes reserved lock to pending SELECT Thread two fails to set shared lock COMMIT on thread one continues, promoting pending lock to exclusive and commits journal, then releases lock INSERT Thread two, retries and gets reserved lock ... I am not clear on the mechanism of promoting a pending lock to exclusive. Does it block until all shared locks are cleared or does it return? Does an attampt to set a shared lock when a pending lock is set return a BUSY or block? Alternatively if maximum concurrency is not required BEGIN IMMEDIATE Sets write lock on thread one BEGIN IMMEDIATE Fails to set write lock on thread two SQL on thread one runs with a chance of a BUSY COMMIT Thread one promotes commits journal and releases lock BEGIN IMMEDIATE On thread two now retries and gets write lock In a threaded environment what we do is equivalent to a BEGIN IMMEDIATE except that it offers greater concurrency by permitting multiple concurrent read-only transactions: a. Read-Only Transaction - set pthread_rwlock to read BEGIN SQL COMMIT reset pthread-rwlock b. Transaction which modifies DB - set pthread_rwlock to write BEGIN SQL... COMMIT reset pthread_rwlock A dummy fcntl avoids Sqlite from needlessly mirroring the locking. In an intense traffic environment some extra logic to provide mandatory write cycles might be necessary, but we do not at this stage use Sqlite in such applications. My apologies for being careless and not checking the facts initially. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: How about the case of: BEGINsets reserved lock on thread one You mean BEGIN IMMEDIATE, right? SELECT promotes lock to shared on thread one I'm not sure what you mean by "promotes" here. If anything, RESERVED lock is a superset of SHARED, not the other way round. SELECT statement most definitely does not cause a transaction that started with BEGIN IMMEDIATE to release its RESERVED lock. BEGIN sets reserved lock from thread two It can't. Thread one already holds a RESERVED lock. Only one thread can acquire such. SELECT promotes reserved lock in thread two to shared Wrong. See above. INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock This statement doesn't make any sense to me, sorry. It bears no relationship to reality, so I don't even know where to begin to disprove it. My understanding is that once a reserved lock has been promoted to shared A reserved lock is never "promoted" to shared. Whatever gave you this idea? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
You're confused about the locking; see http://sqlite.org/lockingv3.html On 8/26/07, John Stanton <[EMAIL PROTECTED]> wrote: > How about the case of: > BEGINsets reserved lock on thread one No lock. > SELECT promotes lock to shared on thread one Thread one acquires SHARED lock. > BEGIN sets reserved lock from thread two No lock. > SELECT promotes reserved lock in thread two to shared Thread two acquires SHARED lock. > ...at this point two threads are simultaneously processing SELECTs > INSERT tries to promote shared lock to exclusive on thread one Thread one acquires RESERVED lock (upgrade from SHARED). > but fails because second thread holds a shared lock Does not fail, as this is merely an intent to write and changes are currently buffered. > My understanding is that once a reserved lock has been promoted to > shared, a further reserved lock can be set. That opens the possibility > that the concurrent transactions can conflict when they try to each set > a write lock. All active readers have SHARED (reading) locks. A writer acquires a RESERVED (intent to write) lock, which blocks all future writers. Readers are not blocked. The writer buffers changes in memory. When a writer needs to commit (or spill) changes, it acquires a PENDING (need to write ASAP) lock while other SHARED locks exist. A PENDING lock blocks all future readers. When all SHARED locks are gone, it acquires an EXCLUSIVE (writing now) lock long enough to commit the changes. For two reading transactions that decide to write at the same time, one will fail with SQLITE_BUSY. The successful writer will not be able to commit until the failed writer ends the transaction. The failed writer will not be able to write until it ends the transaction and starts a new one. The successful writer may later encounter SQLITE_BUSY if it has to spill or commit changes while readers still exist. It is safe to retry under the assumption that the readers will eventually finish. However, the _first_ writing failure must not be retried due to the above, which will lead to deadlock. BEGIN IMMEDIATE replaces the first writing failure: if BEGIN IMMEDIATE succeeds, all writes that fail with SQLITE_BUSY (due to cache spill or commit) are safe to retry without deadlock, since only readers are present and they will eventually finish. If BEGIN EXCLUSIVE succeeds, there will never be SQLITE_BUSY failures. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Re: SQLite3 Concurrency
John Stanton <[EMAIL PROTECTED]> wrote: How about the case of: BEGINsets reserved lock on thread one You mean BEGIN IMMEDIATE, right? SELECT promotes lock to shared on thread one I'm not sure what you mean by "promotes" here. If anything, RESERVED lock is a superset of SHARED, not the other way round. SELECT statement most definitely does not cause a transaction that started with BEGIN IMMEDIATE to release its RESERVED lock. BEGIN sets reserved lock from thread two It can't. Thread one already holds a RESERVED lock. Only one thread can acquire such. SELECT promotes reserved lock in thread two to shared Wrong. See above. INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock This statement doesn't make any sense to me, sorry. It bears no relationship to reality, so I don't even know where to begin to disprove it. My understanding is that once a reserved lock has been promoted to shared A reserved lock is never "promoted" to shared. Whatever gave you this idea? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
Based on the description quoted by Igor-2 the situation below would not occur because the reserved lock is not set until the INSERT is intercepted. Since there can be only one reserved lock current, a busy can occur at that point. As I recall that logic counteracts write starvation by stopping shared locks being set when a write is pending. John Stanton wrote: How about the case of: BEGINsets reserved lock on thread one SELECT promotes lock to shared on thread one BEGIN sets reserved lock from thread two SELECT promotes reserved lock in thread two to shared ...at this point two threads are simultaneously processing SELECTs INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock My understanding is that once a reserved lock has been promoted to shared, a further reserved lock can be set. That opens the possibility that the concurrent transactions can conflict when they try to each set a write lock. The strategy which increases concurrency does have consequences. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: Igor, I confused the issue with names. The correct name is a "reserved" lock, created when a simple transaction is launched in "deferred" mode. Here is the Sqlite explanation. A BEGIN IMMEDIATE or EXCLUSIVE will acquire a write lock immediately. Yes. And that may fail with SQLITE_BUSY. But once it succeeds, subsequent SELECT statements will always succeed, too. How does your observation help prove the point that every single sqlite3_step call may return SQLITE_BUSY? This is the only claim of yours I'm arguing against. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite3 Concurrency
Igor Mironchick wrote:
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to
better concurrency, but does have other effects requiring that
provision be made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN
EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
Your BEGIN tries to set a RESERVED lock, but only one RESERVED lock is
permitted so your BEGIN may fail.
Not true.
BTW:
"The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is
optional) is used to take SQLite out of autocommit mode. Note that the
BEGIN command does not acquire any locks on the database. After a BEGIN
command, a SHARED lock will be acquired when the first SELECT statement
is executed. A RESERVED lock will be acquired when the first INSERT,
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired
until either the memory cache fills up and must be spilled to disk or
until the transaction commits. In this way, the system delays blocking
read access to the file file until the last possible moment."
BTW, I suggest that you do not use sqlite3_exec on new programs and
use sqlite3_prepare and sqlite3_step. It is the preferred interface.
Sqlite3_exec is deprecated and exists for legacy applications.
sqlite3_exec not deprecated but preferable to use. But I don't see any
advantages in PREPARE/STEP when SQL statement so simple and will not
used anymore with another variabled params...
That description would indicate that you have to have a busy check on
each step.
As for sqlite3_exec. It was superceded for a good reason, and if you
get to use Sqlite more you will appreciate the reason.
USing sqlite3_prepare has the advantage that you can precompile all your
SQL and don't start processing until you have all your statements
correct and matching the existing schema.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
How about the case of: BEGINsets reserved lock on thread one SELECT promotes lock to shared on thread one BEGIN sets reserved lock from thread two SELECT promotes reserved lock in thread two to shared ...at this point two threads are simultaneously processing SELECTs INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock My understanding is that once a reserved lock has been promoted to shared, a further reserved lock can be set. That opens the possibility that the concurrent transactions can conflict when they try to each set a write lock. The strategy which increases concurrency does have consequences. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: Igor, I confused the issue with names. The correct name is a "reserved" lock, created when a simple transaction is launched in "deferred" mode. Here is the Sqlite explanation. A BEGIN IMMEDIATE or EXCLUSIVE will acquire a write lock immediately. Yes. And that may fail with SQLITE_BUSY. But once it succeeds, subsequent SELECT statements will always succeed, too. How does your observation help prove the point that every single sqlite3_step call may return SQLITE_BUSY? This is the only claim of yours I'm arguing against. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: SQLite3 Concurrency
Igor Mironchick <[EMAIL PROTECTED]>
wrote:
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
Why do you want to keep running the same statement over and over?
Since I guarante that there is no errors in SQL syntax something when
ret != SQLITE_OK mean that there is concurrency problem and I need to
wait unloking database.
But you will never get ret != SQLITE_OK, except perhaps when executing
the satement for the very first time. Once the first SELECT succeeds,
your transaction has acquired a SHARED lock and will hold on to it until
it ends. This, in turn, prevents any prospective writers from being able
to modify the database.
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
I'm not sure I understand what your point is here. You have a reader
that hogs the database forever in a long-running transaction. Of
course writers are locked out.
No. Reader will not hog database because if in this example ret !=
SQLITE_OK
This condition will never become true, barring catastrophic I/O failure
(e.g. bad disk sector).
then writer have already lock database for write
A writer cannot obtain its lock until all readers release theirs. SQLite
doesn't preempt readers to let writers through. Your reader however
never releases its lock once acquired.
and we need
to wait for unlocking. And when I wrote: "More dangerous when "COMMIT"
after "INSERT" return SQLITE_BUSY" I mean that if this wouldn't be
checked then probably we lost ours data.
Yes, this is possible, and this is precisely what would happen in your
scenario. The reader would never be preempted, and would continue to
loop forever. The writer will get SQLITE_BUSY either on INSERT
statement, or on COMMIT statement.
As for losing data, I'm not sure how this situation is different from
that occuring in any other DBMS. Your data is only guaranteed to be in
the database once the transaction has been committed successfully. A
transaction may fail at any point prior to that, and be rolled back,
resulting in the data not making it into the database.
For example, MS SQL Server supports table and row-level locking, so in
many cases it would let multiple readers and writers into the database
at the same time. But occasionally, this may result in a deadlock (two
transactions each trying to modify rows the other has locked). When this
happens, one of these transactions is chosen as a deadlock victim and is
rolled back to allow the other to proceed. So you may have a transaction
rolling back on you literally at any moment.
P.S. There is no reasons for two threads lock the same database and
will run while cycle at the same time.
I don't understand this statement. Be aware that SQLite does allow
multiple readers to lock the database at the same time, each holding a
SHARED lock. What's not allowed is a reader and a writer, or two
writers, at the same time.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite3 Concurrency
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to
better concurrency, but does have other effects requiring that
provision be made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN
EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
Your BEGIN tries to set a RESERVED lock, but only one RESERVED lock is
permitted so your BEGIN may fail.
Not true.
BTW:
"The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is
optional) is used to take SQLite out of autocommit mode. Note that the
BEGIN command does not acquire any locks on the database. After a BEGIN
command, a SHARED lock will be acquired when the first SELECT statement
is executed. A RESERVED lock will be acquired when the first INSERT,
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired
until either the memory cache fills up and must be spilled to disk or
until the transaction commits. In this way, the system delays blocking
read access to the file file until the last possible moment."
BTW, I suggest that you do not use sqlite3_exec on new programs and
use sqlite3_prepare and sqlite3_step. It is the preferred interface.
Sqlite3_exec is deprecated and exists for legacy applications.
sqlite3_exec not deprecated but preferable to use. But I don't see any
advantages in PREPARE/STEP when SQL statement so simple and will not
used anymore with another variabled params...
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Re: Re: SQLite3 Concurrency
John Stanton <[EMAIL PROTECTED]> wrote: Igor, I confused the issue with names. The correct name is a "reserved" lock, created when a simple transaction is launched in "deferred" mode. Here is the Sqlite explanation. A BEGIN IMMEDIATE or EXCLUSIVE will acquire a write lock immediately. Yes. And that may fail with SQLITE_BUSY. But once it succeeds, subsequent SELECT statements will always succeed, too. How does your observation help prove the point that every single sqlite3_step call may return SQLITE_BUSY? This is the only claim of yours I'm arguing against. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite3 Concurrency
Igor Mironchick wrote:
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to better
concurrency, but does have other effects requiring that provision be
made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement: "
<< errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
Your BEGIN tries to set a RESERVED lock, but only one RESERVED lock is
permitted so your BEGIN may fail.
BTW, I suggest that you do not use sqlite3_exec on new programs and use
sqlite3_prepare and sqlite3_step. It is the preferred interface.
Sqlite3_exec is deprecated and exists for legacy applications.
See here for a locking description:
http://www.sqlite.org/lockingv3.html
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: SQLite3 Concurrency
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
What's the point of running a SELECT statement without actually
reading the returned rows?
It's simple example, not real code. Only show strategy.
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
Why do you want to keep running the same statement over and over?
Since I guarante that there is no errors in SQL syntax something when
ret != SQLITE_OK mean that there is concurrency problem and I need to
wait unloking database. For that reason I keep the same statement over
and over.
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
I'm not sure I understand what your point is here. You have a reader
that hogs the database forever in a long-running transaction. Of
course writers are locked out.
No. Reader will not hog database because if in this example ret !=
SQLITE_OK then writer have already lock database for write and we need
to wait for unlocking. And when I wrote: "More dangerous when "COMMIT"
after "INSERT" return SQLITE_BUSY" I mean that if this wouldn't be
checked then probably we lost ours data.
P.S. There is no reasons for two threads lock the same database and will
run while cycle at the same time.
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Re: SQLite3 Concurrency
Igor Mironchick <[EMAIL PROTECTED]>
wrote:
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
What's the point of running a SELECT statement without actually reading
the returned rows?
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement: "
<< errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
Why do you want to keep running the same statement over and over?
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
I'm not sure I understand what your point is here. You have a reader
that hogs the database forever in a long-running transaction. Of course
writers are locked out.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite3 Concurrency
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to better
concurrency, but does have other effects requiring that provision be
made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement: "
<< errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Re: SQLite3 Concurrency
John Stanton <[EMAIL PROTECTED]> wrote: How about the case of a simple BEGIN which sets a deferred lock so that the busy will occur when that lock is promoted later in the the transaction? I'm not sure I understand your question. http://sqlite.org/lockingv3.html says that a simple BEGIN statement doesn't acquire any locks at all: "Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed." Are you perhaps thinking of BEGIN IMMEDIATE or BEGIN EXCLUSIVE? What exactly do you mean by "deferred lock"? As I understand it the deferred lock capability is conducive to better concurrency, but does have other effects requiring that provision be made to intercept a BUSY in the body of the transaction. But not between two sqlite3_step's for the same SELECT statement, which is what you appear to have claimed. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite3 Concurrency
How about the case of a simple BEGIN which sets a deferred lock so that the busy will occur when that lock is promoted later in the the transaction? As I understand it the deferred lock capability is conducive to better concurrency, but does have other effects requiring that provision be made to intercept a BUSY in the body of the transaction. Forcing the BEGIN to be exclusive would certainly mean that only the BEGIN step could return a BUSY. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: You need to be prepared to get a busy status after each Sqlite3_step. Not really. Only the very first sqlite3_step after a prepare or reset can get SQLITE_BUSY. If it succeeds, the connection has acquired a SHARED lock and subsequent steps are guaranteed not to run into concurrency problems. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite3 Concurrency
John Stanton <[EMAIL PROTECTED]> wrote: You need to be prepared to get a busy status after each Sqlite3_step. Not really. Only the very first sqlite3_step after a prepare or reset can get SQLITE_BUSY. If it succeeds, the connection has acquired a SHARED lock and subsequent steps are guaranteed not to run into concurrency problems. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite3 Concurrency
You need to be prepared to get a busy status after each Sqlite3_step. If you launch a sqlite3_step while a transaction which is executing an INSERT or UPDATE is current, you will get an SQLITE_BUSY. If you get a BUSY, try again until the condition clears. Igor Mironchick wrote: Hi, guys. I have some questions about multithreading in SQLite: a) If one thread "BEGIN" and after that another thread try to "BEGIN". What will with the second thread? How I understand there is nothing wrong. Is it right? b) The same one only when second thread "BEGIN" after first thread "BEGIN" and "INSERT" but before "COMMIT"? Here, how I understand, second thread will get "SQLITE_BUSY" and have nothing to read?!?! P.S. First thread is a writer and a reader and the second one is a reader only. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite3 Concurrency
Igor Mironchick <[EMAIL PROTECTED]> wrote: a) If one thread "BEGIN" and after that another thread try to "BEGIN". What will with the second thread? How I understand there is nothing wrong. Is it right? No problem. It's OK for two or more threads to issue BEGIN statement. Such a statement doesn't actually touch the database at all, just sets some flag in the connection structure. b) The same one only when second thread "BEGIN" after first thread "BEGIN" and "INSERT" but before "COMMIT"? Here, how I understand, second thread will get "SQLITE_BUSY" and have nothing to read?!?! BEGIN statement never causes SQLITE_BUSY. As I said, it doesn't touch the database file, doesn't in itself try to acquire any locks, not even SHARED. Now, when the second thread attempts to read (by issuing SELECT statement), it may or may not get SQLITE_BUSY. This depends on whether the first thread holds a RESERVED lock, or a PENDING or EXCLUSIVE lock. That, in turn, depends on whether the first thread still keeps its pending modifications in an in-memory cache, or has spilled (or is just about to spill) them into the database file on disk. For more details, see http://sqlite.org/lockingv3.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite3 Concurrency
Hi, guys. I have some questions about multithreading in SQLite: a) If one thread "BEGIN" and after that another thread try to "BEGIN". What will with the second thread? How I understand there is nothing wrong. Is it right? b) The same one only when second thread "BEGIN" after first thread "BEGIN" and "INSERT" but before "COMMIT"? Here, how I understand, second thread will get "SQLITE_BUSY" and have nothing to read?!?! P.S. First thread is a writer and a reader and the second one is a reader only. -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: like operator
Maybe you should look at using FTS to get indexed text searching. RaghavendraK 70574 wrote: sorry. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Igor Tandetnik <[EMAIL PROTECTED]> Date: Sunday, August 26, 2007 7:00 pm Subject: [sqlite] Re: Re: like operator RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Will Sqlite uses indexes when using the like operator as below, select * from tbl where '9845' like column || '%' order by des limit 1; No. Next time you have a similar question, be aware you can find out yourself by prepending your query with EXPLAIN QUERY PLAN. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing monetary values and calculations
That is an interesting way to store money. We developed a fixed point arithmetic library of arbitrary precision using the algorithms described by Knuth in his semi-numerical algorithms volume and using standard DECIMAL(n,m) definition. Rounding is precise using an algorithm which does not drift and intermediate. Finally the numbers are stored in display format, right justified and with leading spaces and signs so that they can be displayed in HTML pages or printed output without reformating. The arithmetic is obviously not suited to intensive calculation but is well suited to money usage where the absence of radix changes and edit conversions more than compensates for the arithmetic overhead, helped by using effcient arithmetic algorithms. Another advantage is that it makes inserting numbers in report tables absolutely trivial. They automatically line up in right justified columns. It was a fairly simple exercise to graft this number system into Sqlite, really only requiring that the regular behaviour of giving a DECIMAL type numeric affiliation be altered to text affiliation to stop Sqlite from translating the formatted, fixed point numbers to floating point. RohitPatel wrote: While doing currency math, a useful money class at following link, may be used as a a reference. http://www.di-mare.com/adolfo/p/money.htm Yet Another C++ Money Class (by Adolfo Di Mare), The C Users Journal, Vol.10 No.4, pp [58-64], April 1992 Rohit. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: like operator
sorry. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Igor Tandetnik <[EMAIL PROTECTED]> Date: Sunday, August 26, 2007 7:00 pm Subject: [sqlite] Re: Re: like operator > RaghavendraK 70574 > <[EMAIL PROTECTED]> wrote: > >> Will Sqlite uses indexes when > >> using the like operator > >> as below, > >> > >> select * from tbl where '9845' like column || '%' > >> order by des limit 1; > > No. Next time you have a similar question, be aware you can find > out > yourself by prepending your query with EXPLAIN QUERY PLAN. > > Igor Tandetnik > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: like operator
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Will Sqlite uses indexes when using the like operator as below, select * from tbl where '9845' like column || '%' order by des limit 1; No. Next time you have a similar question, be aware you can find out yourself by prepending your query with EXPLAIN QUERY PLAN. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: like operator
Hi,
Will Sqlite uses indexes when
using the like operator
as below,
select * from tbl where '9845' like column || '%'
order by des limit 1;
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Friday, August 17, 2007 7:25 am
Subject: [sqlite] Re: like operator
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > we have given a web interface which receive delete request.
> > Now in the req we get "%" and in the delete impl we do this
> > delete from table where itemName like xxx.%;
> >
> > since the key is % the above statement becomes,
> > "delete from table where itemName like %.%";And result in fatal
> > problem of erasing all records.
>
> Try
>
> delete from table
> where itemName >= 'xxx.' and itemName < 'xxx/';
>
> (a slash '/' character happens to come after period '.' in ASCII). Or
>
> delete from table
> where substr(itemName, 1, length('xxx.')) = 'xxx.';
>
> The first query would run much faster than the second if you have
> an
> index on itemName.
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Looking for a cryptographic library
Please look at the following link, for few easy-to-use free simple code-packages (two or three) for Encryption and Decryption. http://www.efgh.com/software/ Rohit -- View this message in context: http://www.nabble.com/Looking-for-a-cryptographic-library-tf4298572.html#a12334506 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Are parenthesis really needed?
Hi, On 8/25/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > I assume you're referring to this: > > http://marc.info/?l=sqlite-users=118737502703454=2 > Yep, that's it. > In that specific case, the parens are not needed. But that's not always > the case. In general, LEFT OUTER JOIN is not commutative, nor is it > associative. You cannot blindy strip the parens or reorder the outer joins > out of context. > hmmm I see. Thanks for you attention, Joe. Regards, Bruno -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação - UFLA * Mestrando em Inteligência Computacional - UFPR * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] Storing monetary values and calculations
While doing currency math, a useful money class at following link, may be used as a a reference. http://www.di-mare.com/adolfo/p/money.htm Yet Another C++ Money Class (by Adolfo Di Mare), The C Users Journal, Vol.10 No.4, pp [58-64], April 1992 Rohit. -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12333186 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
