Row-level locking and XtraDB Cluster

  • Filter
  • Time
  • Show
Clear All
new posts

  • Row-level locking and XtraDB Cluster

    Hello, currently i am using MySQL with HA using DRBD and I am planning to migrate to Percona XtraDB Cluster. I use one InnoDB table with user data and set a column flag in it to indicate when the user is accessing the service. So when one web app process starts to work on behalf of this user, it sets the flag to acquire the row and when it finishes it unsets the flag so that other processes can do the same. Since InnoDB tables support row-level locking, I don't wrap this operation inside a transaction as it would not be possible for 2 processes to acquire the same row at the same time. But reading the doc about XtraDB Cluster, it seems all operations must be done inside transactions, which would imply some changes in our stored procedures. It would not be much work, but I want to confirm if this is really necessary.

  • #2
    InnoDB works the same in Percona XtraDB Cluster when you look from the single node perspective. But when you allow writes coming to multiple nodes, you may face conflicts. So either you keep doing writes to only one node at a time (like if you had only one master), or you will adapt your application to avoid conflicts.
    This may be helpful for you: