Getting last inserted ID

Question

I'm currently using the method below to get the ID of the last inserted row.

    Database.ExecuteNonQuery(query, parameters);

    //if another client/connection inserts a record at this time, 
    //could the line below return the incorrect row ID?
    int fileid = Convert.ToInt32(Database.Scalar("SELECT last_insert_rowid()"));
    return fileid;

This method has been working fine so far, but I'm not sure it's completely reliable.

Supposing there are two client applications, each with their own separate database connections, that invoke the server-side method above at exactly the same time. Keeping in mind that the client threads run in parallel and that SQLite can only run one operation at a time (or so I've heard), would it be possible for one client instance to return the row ID of a record that was inserted by the other instance?

Lastly, is there a better way of getting the last inserted row ID?

Answer 1

If both the insert command and the get last inserted row id command are inside the same write lock and no other insert command in that write lock can run between those two commands, then you're safe.

If you start the write lock after the insert command, than there's no way to be sure that another thread didn't get a write lock first. If another thread did get a write lock first, then you won't be able to execute a search for the row id until after that other thread has released their lock. By then, it could be too late if the other thread inserted a new row.

Answer 2

If another client/connection inserts a record at this time, could the line below return the incorrect row ID?

No, since the write will either happen after the read, or before the read, but not during the read.

Keeping in mind that the client threads run in parallel and that SQLite can only run one operation at a time, would it be possible for one client to get the row ID of the record that was inserted by the other client?

Yes, of course.

It doesn't matter that the server-side methods are invoked at exactly the same time. The database's locking allows concurrent reads, though not concurrent writes, or reads while writing.

if you haven't already, have a read over SQLite's file locking model.