> reasons. Can primary key values available because of deletes get re- > used at some point?
I guess you're talking about rowids not about general primary keys. For rowids reuse is possible only if rows with maximum values of rowids are deleted and only if your INTEGER PRIMARY KEY column doesn't declared as AUTOINCREMENT. > Also, in a multi-user environment, is it possible > that another user could insert a record into the same table after my > insert but before my SELECT max statement, or would locking prevent > that possibility? If you don't do select in the same transaction as insert then it is possible to catch other user's id. Locking works only inside transaction, it cannot work between transaction boundaries. > The description of last_insert_rowid says it "returns the rowid of the > most recent successful INSERT into the database from the database > connection in the first argument". and the only parameter is the > database connection. In a multi-user environment, it sounds like this > could be the rowid from a different table or, once again, does locking > take care of that possibility? "Multi-user environment" that shares the same connection between multiple users? Doesn't that sound weird? If you've written your application in that way and try to be sure that last rowid comes from the correct table then it's the matter of your application correctness. Pavel On Thu, Nov 26, 2009 at 1:09 PM, Peter Haworth <[email protected]> wrote: > > > On Nov 26, 2009, at 4:00 AM, [email protected] wrote: > >> Message: 28 >> Date: Thu, 26 Nov 2009 06:38:52 +0000 >> From: Simon Slavin <[email protected]> >> Subject: Re: [sqlite] Getting the rowid after an insert >> To: General Discussion of SQLite Database <[email protected]> >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset=us-ascii >> >> >> On 26 Nov 2009, at 5:54am, Edward Diener wrote: >> >>> I have a table with an integer primary key as the first type. My >>> understanding is that this is an alias for the rowid. When I insert a >>> row in this table using _sqlite3_prepare and then sqlite3_step I >>> need to >>> retrieve the rowid for the row I have just inserted. Is there an SQL >>> statement I can use, or a way, to do that ? >> >> http://www.sqlite.org/c3ref/last_insert_rowid.html >> >> Simon. > > I have this question too. I'm not programming in C so don;t have > access to the sqlite_last_insert_rowid so have been using a SELECT max > statement to get the maximum value of the primary key in the table I > just inserted a row into. > > Never been entirely comfortable with that method for a couple of > reasons. Can primary key values available because of deletes get re- > used at some point? Also, in a multi-user environment, is it possible > that another user could insert a record into the same table after my > insert but before my SELECT max statement, or would locking prevent > that possibility? > > The description of last_insert_rowid says it "returns the rowid of the > most recent successful INSERT into the database from the database > connection in the first argument". and the only parameter is the > database connection. In a multi-user environment, it sounds like this > could be the rowid from a different table or, once again, does locking > take care of that possibility? > > Pete > _______________________________________________ > 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
