Many thanks for your response Igor. Please see response and additional query inline.
On Mon, Apr 21, 2008 at 7:40 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Gaurav Mathur" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > Suppose I have the following schema: > > > > CREATE TABLE t1 > > ( > > f1 integer, > > f2 text > > ); > > > > CREATE TABLE t2 > > ( > > val integer > > ); > > > > Suppose the following inserts statement are executed by two processes > > over the above schema. > > > > insert into t1 values ((SELECT val FROM t2 WHERE ROWID=1), "aa"); > > UPDATE t2 SET val = val + 1 WHERE ROWID=1; > > > > The idea is to use the value 'val' from the table t2 and set the value > > of f1 to that value on an insert. The question I have is whether I can > > rely on the above sequence of SQL to reliably generate *unique* f2 > > values for all INSERTs. > > Why not just use ROWID as a unique identifier? > This is because I need to avoid one property of ROWID fields and that's that SQLITE reuses the ROWID values. For example, in the following sequence of events, 1. Insert 3 rows in t1 2. Delete last row from t1 3. Insert 1 row in t1 assuming there are no instructions between operations (2) and (3), the row added in operation (3) will have the same ROWID value as the row deleted in operation (2). The application I am using SQLITE for needs to set unique identifiers that are never reused. These identifiers will be maintained in a separate table like t2 in my example above. > > Basically, I am not sure whether there is a > > possibility that a thread/process can be preempted after executing the > > SELECT but before executing the INSERT in that thread/process. > > No. An implicit transaction is created for each statement. What is > possible is for another thread to squeeze a statement between your > insert and update. To prevent that, surround the two statements with an > explicit transaction. > Got it. > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
