Multiple interface Update single table frequently creates updates with unique key conflicts

Environment: mysql5.6, php5.5
Multiple API interfaces simultaneously create data for single-table C_point table updates,

C_POINT Table Structure:
idPrimary key int (one) auto incrment,
uidchar (+) NOT null unique key,
temporaryDecimal (10,2) is not NULL,
pressureDecimal (10,2) is not NULL,
updated_atTimestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp

Calculation rules:
UID = MD5 (Deviceid+pointid)

Interface A:
Update the temporary field of the C_point table, or create a new record if it is not.
Interface B:
Update the pressure of the C_point table, or create a new record if not.

Interface A and B have a select check before inserting.

When A and B interfaces are called simultaneously, A and B are simultaneously insert operations, reporting
Exception ' pdoexception ' with message ' sqlstate[23000]: Integrity constraint violation:1062 Duplicate entry ' 8b3e94ae356 8001854dd7c112702dd36 ' for key ' UID '

What happens when an update is created for the same row on the same table for this multiple interface, allowing two interfaces to update their respective fields?

Conclusion:
1) before inserting Select, then insert (the current scene is not appropriate, but it is still necessary to add)
2) Try catch parsing error code

  $duplicate = false;  $entry      = $key = null;  $inner      = $e->getInner();   $info       = $inner->errorInfo;  // Check if mysql error is for a duplicate key  if (in_array($info[1], array(1062, 1022, 1558))) {        $duplicate = true;        preg_match("/'(.*)'.*'(.*)'/", $info[2], $matches);        $entry = $matches[1];        $key = $matches[2];  }

3) Insert Ignore
4) on duplicate key

Specific problems require specific analysis

http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/

Reply content:

Environment: mysql5.6, php5.5
Multiple API interfaces simultaneously create data for single-table C_point table updates,

C_POINT Table Structure:
idPrimary key int (one) auto incrment,
uidchar (+) NOT null unique key,
temporaryDecimal (10,2) is not NULL,
pressureDecimal (10,2) is not NULL,
updated_atTimestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp

Calculation rules:
UID = MD5 (Deviceid+pointid)

Interface A:
Update the temporary field of the C_point table, or create a new record if it is not.
Interface B:
Update the pressure of the C_point table, or create a new record if not.

Interface A and B have a select check before inserting.

When A and B interfaces are called simultaneously, A and B are simultaneously insert operations, reporting
Exception ' pdoexception ' with message ' sqlstate[23000]: Integrity constraint violation:1062 Duplicate entry ' 8b3e94ae356 8001854dd7c112702dd36 ' for key ' UID '

What happens when an update is created for the same row on the same table for this multiple interface, allowing two interfaces to update their respective fields?

Conclusion:
1) before inserting Select, then insert (the current scene is not appropriate, but it is still necessary to add)
2) Try catch parsing error code

  $duplicate = false;  $entry      = $key = null;  $inner      = $e->getInner();   $info       = $inner->errorInfo;  // Check if mysql error is for a duplicate key  if (in_array($info[1], array(1062, 1022, 1558))) {        $duplicate = true;        preg_match("/'(.*)'.*'(.*)'/", $info[2], $matches);        $entry = $matches[1];        $key = $matches[2];  }

3) Insert Ignore
4) on duplicate key

Specific problems require specific analysis

http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/

You do not need to use the Select Check. Direct insertion, conflict, indicates that there is already, and is done in accordance with the existing logic.

PS: Please carefully typesetting your question good, just because the typesetting is not clear to see the wrong.

