last_insert_id with multiple threads

Question

I'm writing an application in C# that basically consists of two threads. Thread 1 gets data and puts it into a database, thread 2 takes that data does some additional operations. Will I run into problems with last_insert_id, like getting an insert id in the first thread from an insert that was done in the second thread? I am using the connector .net and primarily use the MySqlHelper with the connection string as a parameter.

Is last_insert_id safe to use in this case or would I be better off rethinking my approach, like creating two separate applications for these to tasks?

Also, I can't do everything in one thread. Each thread runs periodically and takes a different amount to complete. The first thread has to be able to insert data while the second thread performs its operations.

Answer 1

last_insert_id() is reliable on a PER CONNECTION basis. If your threads are sharing the same MySQL connection and doing inserts, then it's undefined as to what ID you'll be retrieving.

If you're not running too many threads, you might be better off having the individual threads open their own MySQL connections, and then you'd be able to use last_insert_id() without issue.