Deadlock between Update and Insert queries

Does the id value of 110 exist in the M table? Also it may be useful to wrap these individual transactions in START TRANSACTION; and COMMIT; commands to ensure the insert completes before the update tries to run.

Example:

START TRANSACTION;INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd')COMMIT;START TRANSACTION;UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362COMMIT;

As said @SergGr your two queries cant cause deadlock. But possible next situation. For example, we have next records in MSC table:

id      m_id  s_id  c_id54362   109   1235  9b39cd

Now we trying to run next queries in parallel (I changed your update and wrote 1235 instead of 1234):

UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362;INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');

We must have problem with unique index on (m_id, s_id, c_id).

Update and insert may start in parallel, because there is no problem with constraint before start of execution. But queries cant finished, because they both must produce equal lines and they must be conflicted with unique constraint.

To avoid this situation you may use forced locks. For example,

START TRANSACTION;SELECT * FROM M WHERE id = 110 FOR UPDATE;UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362;COMMIT;START TRANSACTION;SELECT * FROM M WHERE id = 110 FOR UPDATE;INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');COMMIT;

I dont like similar locks because after that problem may be solved here but moved into up level. If it is possible, revise your database schema or algorithm. May be you will find more elegancy way to store and update your data without probability of deadlocks.