How to retrieve the last autoincremented ID from a SQLite table?

Question

I have a table Messages with columns ID (primary key, autoincrement) and Content (text).
I have a table Users with columns username (primary key, text) and Hash.
A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages.
I created a table Messages_Recipients with two columns: MessageID (referring to the ID column of the Messages table and Recipient (referring to the username column in the Users table). This table represents the many to many relation between recipients and messages.

So, the question I have is this. The ID of a new message will be created after it has been stored in the database. But how can I hold a reference to the MessageRow I just added in order to retrieve this new MessageID?
I can always search the database for the last row added of course, but that could possibly return a different row in a multithreaded environment?

EDIT: As I understand it for SQLite you can use the SELECT last_insert_rowid(). But how do I call this statement from ADO.Net?

My Persistence code (messages and messagesRecipients are DataTables):

public void Persist(Message message)
{
    pm_databaseDataSet.MessagesRow messagerow;
    messagerow=messages.AddMessagesRow(message.Sender,
                            message.TimeSent.ToFileTime(),
                            message.Content,
                            message.TimeCreated.ToFileTime());
    UpdateMessages();
    var x = messagerow;//I hoped the messagerow would hold a
    //reference to the new row in the Messages table, but it does not.
    foreach (var recipient in message.Recipients)
    {
        var row = messagesRecipients.NewMessages_RecipientsRow();
        row.Recipient = recipient;
        //row.MessageID= How do I find this??
        messagesRecipients.AddMessages_RecipientsRow(row);
        UpdateMessagesRecipients();//method not shown
    } 

}

private void UpdateMessages()
{
    messagesAdapter.Update(messages);
    messagesAdapter.Fill(messages);
}

Solution 1

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/[email protected]/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
    string sql = "SELECT last_insert_rowid()";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    int lastID = (Int32) cmd.ExecuteScalar();
}

Solution 2

One other option is to look at the system table sqlite_sequence. Your sqlite database will have that table automatically if you created any table with autoincrement primary key. This table is for sqlite to keep track of the autoincrement field so that it won't repeat the primary key even after you delete some rows or after some insert failed (read more about this here http://www.sqlite.org/autoinc.html).

So with this table there is the added benefit that you can find out your newly inserted item's primary key even after you inserted something else (in other tables, of course!). After making sure that your insert is successful (otherwise you will get a false number), you simply need to do:

select seq from sqlite_sequence where name="table_name"

Solution 3

I've had issues with using SELECT last_insert_rowid() in a multithreaded environment. If another thread inserts into another table that has an autoinc, last_insert_rowid will return the autoinc value from the new table.

Here's where they state that in the doco:

If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid.

That's from sqlite.org doco

Solution 4

According to Android Sqlite get last insert row id there is another query:

SELECT rowid from your_table_name order by ROWID DESC limit 1

Solution 5

sqlite3_last_insert_rowid() is unsafe in a multithreaded environment (and documented as such on SQLite) However the good news is that you can play with the chance, see below

ID reservation is NOT implemented in SQLite, you can also avoid PK using your own UNIQUE Primary Key if you know something always variant in your data.

Note: See if the clause on RETURNING won't solve your issue https://www.sqlite.org/lang_returning.html As this is only available in recent version of SQLite and may have some overhead, consider Using the fact that it's really bad luck if you have an insertion in-between your requests to SQLite

see also if you absolutely need to fetch SQlite internal PK, can you design your own predict-able PK: https://sqlite.org/withoutrowid.html

If need traditional PK AUTOINCREMENT, yes there is a small risk that the id you fetch may belong to another insertion. Small but unacceptable risk.

A workaround is to call twice the sqlite3_last_insert_rowid()

#1 BEFORE my Insert, then #2 AFTER my insert

as in :

int IdLast = sqlite3_last_insert_rowid(m_db);   // Before (this id is already used)

const int rc = sqlite3_exec(m_db, sql,NULL, NULL, &m_zErrMsg);

int IdEnd = sqlite3_last_insert_rowid(m_db);    // After Insertion most probably the right one,

In the vast majority of cases IdEnd==IdLast+1. This the "happy path" and you can rely on IdEnd as being the ID you look for.

Else you have to need to do an extra SELECT where you can use criteria based on IdLast to IdEnd (any additional criteria in WHERE clause are good to add if any)

Use ROWID (which is an SQlite keyword) to SELECT the id range that is relevant.

"SELECT my_pk_id FROM Symbols WHERE ROWID>%d && ROWID<=%d;",IdLast,IdEnd); 

// notice the > in: ROWID>%zd, as we already know that IdLast is NOT the one we look for.

As second call to sqlite3_last_insert_rowid is done right away after INSERT, this SELECT generally only return 2 or 3 row max. Then search in result from SELECT for the data you Inserted to find the proper id.

Performance improvement: As the call to sqlite3_last_insert_rowid() is way faster than the INSERT, (Even if mutex may make that wrong it is statistically true) I bet on IdEnd to be the right one and unwind the SELECT results by the end. Nearly in every cases we tested the last ROW does contain the ID you look for).

Performance improvement: If you have an additional UNIQUE Key, then add it to the WHERE to get only one row.

I experimented using 3 threads doing heavy Insertions, it worked as expected, the preparation + DB handling take the vast majority of CPU cycles, then results is that the Odd of mixup ID is in the range of 1/1000 insertions (situation where IdEnd>IdLast+1)

So the penalty of an additional SELECT to resolve this is rather low.

Otherwise said the benefit to use the sqlite3_last_insert_rowid() is great in the vast majority of Insertion, and if using some care, can even safely be used in MT.

Caveat: Situation is slightly more awkward in transactional mode.

Also SQLite didn't explicitly guaranty that ID will be contiguous and growing (unless AUTOINCREMENT). (At least I didn't found information about that, but looking at the SQLite source code it preclude that)

View more solutions

Comments

[Dabblernl]

Thank you again! Unfortunately it does not work as the last_insert_rowid() function needs to be called before the connection that is used to update the DataTable is closed. This may be a quirk of SQLite though...

[Jonathan Olson]

Sorry, yes that's probably true. Did you try executing it after the messagesAdapter.Update(messages);

[Fidel]

I guess you could use a separate connection for each thread, but I've noticed that it has a big performance hit. I can only do about 15 inserts per second in my scenario.

[Marek Bar]

It works when sequence is incremented and after row deletion.

[Jonathan Olson]

@Dabblernl seems another answer is more reliable, you might want to change the accepted answer if you feel another one is more helpful

[rogerdpack]

My guess is that separate threads would be the same issue. Unfortunately there doesn't seem to be a thread-safe work around to date, my hunch is multiple transactions wouldn't even be enough...so be careful :|

[CoolMind]

Good answer. Strange that in "DB Browser for SQLite" if remove a row from "sqlite_sequence", you won't get a last id for some table anymore.

[Кое Кто]

But RowId is not always the same as PRIMARY KEY. Except If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

[Sr. Libre]

This option has a performance penalty due execute a sort algorithm before finding the rowid

[Francine DeGrood Taylor]

Maybe it is less optimal, but it actually works once the connection has been closed and reopened, and the other two solutions didn't for me.

[tanius]

The following variant avoids ORDER BY. Should be faster, but I did not test that: SELECT MAX(rowid) FROM your_table_name