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, &errors );
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]
-----------------------------------------------------------------------------
