Re: [sqlite] Getting last inserted rowid?

[John Elrick]

SNIP
>> processed by a complied virtual machine.  Threads switch at the
>> machine code level, so a thread could switch between the processing
>> of the 'g' and the 'i' of the first 'begin'.
>> 
>
> SQLite actually maintains a mutex per connection. Every API call 
> acquires this mutext and keeps it for the duration of the call. Thus no 
> two calls can proceed simultaneously on the same connection.
>
> So no, another thread cannot interfere while sqlite3_prepare call 
> advances from 'g' to 'i'.
>   

I stand corrected.


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

Last try...

On Sat, Jun 6, 2009 at 1:30 AM, Nikolaus Rath wrote:
> John Machin  writes:
>> On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
>>> John Machin  writes:
> Now I'm confused. I want to know if it will be sufficient to wrap my
> last_insert_rowid() call between BEGIN .. and END in order to make it
> return the rowid that was last inserted by the same thread even if
> multiple threads are using the same connection (but different cursors).
>
> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
> saying that it's not sufficient, I need to use additional mechanism.

Forget all I and Igor said...

You have 2 choices to have consistent last_insert_rowid() after an
INSERT using threads sharing the same connection handle:

1) Wrap the "INSERT..." ==> "last_insert_rowid()" inside a BEGIN/END
transaction.

Advantages:
- No need for mutexes or other synchronization primitive
- Other threads that don't use transactions can still read from the
database if they don't require the last_insert_rowid() value (this can
be of no great importance as they will still find the database busy
while doing the insert, which is probably the big majority of the time
of the operation)

Disadvantages:
- You need to check for the nested transaction error and retry
- Implies 2 more automatic mutex locks done by the sqlite library for
the BEGIN and END (probably negligible time on most cases but maybe
important when dealing with a lot of threads)

2) Wrap  a mutex or other synchronization primitive around the
"INSERT" ==> "last_insert_rowid()" calls.

Advantages:
- 1 less mutex held (although this one has a longer duration)
- No need to check for a specific error condition. Other threads just
wait until they can proceed.

Disadvantages:
- Other threads are locked for the full duration of the event (as in
the point 1, probably also not that important, as the insert will make
most of the time)
- You need to share the mutex to all threads involved and removes some
flexibility for the algorithm design.


The pros/cons relating to the mutexes held can very well be inverted
depending on the specific code being run.
If you don't use the same connection handle, then all this is moot, as
all synchronization is done at the file level.
I didn't try to be exhaustive. That is your job.

Now, if you still have doubts after this I don't think I can be of any
help, as that makes it obvious my communication skills are lacking.


Regards,
~Nuno Lucas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

John Machin  writes:
> On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
>> John Machin  writes:
 Now I'm confused. I want to know if it will be sufficient to wrap my
 last_insert_rowid() call between BEGIN .. and END in order to make it
 return the rowid that was last inserted by the same thread even if
 multiple threads are using the same connection (but different cursors).

 As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
 saying that it's not sufficient, I need to use additional mechanism.
>>> As Igor pointed out, if you have multiple threads using the same 
>>> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
>>> If you don't have that, yes you need to "use additional mechanism" BUT 
>>> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
>>> added a rider like "(presuming your existing code is not stuffed)".
>> 
>> Are you saying that I need to use mutexes or whatever in the following
>> program?
>> 
>> def thread1():
>> cur = conn.cursor()
>> for i in range(500):
>> cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)
>> 
>> def thread2():
>> cur = conn.cursor()
>> for i in range(500):
>> cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)
>> 
>> threading.Thread(target=thread1).start()
>> threading.Thread(target=thread2).start()
>
> Somebody needs to use mutexes somewhere. You have obscured the question 
> by introducing two unknowns: (1) Which Python wrapper are you using 
> (sqlite3 module or the apsw module)? (2) What does it do under the 
> covers? Try asking the relevant guru for whatever you are using.

It's made for apsw and it shouldn't do anything under the hood. Consider
the same example in C if it helps.

 Where am I wrong?
