I'm doing this test in order to understand how Mysql NDB cluster works. So I have two sql nodes. Preparation I created the following table:
CREATE TABLE tb_id (
id bigint(20) NOT NULL AUTO_INCREMENT,
ix bigint(20) default 0,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
INSERT INTO tb_id (name, ix) VALUES
('dog', 1),('cat', 2),('penguin', 3), ('cow', 4),('tiger', 5),('fish', 6);
I create 2 sessions, each on a different node and I run on both: set session autocommit=off; after that on session one I run:
UPDATE tb_id SET ix = 2 where name="cat";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
no commit and run the following on session 2:
UPDATE tb_id SET ix = 2 where name="fish";
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Why does the second session fails since I'm not update the same row?
The second transaction is using a full table scan to find the rows to update since there is no index on the name column. Since this is an update, the scan takes an exclusive lock on all rows before deciding if the row should be updated. The row with "cat" is locked exclusively, so when the full table scan reaches this row it will wait for the row to be unlocked.
If you had an index on the name column, the problem would disappear. But an update or delete that uses a condition that has no index support will have to acquire an exclusive lock on every row in the table.