2

Why is the record not locking between 2 nodes of MariaDB Galera Cluster 10.2.11?

Node 1                                        Node 2

START TRANSACTION;
SELECT * FROM table WHERE id = 1 FOR UPDATE;
+----+------+
| id | info |
+----+------+
|  1 | 123  |
+----+------+

                                              START TRANSACTION;
                                              SELECT * FROM table WHERE id = 1 FOR UPDATE;
                                              +----+------+
                                              | id | info |
                                              +----+------+
                                              |  1 | 123  |
                                              +----+------+

If I try this by two connection in one node - locking is ok.

My server config (/etc/my.cnf.d/server.cnf)

[server]

[mysqld]
log_bin = prod-bin
sql_mode = STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
transaction-isolation = READ-COMMITTED
slow_query_log = ON
slow_launch_time = 2
innodb_buffer_pool_size=3G
expire_logs_days = 30
auto_increment_increment = 3
auto_increment_offset = 2
sql_error_log_rotate = TRUE
max_connections = 2000

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.102,192.168.0.80,192.168.0.23
wsrep_cluster_name="cluster"
wsrep_sst_method=xtrabackup
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

[embedded]

[mariadb]

[mariadb-10.1]

Thank you.

  • 1
    Galera has a "optimistic" looking. This means that only after a change of data the nodes looks who was the first. the other gets a deadlook. this must be handled from the application – Bernd Buffen Jun 22 '18 at 09:29
  • optimistic locking can be bad in some situations..There is for example a chance that a client won't retrive the lasted version of the data from the database because of the optimistic locked.. That's ofcource not good when you been the data to be accurate.. If i was you i would research if it's possible to run MySQL cluster with MariaDB to get the good old pessimistic locking if you need 100% accurate data. – Raymond Nijland Jun 22 '18 at 10:17
  • wow, exactly, Bernd! when i did changes and called commit, first node said OK, but second said: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction – salam_akhin Jun 22 '18 at 13:04
  • if you paste your idea to answer, i mark it as solving. – salam_akhin Jun 22 '18 at 13:07
  • With Galera, you must check for errors even after `COMMIT`. – Rick James Jun 24 '18 at 20:29

0 Answers0