>>> In wasting time on semantic confusion instead of implementing it and 
>>> testing the bejaysus out of it.
>> 
>> When you are working with a multithreaded program, you can't even hope
>> to test a fraction of the possible state trajectories. Finding the
>> proper implementation by trial & error is therefore even more hopeless
>> than in a single threaded program.
>
> If you can't test it, then how will you know whether *any* 
> implementation is "proper", let alone *the* "proper" one?

Of course you have to test once you have defined proper behavior. But my
question in this thread is essentially what the "proper" behavior is.

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath  wrote:
>>> Is BEGIN ... COMMIT sufficient for that?
>>
>> No. Transaction is also maintained per connection. Starting a
>> transaction would prevent other connections from making concurrent
>> changes, but wouldn't block other threads using the same connection.
>
> Actually it seems to do exactly that:
>
>>
>> def thread1():
>> cur = conn.cursor()
>> cur.execute("BEGIN")
>> print "Thread 1 in transaction"
>> time.sleep(5)
>> print "Thread 1 finished."
>>
>> def thread2():
>> cur = conn.cursor()
>> time.sleep(2)
>> cur.execute("BEGIN")
>> print "Thread 2 in transaction"
>> time.sleep(5)
>> print "Thread 2 finished."

What you see is the effect that Nuno Lucas pointed out: if you try to 
execute BEGIN while there's an open transaction, you'll get an error. It 
doesn't matter whether the nested BEGIN is executed on the same or 
different thread.

This behavior doesn't mean that starting a transaction blocks out other 
threads - they can happily execute any statements (which would then 
become part of the open transaction), just not BEGIN.

So yes, you can use BEGIN statement as a test to see whether a 
transaction is open on a connection. I must admit I haven't thought of 
that. See also sqlite3_get_autocommit.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath wrote:
>> "Igor Tandetnik"  writes:
>>> Nikolaus Rath  wrote:
 How can I determine the rowid of the last insert if I am accessing
 the db from different threads? If I understand correctly,
 last_insert_rowid() won't work reliably in this case.
>>>
>>> Last inserted rowid is maintained per connection. Do your threads use
>>> the same connection, or each create their own?
>>
>> Same connection, just different cursors.
>>
>>> If all threads share the same connection, it is your responsibility
>>> to make "insert then retrieve last rowid" an atomic operation, using
>>> thread synchronization mechanism of your choice. Just as with any
>>> access to shared data.
>>
>> Is BEGIN ... COMMIT sufficient for that?
>
> No. Transaction is also maintained per connection. Starting a 
> transaction would prevent other connections from making concurrent 
> changes, but wouldn't block other threads using the same connection.

Actually it seems to do exactly that:

,
| $ cat test.py 
| import apsw
| import threading
| import time
| 
| conn = apsw.Connection('test.db')
| 
| def thread1():
| cur = conn.cursor()
| cur.execute("BEGIN")
| print "Thread 1 in transaction"
| time.sleep(5)
| print "Thread 1 finished."
| 
| def thread2():
| cur = conn.cursor()
| time.sleep(2)
| cur.execute("BEGIN")
| print "Thread 2 in transaction"
| time.sleep(5)
| print "Thread 2 finished."
| 
| 
| threading.Thread(target=thread1).start()
| threading.Thread(target=thread2).start()
`

,
| $ python test.py 
| Thread 1 in transaction
| Exception in thread Thread-2:
| Traceback (most recent call last):
|   File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
| self.run()
|   File "/usr/lib/python2.6/threading.py", line 477, in run
| self.__target(*self.__args, **self.__kwargs)
|   File "test.py", line 17, in thread2
| cur.execute("BEGIN")
|   File "apsw.c", line 4238, in resetcursor
| SQLError: SQLError: cannot start a transaction within a transaction
| 
| Thread 1 finished.
`


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
> John Machin  writes:
>>> Now I'm confused. I want to know if it will be sufficient to wrap my
>>> last_insert_rowid() call between BEGIN .. and END in order to make it
>>> return the rowid that was last inserted by the same thread even if
>>> multiple threads are using the same connection (but different cursors).
>>>
>>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
>>> saying that it's not sufficient, I need to use additional mechanism.
>> As Igor pointed out, if you have multiple threads using the same 
>> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
>> If you don't have that, yes you need to "use additional mechanism" BUT 
>> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
>> added a rider like "(presuming your existing code is not stuffed)".
> 
> Are you saying that I need to use mutexes or whatever in the following
> program?
> 
> def thread1():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)
> 
> def thread2():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)
> 
> threading.Thread(target=thread1).start()
> threading.Thread(target=thread2).start()

