Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 09:35:21AM -0500, Pavel Ivanov scratched on the wall: > But as I see in SQLite sources sqlite3_exec does acquire > connection's mutex, so nothing can be executed in between statements. > Thus if you execute this line as one call to sqlite3_exec then it > won't suffer from threading. Humm... I missed that. I knew that _exec() doesn't actually open a proper transaction (since it has no idea what the transaction state might be), but I didn't catch that it still grabs the mutex to prevent issues with threads. Clever. More teo the point, that means a single _exec() call with the INSERT, followed by a call to last_insert_rowid() should always do the right thing. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 04:08:16PM +0300, Ruslan Mullakhmetov scratched on the wall: > i got following contradictory replies to my question > > > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > > from threads? > > from borgan: > > Hi, i think this will probably be "atomic". > > What i mean is the transaction will aquire EXCLUSIVE lock (because of > > the insert command) and it will keep this lock until commit, which > > takes place after the last select and last semicolon. > > > > So i believe no other threads should be able to do anything with your > > db until the whole query finishes. > and from Kees Nuyt: > > The last_insert_rowid() function returns the ROWID of the last row > > insert from the database connection which invoked the function. > > So, yes, it suffers from threads if the threads use the same > > database connection. > > whom to trust? The docs, which make this fairly clear: http://sqlite.org/lang_corefunc.html#last_insert_rowid The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function. http://sqlite.org/c3ref/last_insert_rowid.html [...] This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned. [...] So Kees Nuyt is correct... the value returned by last_insert_rowid() is "per database connection". It is not a global. It is not a per-database-file value. It is not a pre-thread value. If each thread is using its own, private database connection, then you're safe. If multiple threads are sharing a database connection and using the "serialized" thread mode, then you have a race condition as it is possible for the database connection to be handed off between statements. The value returned by last_insert_rowid() may be invalid by the time it is called. In either case, these two operations are never "atomic", in the fullest sense of that word. last_insert_rowid() is not bounded by transactions in the way statement processing is. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? Using sqlite3_exec() changes nothing. It just runs the normal prepare/step interfaces. It does not provide any kind of atomic processing above and beyond autocommit. It doesn't even wrap multiple statements in a transaction. It is just a syntax short-cut and provides no significant functionality beyond convenience. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 11:38 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > Understand this: Any programming language that is built around threads > is > > inherently broken and should be avoided. (I will not name names - you > know > > the languages I'm talking about.) And any operating system that depends > > upon threads for performance is equality busted and should also be > shunned. > > so you advise to change language? to some functional or logic? i got no > more > idea how to avoid threads and evils connected with them. > > unfortunately i'm not free in language choosing cause i develop extensions > which has api in language that "is inherently broken and should be > avoided". > I recognize this sad reality, which is why SQLite is "threadsafe" even though we actively discourage the use of threads. Sometimes you just have no choice. Unchangeable external constraints impose a programming language or platform on you. And for those occasions, you just have to VERY VERY CAREFUL. > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
> Understand this: Any programming language that is built around threads is > inherently broken and should be avoided. (I will not name names - you know > the languages I'm talking about.) And any operating system that depends > upon threads for performance is equality busted and should also be shunned. so you advise to change language? to some functional or logic? i got no more idea how to avoid threads and evils connected with them. unfortunately i'm not free in language choosing cause i develop extensions which has api in language that "is inherently broken and should be avoided". > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 10:29 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > as you see in _serialized_ mode "SQLite can be safely used by multiple > threads with no restriction". > > do i misunderstand something? > With serialized threading, SQLite is perfectly "safe" to use. But if you change a value (the last-insert-rowid in this case) then that change will appear in other threads. There is nothing "unsafe" about that - you just need to be aware that it happens. "Safe" does not mean "atomic". If nothing else, this entire discussion should make it abundantly clear that threads are a very dangerous construct and should be actively avoided in all software that strives to be robust and/or reliable. People rant and rail about the evils of "goto" or pointers, and to be fair, those constructs are easily misused. But threads are a far more insidious evil in that they are non-deterministic: the failures happen differently on each run, or perhaps not at all until the product goes into production. Understand this: Any programming language that is built around threads is inherently broken and should be avoided. (I will not name names - you know the languages I'm talking about.) And any operating system that depends upon threads for performance is equality busted and should also be shunned. "Threadsafe" is an oxymoron. Nothing is safe when threads are in play. There are merely differing levels of hazard. So the best solution to your problem, Ruslan, is to not use threads. Failing that, allocate a separate database connection for each thread. If you can't do that, then you are going to have concurrency issues to deal with, regardless of what libraries you use. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
thanks everybody, especially Pavel Ivanov who did not give himself a trouble to look at the source though i did. but as far as i understand this is only applicable to current version of sqlite and future releases may change things. Jay A. Kreibich wrote: > If each thread is using its own, private database connection, then > you're safe. If multiple threads are sharing a database connection > and using the "serialized" thread mode, then you have a race > condition as it is possible for the database connection to be > handed off between statements. The value returned by > last_insert_rowid() may be invalid by the time it is called. Yes, i share one database handle between multiple threads. actually i have some class Storage which is accessible from multiple threads. I defined SQLITE_TREADSAFE and thought that there could be no racing. as for http://cvs.hwaci.com/threadsafe.html > SQLite support three different threading modes: > > 1. Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once. > 2. Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. > 3. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction. as you see in _serialized_ mode "SQLite can be safely used by multiple threads with no restriction". do i misunderstand something? BR, RM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
> whom to trust? Trust Kees. Borgan's thought about keeping the lock and transaction management inside sqlite3_exec is incorrect. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? Actually it depends on particular implementation of sqlite3_exec. Kees' reply is general and it would stand if sqlite3_exec didn't acquire connection's mutex and let individual statements acquire mutex as necessary. But as I see in SQLite sources sqlite3_exec does acquire connection's mutex, so nothing can be executed in between statements. Thus if you execute this line as one call to sqlite3_exec then it won't suffer from threading. Pavel On Tue, Nov 16, 2010 at 8:08 AM, Ruslan Mullakhmetov wrote: > i got following contradictory replies to my question > >> if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select >> last_insert_rowid() as li;" would be it atomic? or it anyway would suffer >> from threads? > > from borgan: >> Hi, i think this will probably be "atomic". >> What i mean is the transaction will aquire EXCLUSIVE lock (because of >> the insert command) and it will keep this lock until commit, which >> takes place after the last select and last semicolon. >> >> So i believe no other threads should be able to do anything with your >> db until the whole query finishes. > and from Kees Nuyt: >> The last_insert_rowid() function returns the ROWID of the last row >> insert from the database connection which invoked the function. >> So, yes, it suffers from threads if the threads use the same >> database connection. > > whom to trust? > > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? > > BR, RM > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
i got following contradictory replies to my question > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > from threads? from borgan: > Hi, i think this will probably be "atomic". > What i mean is the transaction will aquire EXCLUSIVE lock (because of > the insert command) and it will keep this lock until commit, which > takes place after the last select and last semicolon. > > So i believe no other threads should be able to do anything with your > db until the whole query finishes. and from Kees Nuyt: > The last_insert_rowid() function returns the ROWID of the last row > insert from the database connection which invoked the function. > So, yes, it suffers from threads if the threads use the same > database connection. whom to trust? as for Kees Nuyt reply, did you toke int account that "select last_insert_rowid() " and insert query combined in single query and executed via single call of sqlite3_exec()? BR, RM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 11/15/2010 04:15 AM, Ruslan Mullakhmetov wrote:
> i need to get last insert id in multithreaded app.
> I use C API, actually some C++ wrap over it.
> if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select
> last_insert_rowid() as li;" would be it atomic? or it anyway would suffer
> from threads?
As others mentioned the value returned is for the database handle. There
are two ways you can address this. One is to make each thread use its own
completely separate database handle. Do some benchmarking if you think it
will affect overall performance of your program (unlikely) and you can do
some tuning such as shared cache mode.
The other thing is to use a transaction. I do queries like this:
begin;
insert into tbl( filed ) VALUES ( 1 );
Select last_insert_rowid() as li;
commit;
Another issue you need to be careful with when threading is errors as the
SQLite error stuff is not thread safe. SQLite records the error message
pointer against the database handle and the error API returns this pointer.
Another thread could cause different error code/message by the time you
get their values. Also the pointer the API returns could become invalid by
the time you use it due to other thread activity, with the worst case being
that you crash if it now points to unmapped memory.
You need to structure your calls to SQLite APIs like this:
sqlite3_mutex_enter(sqlite3_db_mutex(db));
rc=sqlite3_...;
if (rc!=SQLITE && rc!=SQLITE_DONE && rc!=SQLITE_ROW)
{
copy sqlite3_errmsg(db) into a thread local buffer;
copy code as well if you don't use rc;
}
sqlite3_mutex_leave(sqlite3_db_mutex(db));
On first discovering this issue several years ago I did campaign for the
SQLite semantics be changed so that the error code and string become thread
local just like errno and GetLastError are, but DRH didn't want to do this
because there *may* be code out using SQLite that does calls in one thread
and retrieves error information in a different one.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzhWQYACgkQmOOfHg372QSn+QCcC+PdUdjCrfny6fucvY5q1D8W
FIUAn1vAgsVDw+NESBbwsA8ST9mFIzVC
=a1CE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Mon, 15 Nov 2010 15:15:10 +0300, Ruslan Mullakhmetov wrote: >hi. > >i need to get last insert id in multithreaded app. >I use C API, actually some C++ wrap over it. >if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select >last_insert_rowid() as li;" would be it atomic? or it anyway would suffer >from threads? The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. So, yes, it suffers from threads if the threads use the same database connection. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
Hi, i think this will probably be "atomic". What i mean is the transaction will aquire EXCLUSIVE lock (because of the insert command) and it will keep this lock until commit, which takes place after the last select and last semicolon. So i believe no other threads should be able to do anything with your db until the whole query finishes. bogdan > hi. > > i need to get last insert id in multithreaded app. > I use C API, actually some C++ wrap over it. > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > from threads? > > -- > Ruslan Mullakhmetov > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] threads and last_insert_rowid()
hi. i need to get last insert id in multithreaded app. I use C API, actually some C++ wrap over it. if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select last_insert_rowid() as li;" would be it atomic? or it anyway would suffer from threads? -- Ruslan Mullakhmetov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
