MySQL Forums
Forum List  »  InnoDB

Re: Deadlock information incomplete
Posted by: Rick James
Date: August 27, 2015 11:35AM

You are getting a "sequence number" from a table? Via a Stored Routine? Inside a transaction?

There is no good reason to have "get a seq number" inside a bigger transaction; get it before you START the TRANSACTION.

This change will decrease waits/deadlocks on the sequence table, letting the whole system run faster.

I suspect that will help significantly, but still not prevent all deadlocks. I see a very suspicious
delete from SystemSequence where NextId < _nextId

Is there really any need for more than one row in the table?

These two steps:
    UPDATE sequence SET id = LAST_INSERT_ID(id + 1)
           WHERE sequence_id = 'MySequenceName' ;
    SELECT LAST_INSERT_ID();
Note that LAST_INSERT_ID is session-specific, so is unaffected by other connections.

See, for example,
http://forums.mysql.com/read.php?10,616636,616636

(Even better would be to switch to AUTO_INCREMENT, if possible.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Deadlock information incomplete
962
August 27, 2015 11:35AM
800
August 29, 2015 11:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.