Somebody needs to use mutexes somewhere. You have obscured the question 
by introducing two unknowns: (1) Which Python wrapper are you using 
(sqlite3 module or the apsw module)? (2) What does it do under the 
covers? Try asking the relevant guru for whatever you are using.

>>> Where am I wrong?
>> In wasting time on semantic confusion instead of implementing it and 
>> testing the bejaysus out of it.
> 
> When you are working with a multithreaded program, you can't even hope
> to test a fraction of the possible state trajectories. Finding the
> proper implementation by trial & error is therefore even more hopeless
> than in a single threaded program.

If you can't test it, then how will you know whether *any* 
implementation is "proper", let alone *the* "proper" one?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

John Machin  writes:
>> Now I'm confused. I want to know if it will be sufficient to wrap my
>> last_insert_rowid() call between BEGIN .. and END in order to make it
>> return the rowid that was last inserted by the same thread even if
>> multiple threads are using the same connection (but different cursors).
>> 
>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
>> saying that it's not sufficient, I need to use additional mechanism.
>
> As Igor pointed out, if you have multiple threads using the same 
> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
> If you don't have that, yes you need to "use additional mechanism" BUT 
> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
> added a rider like "(presuming your existing code is not stuffed)".

Are you saying that I need to use mutexes or whatever in the following
program?

def thread1():
cur = conn.cursor()
for i in range(500):
cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)

def thread2():
cur = conn.cursor()
for i in range(500):
cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)

threading.Thread(target=thread1).start()
threading.Thread(target=thread2).start()

   
>> Where am I wrong?
>
> In wasting time on semantic confusion instead of implementing it and 
> testing the bejaysus out of it.

When you are working with a multithreaded program, you can't even hope
to test a fraction of the possible state trajectories. Finding the
proper implementation by trial & error is therefore even more hopeless
than in a single threaded program.

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

John Elrick  wrote:
> Nuno Lucas wrote:
>> If you open a sqlite shell and do:
>>
>> SQLite version 3.6.14.2
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> begin;
>> sqlite> begin;
>
> Since we are discussing threads here, the example is meaningless.
> "begin" is a piece of text which is parsed into opcodes and then
> processed by a complied virtual machine.  Threads switch at the
> machine code level, so a thread could switch between the processing
> of the 'g' and the 'i' of the first 'begin'.

SQLite actually maintains a mutex per connection. Every API call 
acquires this mutext and keeps it for the duration of the call. Thus no 
two calls can proceed simultaneously on the same connection.

So no, another thread cannot interfere while sqlite3_prepare call 
advances from 'g' to 'i'.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[John Elrick]

Nuno Lucas wrote:
> On Wed, Jun 3, 2009 at 11:22 PM, Nikolaus Rath  wrote:
>   
>> Nuno Lucas  writes:
>> 
>>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
>>>   
 Nuno Lucas  writes:
 
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>   
>> Hello,
>>
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>> 
> It should work if you do:
>
> BEGIN
> INSERT ...
> last_insert_rowid()
> END
>   
 That would be very nice. But does "it should work" mean that you know
 that it works (and it is documented and guaranteed)? The above sounds a
 bit uncertain to me...
 
>>> It just means I'm too old to assume anything is certain. The Universe
>>> is always conspiring against you ;-)
>>>
>>> What I mean is that if it doesn't work, then you found a bug, most
>>> probably in your own code.
>>>   
>> Well, now you are in direct contradiction to Igor who says that it does
>> not work:
>> 
>
> Let's try end this confusion...
>
> If you open a sqlite shell and do:
>
> SQLite version 3.6.14.2
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> begin;
> sqlite> begin;

Since we are discussing threads here, the example is meaningless.  
"begin" is a piece of text which is parsed into opcodes and then 
processed by a complied virtual machine.  Threads switch at the machine 
code level, so a thread could switch between the processing of the 'g' 
and the 'i' of the first 'begin'.

