How to manage primary key with multiple connection?

Question

Suppose I have an application that extract some data from an internet site and add them to a database. This application run in multiple instance, each instance extract data for a specific country.

Some data are linked to a master table called rounds which have as PK an auto-increment field, my doubt comes from this code:

using (MySqlConnection connection = new DBConnection().Connect())
{
    using (MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT IGNORE INTO competition_rounds (round_id, season_id, `name`) 
                                             VALUES (@round_id, @season_id, @round_name)";

        command.Parameters.Add("@round_id", MySqlDbType.Int32).Value = round.Id;
        command.Parameters.Add("@season_id", MySqlDbType.Int32).Value = round.seasonId;
        command.Parameters.Add("@round_name", MySqlDbType.VarChar).Value = round.Name;
        command.ExecuteNonQuery();

        return Convert.ToInt32(command.LastInsertedId);
    }
}

The code above add a new round to the rounds table, and this works well. But if I have multiple instances running, is possible that the application will fire the same code (in both the instances) and return the same id for both instance? eg:

instance 1 -> fire round insert -> return 3
instance 2 -> fire round insert -> return 3

both instance has executed the same method in the exact same time. Could this situation happen? Is possible prevent that? Should I create a guid or a composed PK?

Are you asking if authors of MySQL are complete !@## and don't know how to correctly implement autoincrement? (unlikely).. Or you just need explanation on what autoincrement in DB means? — Alexei Levenkov, Nov 21 '18 at 18:39
The whole point of an `auto_increment` column is that the DB handles this for you. — stuartd, Nov 21 '18 at 18:39
@AlexeiLevenkov nothing of them, I just have a doubt about the multiple instances that could fire the same code, I do not want to insult anyone — Spartaok, Nov 21 '18 at 18:40
Your doubt is valid and I commend you for thinking at such a deep level. Having said that, the db server knows and it will only do one then the other. You don't need to worry about it and the db server is smart enough to not do that. — CodingYoshi, Nov 21 '18 at 18:47
It's OK to wonder. This might help: https://stackoverflow.com/questions/4562479/how-does-mysql-auto-increment-work — JuanR, Nov 21 '18 at 18:51
@Spartaok And when you looked at the documentation of the auto increment feature of that DB what did it say on the matter of multiple concurrent connections? Did it mention whether it was supported or not? — Servy, Nov 21 '18 at 18:52

score 1 · Answer 1 · edited Nov 21 '18 at 19:53

1

Database Management Systems (DBMSs) such as MySQL operate on a basis of ACID (Atomicity, Consistency, Isolation, Durability) transactions. These transactions are scheduled in a sequential all or nothing fashion. Therefore, you don't need to worry about parallel transactions.

That said, with the multiple application instances you may need to worry about which transaction is process first. That is, UserA of application instanceA may send insert A and UserB of application instanceB may send insert B some time after UserA. Even though UserA sent the request first, they can be received and process in B then A order - perhaps due to network latency.

edited Nov 21 '18 at 19:53

CodingYoshi

25,467
4
62
64

answered Nov 21 '18 at 18:50

Jason

2,382
1
16
29

The adding order is not a problem, I just want to be sure that the data linked to a specific round will get the exact round reference – Spartaok Nov 21 '18 at 18:51
@CodingYoshi I guess that Jason could be right, because specially in that type of application an instance could await the response of a site, so the other instance meanwhile continue to adding data in the database. But I'm not worried about this – Spartaok Nov 21 '18 at 18:55
@CodingYoshi I'm not asserting transactions _will be_ received not in the order they are sent, only that they _can be_. – Jason Nov 21 '18 at 19:03
@CodingYoshi I would think that the out-of-order issue could occur due to networking issues. For example, an application user on the LAN sending transactions within the same second as a user also sending them from a remote location over a VPN. The VPN user may send the transaction first - in real time, but due to network latency their transaction arrives at the database after the LAN user. If time is important, one could timestamp the transaction at the point of origin. – Jason Nov 21 '18 at 19:38
@CodingYoshi I updated the answer to clarify which type of instance I was talking about. Good catch, thanks. – Jason Nov 21 '18 at 19:48

score 1 · Accepted Answer · answered Nov 21 '18 at 19:28

The client loads the LastInsertedId property from the OK_PACKET:

An OK packet is sent from the server to the client to signal successful completion of a command. As of MySQL 5.7.5, OK packets are also used to indicate EOF, and EOF packets are deprecated.

On the server side, from the documentation:

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

In other words, this kind of situation is accounted for (in any respectable DB system).

You'll be fine.

How to manage primary key with multiple connection?

2 Answers2