1

I have a table:

CREATE TABLE MyTable (
  UserID BIGINT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY (UserID));

Now I need to insert value and get last insert key for that value. Right now I do it like this:

using (MySqlConnection connection = new MySqlConnection(CONNECTION_STRING))
{
    connection.Open();
    string query = $"INSERT INTO MyTable VALUES('Bob');\n" +
                   $"SELECT last_insert_id();";

    MySqlCommand command = new MySqlCommand(query, connection);
    var dataReader = command.ExecuteReaderAsync();
    await dataReader;

    long id = -1;
    while (dataReader.Result.Read())
    {
        string idString = (dataReader.Result as IDataRecord)[0].ToString();
        id = long.Parse(idString);
    }

    return id;
}

But what if there will be to many queries in one time? Is it possible that 2 simultaneous commands execute INSERT query and then both return the same value via last_insert_id()? How can I make sure that last_insert_id() will return value corresponding to its INSERT inside one command?

huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
Zura
  • 1,090
  • 2
  • 11
  • 16
  • 2
    `Is it possible that 2 simultaneous commands execute INSERT query and then both return the same value via last_insert_id()`, no they wont return the same value, its unique because it represents the first generated value of successfully inserted data; it's per record. – Trevor Nov 26 '19 at 16:31
  • Possible duplicate of [OUTPUT Clause in MySQL](https://stackoverflow.com/questions/5817414/output-clause-in-mysql) – Johnathan Barclay Nov 26 '19 at 16:39
  • @JohnathanBarclay there's no such in MySQL, that is asking for a workaround and this question is not a duplicate of your proposed link, – Trevor Nov 26 '19 at 16:40
  • 1
    last_insert_id is similar to Sql Server's SCOPE_IDENTITY. It's per-connection and limited to the current scope (the value is restored after triggers fire, so triggers do not affect the value in the current scope, even if they themselves also generate an id). – Robert McKee Nov 26 '19 at 16:53

1 Answers1

2

No, last_insert_id is maintained on a per-connection basis, so if two connections are running at the same time, one won't get the other's inserted value.

Robert McKee
  • 21,305
  • 1
  • 43
  • 57