IMHO, if you are programming in threads, maintain control in the 
application.  Threads are already non-deterministic and potentially 
buggy.  The last thing one wants is to add places of uncertainty.

Pseudocode:

entryPoint
  grabMutex
db.execute('begin;')
db.execute('insert...
lastInsertRowId = db.last_insert_rowid
  releaseMutex

Anything else "might" work, but I wouldn't want to waste time losing 
sleep over it.

FWIW

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

On Wed, Jun 3, 2009 at 11:22 PM, Nikolaus Rath  wrote:
> Nuno Lucas  writes:
>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
>>> Nuno Lucas  writes:
 On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
> Hello,
>
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.

 It should work if you do:

 BEGIN
 INSERT ...
 last_insert_rowid()
 END
>>>
>>> That would be very nice. But does "it should work" mean that you know
>>> that it works (and it is documented and guaranteed)? The above sounds a
>>> bit uncertain to me...
>>
>> It just means I'm too old to assume anything is certain. The Universe
>> is always conspiring against you ;-)
>>
>> What I mean is that if it doesn't work, then you found a bug, most
>> probably in your own code.
>
> Well, now you are in direct contradiction to Igor who says that it does
> not work:

Let's try end this confusion...

If you open a sqlite shell and do:

SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> begin;
sqlite> begin;
SQL error: cannot start a transaction within a transaction
sqlite>

So you see that you can't start a second transaction using the same
connections handle.

The last_insert_rowid variable is a property of the connection, so
they can only be different in different connections as they relate to
the last INSERT statement using that single connection.

You question involves the same connection handle used by different
threads, so now you know they share the same last_insert_rowid
variable and that you can't nest transactions using the same handle.

If you don't use transactions then you have to think of this variable
as a shared resource, and as such protect it's access using whatever
means you have at your disposal (being mutexes, semaphores, critical
sections, etc).

But, if you have a thread doing:

BEGIN
INSERT ...
last_insert_rowid()
...
END

You now know that a second thread executing the exact same code should
(barring bugs in sqlite code, bugs in your code, bugs in the compiler,
bugs in all the code your application and sqlite call, random bits
toggled by action of cosmic rays or similar occurrences) automatically
fail on the first BEGIN statement if executed in the middle of the
first thread transaction, so you simply can't even get the chance of
running the last_insert_rowid() function.

Hope this clears your doubts.


Regards,
~Nuno Lucas

>
> ,
> | >> If all threads share the same connection, it is your responsibility
> | >> to make "insert then retrieve last rowid" an atomic operation, using
> | >> thread synchronization mechanism of your choice. Just as with any
> | >> access to shared data.
> | >
> | > Is BEGIN ... COMMIT sufficient for that?
> |
> | No. Transaction is also maintained per connection. Starting a
> | transaction would prevent other connections from making concurrent
> | changes, but wouldn't block other threads using the same connection.
> `
>
>
> Any third opinions or references to documentation?
>
>
> Best,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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] Getting last inserted rowid?

[Harold Wood & Meyuni Gani]

If you are inserting in order then selecting the max value from  an indexed 
column should work.

Harold Wood 

-Original Message-
From: Nikolaus Rath <nikol...@rath.org>
Sent: Wednesday, June 03, 2009 3:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Getting last inserted rowid?

Nuno Lucas <ntlu...@gmail.com> writes:
> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote:
>> Nuno Lucas <ntlu...@gmail.com> writes:
>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote:
>>>> Hello,
>>>>
>>>> How can I determine the rowid of the last insert if I am accessing the
>>>> db from different threads? If I understand correctly,
>>>> last_insert_rowid() won't work reliably in this case.
>>>
>>> It should work if you do:
>>>
>>> BEGIN
>>> INSERT ...
>>> last_insert_rowid()
>>> END
>>
>> That would be very nice. But does "it should work" mean that you know
>> that it works (and it is documented and guaranteed)? The above sounds a
>> bit uncertain to me...
>
> It just means I'm too old to assume anything is certain. The Universe
> is always conspiring against you ;-)
>
> What I mean is that if it doesn't work, then you found a bug, most
> probably in your own code.

Well, now you are in direct contradiction to Igor who says that it does
not work:

,
| >> If all threads share the same connection, it is your responsibility
| >> to make "insert then retrieve last rowid" an atomic operation, using
| >> thread synchronization mechanism of your choice. Just as with any
| >> access to shared data.
| >
| > Is BEGIN ... COMMIT sufficient for that?
| 
| No. Transaction is also maintained per connection. Starting a 
| transaction would prevent other connections from making concurrent 
| changes, but wouldn't block other threads using the same connection.
`


