0

In this question Get the id of inserted row using C#, some comments mention that last_insert_id/LastInsertedId is not thread safe, then what is the thread safe way? Is transaction like bellow enough?

MySqlTransaction tr = mysqlcon.BeginTransaction();
MySqlCommand cmd=new MySqlCommand("insert ...", mysqlcon, tr);
cmd.ExecuteNonQuery();
long id = cmd.LastInsertedId; 
tr.Commit();
jw_
  • 1,663
  • 18
  • 32
  • 1
    Did you read the other comment about shared connection? That is the whole point – Sir Rufo Feb 02 '20 at 03:26
  • You will be fine unless you are running your application with many different threads and a single connection. But if you are running insert statements on different threads and are using a shared connection the last inserted id could be overwritten due to another thread running an insert query before the last thread finished its request for the last inserted id. – jgetner Feb 02 '20 at 03:35
  • @SirRufo I implicitly mean that the connection is shared – jw_ Feb 02 '20 at 03:44
  • 1
    @jgetner Then the anwer is no? Non-shared connection is the only way? I use shared connection since non-shared means a thread pool with connection on each thread which I'm not familiar with. In my there are jobs pending to be done by a thread, not some fixed threads. – jw_ Feb 02 '20 at 03:49
  • Well but sharing the connection is not thread safe. You have to take care that only one thread is **using** the connection. – Sir Rufo Feb 02 '20 at 03:50
  • @SirRufo Then thread pool with their own connection is the only choice – jw_ Feb 02 '20 at 03:51
  • 1
    It is the same with your family car. Some of you are sharing one car, but only one of you can drive the car and all others have to **wait** for the driver to finish driving before they can drive ;o) – Sir Rufo Feb 02 '20 at 03:52
  • @SirRufo Got it, now it is an object lock problem, not a MySQL problem. Transaction is for contentions cat you can't control in-process. – jw_ Feb 02 '20 at 03:54

0 Answers0