0

Assume I have a multi-master innodb cluster (with just 2 data nodes) where clients make requests through mysql routers. Suppose a specific row on some table in a database is updated on both the nodes by two clients almost at the same time. Now both the nodes are having different data in the same row. How these kinds of conflicts will be reconciled in a multi-master innodb cluster?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Nipun Talukdar
  • 4,975
  • 6
  • 30
  • 42
  • This will depend on your specific setup, but if you follow the guidelines, the basic rule is: only one conflicting transaction can commit, the others will fail (which in turn can lead to so called "hot spots" if you have some rows you update often, resulting in frequent rollbacks). – Solarflare Aug 18 '17 at 08:12
  • @Solarflare on multi-master innodb cluster transactions on the both servers may be successful. Basically, each transaction is happening on two database servers. But the changes from both the servers are replicated to each other. In this case, which update will win (2 updates on the same row, but on 2 different servers)? – Nipun Talukdar Aug 18 '17 at 08:17
  • 1
    Is this a statement or a question if that can happen? In a multi master setup, transactions will check the other servers before committing, and the first one wins. – Solarflare Aug 18 '17 at 08:20

1 Answers1

3

The key word in your question "Suppose a specific row on some table in a database is updated on both the nodes by two clients almost at the same time." is almost

Each transaction in InnoDB cluster assigned GTID, based on the GTID transactions will be applied to all cluster instances in the order they were created ( or to be exact committed). In a case both transaction were commited exactly at the same time one of the transaction will be failed by InnoDB Cluster.

Alec
  • 630
  • 5
  • 8
  • A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (source). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. – Mohit Rustagi Sep 24 '20 at 16:44