Any third opinions or references to documentation? 


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
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] Getting last inserted rowid?

[John Machin]

On 4/06/2009 12:57 PM, Nikolaus Rath wrote:
> John Machin  writes:
>> On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
>>> Nuno Lucas  writes:
 On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
> Nuno Lucas  writes:
>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>>> Hello,
>>>
>>> How can I determine the rowid of the last insert if I am accessing the
>>> db from different threads? If I understand correctly,
>>> last_insert_rowid() won't work reliably in this case.
>> It should work if you do:
>>
>> BEGIN
>> INSERT ...
>> last_insert_rowid()
>> END
> That would be very nice. But does "it should work" mean that you know
> that it works (and it is documented and guaranteed)? The above sounds a
> bit uncertain to me...
>> Guaranteed? You're expecting a lot. Most software that you pay large 
>> sums of money for guarantee not much more than that the version numbers 
>> will be monotonically increasing.
> 
> I trust you know what I mean. Guaranteed in the sense that the
> developers try to make sqlite behave in this way and in contrast to "it
> just happens to work right now, but it might change anytime without
> warning".

"guarantee X" means "try to ensure X"??
You trust that I know that you mean that

> 
 It just means I'm too old to assume anything is certain. The Universe
 is always conspiring against you ;-)

 What I mean is that if it doesn't work, then you found a bug, most
 probably in your own code.
>>> Well, now you are in direct contradiction to Igor who says that it does
>>> not work:
>>>
>>> ,
>>> | >> If all threads share the same connection, it is your responsibility
>>> | >> to make "insert then retrieve last rowid" an atomic operation, using
>>> | >> thread synchronization mechanism of your choice. Just as with any
>>> | >> access to shared data.
>>> | >
>>> | > Is BEGIN ... COMMIT sufficient for that?
>>> | 
>>> | No. Transaction is also maintained per connection. Starting a 
>>> | transaction would prevent other connections from making concurrent 
>>> | changes, but wouldn't block other threads using the same connection.
>>> `
>>>
>>>
>>> Any third opinions or references to documentation? 
>> 
>> I don't see Igor saying it doesn't work. He says that it is not 
>> sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- 
>> just as you need to "with any access to shared data" if you have 
>> multiple threads per connection.
>>
>> "if it doesn't work, then you found a bug, most probably in your own 
>> code" is not "in direct contradiction to Igor" ... I'd call it 
>> corroborative of Igor.
>> 
> 
> Now I'm confused. I want to know if it will be sufficient to wrap my
> last_insert_rowid() call between BEGIN .. and END in order to make it
> return the rowid that was last inserted by the same thread even if
> multiple threads are using the same connection (but different cursors).
> 
> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
> saying that it's not sufficient, I need to use additional mechanism.

As Igor pointed out, if you have multiple threads using the same 
connection, you ALREADY need mutexes or whatever to maintain atomicity. 
If you don't have that, yes you need to "use additional mechanism" BUT 
this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
added a rider like "(presuming your existing code is not stuffed)".

> Where am I wrong?

In wasting time on semantic confusion instead of implementing it and 
testing the bejaysus out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

John Machin  writes:
> On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
>> Nuno Lucas  writes:
>>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
 Nuno Lucas  writes:
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>> Hello,
>>
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
> It should work if you do:
>
> BEGIN
> INSERT ...
> last_insert_rowid()
> END
 That would be very nice. But does "it should work" mean that you know
 that it works (and it is documented and guaranteed)? The above sounds a
 bit uncertain to me...
>
> Guaranteed? You're expecting a lot. Most software that you pay large 
> sums of money for guarantee not much more than that the version numbers 
> will be monotonically increasing.

I trust you know what I mean. Guaranteed in the sense that the
developers try to make sqlite behave in this way and in contrast to "it
just happens to work right now, but it might change anytime without
warning".

>>> It just means I'm too old to assume anything is certain. The Universe
>>> is always conspiring against you ;-)
>>>
>>> What I mean is that if it doesn't work, then you found a bug, most
>>> probably in your own code.
>> 
>> Well, now you are in direct contradiction to Igor who says that it does
>> not work:
>> 
>> ,
>> | >> If all threads share the same connection, it is your responsibility
>> | >> to make "insert then retrieve last rowid" an atomic operation, using
>> | >> thread synchronization mechanism of your choice. Just as with any
>> | >> access to shared data.
>> | >
>> | > Is BEGIN ... COMMIT sufficient for that?
>> | 
>> | No. Transaction is also maintained per connection. Starting a 
>> | transaction would prevent other connections from making concurrent 
>> | changes, but wouldn't block other threads using the same connection.
>> `
>> 
>> 
>> Any third opinions or references to documentation? 
>
> 
> I don't see Igor saying it doesn't work. He says that it is not 
> sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- 
> just as you need to "with any access to shared data" if you have 
> multiple threads per connection.
>
> "if it doesn't work, then you found a bug, most probably in your own 
> code" is not "in direct contradiction to Igor" ... I'd call it 
> corroborative of Igor.
> 

