0
int addNew(MySqlConnection mysql,string new_name){
var tr = mysql.BeginTransaction();
try{
    new MySqlCommand(String.Format("select ... where name_col='{0}'",new_name), mysql, tr).ExecuteReader();
    ...check whether the reader is empty...
    if (!read_is_empty) {..rollback/return id...}
    var cmd=new MySqlCommand(String.Format("insert ... (default,...,'{0}')",new_name), mysql,tr);
    cmd.ExecuteNonQuery();
    int id=(int)cmd.LastInsertedId;
    tr.Commit();
}catch(Exception e){
    tr.Rollback();
}
}

The purpose is to create a new record if there is no existing record whose column "name_col" is new_name. Then return the created or existing record id which is the primary key with auto increment. "name_col" is unique key.

Although this can be done with "insert ignore", but the above code could work too, and get the id bring in more problem for "insert ignore".

When multiple clients run the code concurrently, sometimes duplicate entry exception is thrown. The duplicate entry is due to "name_col", not some other column.

Maybe my understanding about the transaction is wrong. What's wrong with the code?

jw_
  • 1,663
  • 18
  • 32
  • Seems to me that if you've got multiple clients running the code simultaneously, and new entries all have `'new_name'` as the name, it's inevitable that they will overlap at some point and throw a duplicate entry exception. Maybe you could try concatenating the name and the unique id, eg `new_name45`, for each entry – half of a glazier Feb 09 '20 at 13:56
  • @Still_learning Concatenating is not applicable for this case. Then is there an elegant way to insert new record in a who-first-who-insert way? I thought transaction is the elegant way. – jw_ Feb 09 '20 at 14:00
  • 1
    Perhaps this QA could help you. In particular read the comment in the accepted answer. https://stackoverflow.com/questions/4828490/mysql-concurrency-how-does-it-work-and-do-i-need-to-handle-it-in-my-application – Steve Feb 09 '20 at 14:14
  • @Steve Yes, the comment is what I'm looking for. The answer itself is wrong. Here too https://stackoverflow.com/questions/10935850/when-to-use-select-for-update – jw_ Feb 09 '20 at 14:19
  • The problem is that you are doing a select to check for an existing value, rather than using a unique index and check for an error. – Shadow Feb 09 '20 at 14:25
  • @Steve now with select udpate sometimes there is "Deadlock found when trying to get lock; try restarting transaction" – jw_ Feb 09 '20 at 14:28
  • @Shadow I mentioned there are other ways, but here is to focus on this way. For example, if name_col is very long which can't contain a effective unique key, the you need to check for duplication manually. – jw_ Feb 09 '20 at 14:30
  • If the column is too long for a unique key, then it is extremely unlikely to have a duplicate value in the first place. If this is some kind of a mission critical feture, then I would rather do a hash in a generated column on the long text column and have the unique key on the hashed value. Your approach is simply inefficient. – Shadow Feb 09 '20 at 14:35
  • @Shadow This is not server application, index(500 char)+manually check first is enough for my case. https://dba.stackexchange.com/questions/252009/how-to-implement-a-unique-key-for-a-long-varchar-column-in-mysql. Please close the question to one of the above question in the answer, the current close question have little to do with my question. – jw_ Feb 09 '20 at 14:38
  • @Shadow Do you know why I get "Deadlock found when trying to get lock; try restarting transaction" when I add "for udpate" for the select? The deadlock is on the insert, which means select return nothing, then what can cause deadlock? – jw_ Feb 10 '20 at 01:53

0 Answers0