Thread-safe last_insert_row_id for ruby SQLite library

Question

I wondering what a thread-safe way to do last_insert_row_id would be. Currently my code looks like:

require 'sinatra'
require 'sqlite3'

db = SQLite3::Database.connect("dbname.db")

get '/' do 
  db.execute("INSERT INTO logs (ip,time) VALUES (?,now())",[request.ip])
  return "Your row id is #{db.last_insert_row_id}"
end

It's a little silly of an example, but my point is that if two people visit at the same time, it could get into a race condition such that two people are given the same row id. How do I avoid this?

Baisicly what I'm looking for is a ruby-equivelent answer to this question: How to retrieve inserted id after inserting row in SQLite using Python? I looked and couldn't find anything in the sqlite gem docs about a cursor, and googling brought me to the ResultSet class which does not have a method of retrieving the last insert ID.

score 1 · Answer 1 · answered May 14 '15 at 07:43

The "last insert rowid" is a property of connection, thus if you need to track row ids in multithreaded application, I think you will need a separate sqlite connection for each thread.

An alternative would be to get the next ID manually by selecting the value from sqlite_sequence table (note that this works only if your table has an explicit PRIMARY KEY AUTOINCREMENT column):

BEGIN;
SELECT seq FROM sqlite_sequence WHERE name = 'your_table'; -- remember this value in ruby
UPDATE sqlite_sequence SET seq = seq+1 WHERE table = 'your_table';
COMMIT;

and then:

db.execute("INSERT INTO logs (pk_column,ip,time) VALUES (?,?,now())",[preselected_row_id,request.ip])

The downside of that second approach is that it's a bit slow when compared to a simple INSERT.

Thread-safe last_insert_row_id for ruby SQLite library

1 Answers1