Now I'm confused. I want to know if it will be sufficient to wrap my
last_insert_rowid() call between BEGIN .. and END in order to make it
return the rowid that was last inserted by the same thread even if
multiple threads are using the same connection (but different cursors).

As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
saying that it's not sufficient, I need to use additional mechanism.

Where am I wrong?

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
> Nuno Lucas  writes:
>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
>>> Nuno Lucas  writes:
 On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
> Hello,
>
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.
 It should work if you do:

 BEGIN
 INSERT ...
 last_insert_rowid()
 END
>>> That would be very nice. But does "it should work" mean that you know
>>> that it works (and it is documented and guaranteed)? The above sounds a
>>> bit uncertain to me...

Guaranteed? You're expecting a lot. Most software that you pay large 
sums of money for guarantee not much more than that the version numbers 
will be monotonically increasing.

>> It just means I'm too old to assume anything is certain. The Universe
>> is always conspiring against you ;-)
>>
>> What I mean is that if it doesn't work, then you found a bug, most
>> probably in your own code.
> 
> Well, now you are in direct contradiction to Igor who says that it does
> not work:
> 
> ,
> | >> If all threads share the same connection, it is your responsibility
> | >> to make "insert then retrieve last rowid" an atomic operation, using
> | >> thread synchronization mechanism of your choice. Just as with any
> | >> access to shared data.
> | >
> | > Is BEGIN ... COMMIT sufficient for that?
> | 
> | No. Transaction is also maintained per connection. Starting a 
> | transaction would prevent other connections from making concurrent 
> | changes, but wouldn't block other threads using the same connection.
> `
> 
> 
> Any third opinions or references to documentation? 


I don't see Igor saying it doesn't work. He says that it is not 
sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- 
just as you need to "with any access to shared data" if you have 
multiple threads per connection.

"if it doesn't work, then you found a bug, most probably in your own 
code" is not "in direct contradiction to Igor" ... I'd call it 
corroborative of Igor.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

Nuno Lucas  writes:
> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
>> Nuno Lucas  writes:
>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
 Hello,

 How can I determine the rowid of the last insert if I am accessing the
 db from different threads? If I understand correctly,
 last_insert_rowid() won't work reliably in this case.
>>>
>>> It should work if you do:
>>>
>>> BEGIN
>>> INSERT ...
>>> last_insert_rowid()
>>> END
>>
>> That would be very nice. But does "it should work" mean that you know
>> that it works (and it is documented and guaranteed)? The above sounds a
>> bit uncertain to me...
>
> It just means I'm too old to assume anything is certain. The Universe
> is always conspiring against you ;-)
>
> What I mean is that if it doesn't work, then you found a bug, most
> probably in your own code.

Well, now you are in direct contradiction to Igor who says that it does
not work:

,
| >> If all threads share the same connection, it is your responsibility
| >> to make "insert then retrieve last rowid" an atomic operation, using
| >> thread synchronization mechanism of your choice. Just as with any
| >> access to shared data.
| >
| > Is BEGIN ... COMMIT sufficient for that?
| 
| No. Transaction is also maintained per connection. Starting a 
| transaction would prevent other connections from making concurrent 
| changes, but wouldn't block other threads using the same connection.
`


