Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I can trigger a deadlock on our (test) Galera cluster when updating a row with the same data.

INSERT INTO test (id, val) VALUES (1, 42), (2, 47);
-- Query OK, 2 rows affected (0.01 sec)
-- Records: 2  Duplicates: 0  Warnings: 0

Updating a value to a different value, doesn't result in an error:

UPDATE test SET val=43 WHERE id=1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

And when you set it to the same value:

UPDATE test SET val=47 WHERE id=2;
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

It appears to be a sort of warning that is incorrectly relayed (from /var/log/mysql):

140106 13:52:04 [Warning] WSREP: SQL statement was ineffective, THD: 12, buf: 103
QUERY: UPDATE test SET val=47 WHERE id=2
 => Skipping replication
140106 13:52:04 [Warning] WSREP: SQL statement was ineffective, THD: 12, buf: 103
QUERY: UPDATE test SET val=47 WHERE id=2
 => Skipping replication

Note: the table is created like

CREATE TABLE IF NOT EXISTS `test` (
      `id` int(3) NOT NULL PRIMARY KEY,
      `val` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf-8;
share|improve this question

1 Answer 1

up vote 4 down vote accepted

If your binary log format on the server is "STATEMENT" (Check by SELECT @@binlog_format;) You should restart the server with --binlog-format=row and then retry your queries. Normally this would fix your problem!

Source: https://mariadb.atlassian.net/browse/MDEV-5490?jql=project%20%3D%20MDEV%20AND%20issuetype%20%3D%20Bug%20AND%20text%20~%20%22update%20deadlock%22

share|improve this answer
    
Silly me, I forgot to update set the mandatory settings in the Galera cluster. Thanks! –  Gert Jan 6 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.