Any third opinions or references to documentation? 


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
> Nuno Lucas  writes:
>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>>> Hello,
>>>
>>> How can I determine the rowid of the last insert if I am accessing the
>>> db from different threads? If I understand correctly,
>>> last_insert_rowid() won't work reliably in this case.
>>
>> It should work if you do:
>>
>> BEGIN
>> INSERT ...
>> last_insert_rowid()
>> END
>
> That would be very nice. But does "it should work" mean that you know
> that it works (and it is documented and guaranteed)? The above sounds a
> bit uncertain to me...

It just means I'm too old to assume anything is certain. The Universe
is always conspiring against you ;-)

What I mean is that if it doesn't work, then you found a bug, most
probably in your own code.


Regards,
~Nuno Lucas

>
> Thanks for the clarification,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath wrote:
> "Igor Tandetnik"  writes:
>> Nikolaus Rath  wrote:
>>> How can I determine the rowid of the last insert if I am accessing
>>> the db from different threads? If I understand correctly,
>>> last_insert_rowid() won't work reliably in this case.
>>
>> Last inserted rowid is maintained per connection. Do your threads use
>> the same connection, or each create their own?
>
> Same connection, just different cursors.
>
>> If all threads share the same connection, it is your responsibility
>> to make "insert then retrieve last rowid" an atomic operation, using
>> thread synchronization mechanism of your choice. Just as with any
>> access to shared data.
>
> Is BEGIN ... COMMIT sufficient for that?

No. Transaction is also maintained per connection. Starting a 
transaction would prevent other connections from making concurrent 
changes, but wouldn't block other threads using the same connection.

> Or do I have to take care
> that no other thread is even attempting to execute some SQL?

It's OK to execute SQL - just not another INSERT.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

Nuno Lucas  writes:
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>> Hello,
>>
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> It should work if you do:
>
> BEGIN
> INSERT ...
> last_insert_rowid()
> END

That would be very nice. But does "it should work" mean that you know
that it works (and it is documented and guaranteed)? The above sounds a
bit uncertain to me...

Thanks for the clarification,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath  wrote:
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> Last inserted rowid is maintained per connection. Do your threads use 
> the same connection, or each create their own?

Same connection, just different cursors.

> If all threads share the same connection, it is your responsibility to
> make "insert then retrieve last rowid" an atomic operation, using
> thread synchronization mechanism of your choice. Just as with any
> access to shared data.

Is BEGIN ... COMMIT sufficient for that? Or do I have to take care that
no other thread is even attempting to execute some SQL?


>> I can't believe that I really have to do a SELECT on the data that I
>> just INSERTed only to get the rowid...
>
> I'm not sure how this helps, if another thread can insert more data 
> between your INSERT and SELECT. Wouldn't that suffer from the same 
> problem?

Not in my case, because I can uniquely select the data that I have just
inserted. 


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath  wrote:
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.

Last inserted rowid is maintained per connection. Do your threads use 
the same connection, or each create their own?

If all threads share the same connection, it is your responsibility to 
make "insert then retrieve last rowid" an atomic operation, using thread 
synchronization mechanism of your choice. Just as with any access to 
shared data.

> I can't believe that I really have to do a SELECT on the data that I
> just INSERTed only to get the rowid...

I'm not sure how this helps, if another thread can insert more data 
between your INSERT and SELECT. Wouldn't that suffer from the same 
problem?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
> Hello,
>
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.

It should work if you do:

BEGIN
INSERT ...
last_insert_rowid()
END

If you don't do this then last_insert_rowid() could refer to an insert
happening in other thred:


INSERT ...

INSERT ...

last_insert_rowid()

last_insert_rowid()
<...>

Also (not really sure as I avoid threads) I believe it will work if
each thread has it's own db handle, but don't know what will happen
you use the shared cache feature.


Regards,
~Nuno Lucas

>
> I can't believe that I really have to do a SELECT on the data that I
> just INSERTed only to get the rowid...
>
>
> Thanks,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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