Deadlock found when trying to get lock; try restarting transaction

[mfres]

Hello everyone,

we have deadlock problems with application.

Application use static database connections and makes a lot of inserts (about 80000) per minute also it does not reconnect on every query.

We use at the moment 3 multi master galera nodes with following installation packages:

MySQL-server-5.5.28_wsrep_23.7-1.rhel5
MySQL-client-5.5.28-1.rhel5
galera-23.2.2-1.rhel5

Loadbalancer
glb v0.9.1 (epoll),

error is like:

Warning: ServiceRequestLogger::log() - Deadlock found when trying to get lock; try restarting transaction in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php (205)
#0 /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php(105): MysqliTemplate->_checkForStatementError(Object(mysqli_stmt))
#1 /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php(28): MysqliTemplate->_query('INSERT INTO ser...', Array, false)
#2 /opt/prod/online/classes/db/dao/logging/ServiceRequestLogDao.php(78): MysqliTemplate->execute('INSERT INTO ser...')
#3 /opt/prod/online/aklogservice/classes/operators/ServiceRequestLogOperator.class.php(98): ServiceRequestLogDao->log('OK', '0.003023', 'stadtplan.domain...', 'www.domain....', '200', '0', '1359981783')
#4 /opt/prod/online/aklogservice/classes/core/AkLogService.class.php(153): ServiceRequestLogOperator->log(Object(AkLogItem))
#5 /opt/prod/online/aklogservice/bin/cmd/aklogservice.php(29): AkLogService->deQueueBu in /opt/prod/online/aklogservice/classes/operators/ServiceRequestLogOperator.class.php on line 102

Warning: mysqli::set_charset(): Error executing query in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php on line 19

Warning: Wrong COM_STMT_PREPARE response size. Received 7 in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php on line 74

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php on line 141

Warning: mysqli_stmt_error() expects parameter 1 to be mysqli_stmt, boolean given in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php on line 202

Fatal error: Call to a member function close() on a non-object in /opt/prod/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php on line 113



Galera Node configuration

####################################################################
[MYSQLD]
user=mysql
basedir=/usr/
datadir=/data2/mysql_data/cluster_log
socket=/data1/mysql_log/cluster_log/mysql.sock
pid-file=/data1/mysql_log/cluster_log/mysqld.pid
port=3306
bind-address=192.168......
log-error=/data1/mysql_log/cluster_log/error.log
#log-output=FILE
#relay-log=relay-bin
### INNODB
innodb-buffer-pool-size = 12000M
innodb-flush-log-at-trx_commit=2
innodb-file-per-table=1
innodb-doublewrite=0
innodb_log_file_size=1024M
innodb-log-files-in-group=3
innodb-buffer-pool-instances=10
innodb-thread-concurrency=0
#innodb-file-format=barracuda
innodb-flush-method = O_DIRECT
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
engine-condition-pushdown=1
innodb_log_group_home_dir = /data1/mysql_log/cluster_log/

# CHARACTER SET
#collation-server = utf8_unicode_ci
#init-connect='SET NAMES utf8'
character-set-server = utf8

# REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in replication.
server-id=3
#auto_increment_increment=2
#auto_increment_offset=SERVERID
# REPLICATION SPECIFIC - MASTER
binlog_format=ROW
#log-bin=binlog
#log-slave-updates=1
# OTHER THINGS, BUFFERS ETC
key_buffer_size = 32M
max-allowed-packet = 512M
sort-buffer-size = 512K
read-buffer-size = 256K
read-rnd-buffer-size = 512K
#thread-cache-size=1024
myisam-sort-buffer_size = 8M
memlock=0
sysdate-is-now=1
max-connections=301
thread-cache-size=512
query-cache-type = 0
query-cache-size = 0
table-open_cache=1024
lower-case-table-names=0
##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_address=192.168......
# Provider specific configuration options
wsrep_provider_options="gcache.size=1G"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="CLUSTER_LOG"

# Group communication system handle

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name=production.cluster-log.domain.org

# Address for incoming client connections. Autodetect by default.
wsrep_node_incoming_address=192.168.....:4567

# How many threads will process writesets from other nodes
# (more than one untested)
wsrep_slave_threads=48

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1
# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
# ClusterControl currently DOES NOT support wsrep_sst_method=mysqldump
wsrep_sst_method=rsync


# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
wsrep_sst_receive_address=192.168.......

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
#wsrep_sst_auth=root:

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=
[MYSQL]
socket=/data1/mysql_log/cluster_log/mysql.sock
[client]
socket=/data1/mysql_log/cluster_log/mysql.sock
default_character_set                   = utf8
[mysqldump]
max-allowed-packet = 512M
default_character_set                   = utf8
[MYSQLD_SAFE]
wsrep_urls=gcomm://192.168.......:4567,gcomm://192.168.......:4567,gcomm://192.168......:4567,gcomm://
pid-file=/data1/mysql_log/cluster_log/mysqld.pid
log-error=/data1/mysql_log/cluster_log/error.log
basedir=/usr/
datadir=/data2/mysql_data/cluster_log
####################################################################


thanks

[Alex Yurchenko]

Hi,

What you describe seems to be cluster-wide conflicts. E.g. two
transactions on different nodes simultaneously modifying the same row.

This is by design and should be expected in a multi-master operation.
All you need to do is exactly what it says: restarting transaction (it
is already rolled back).

To avoid this you have to direct all (potentially conflicting) writes
to a single node.

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[enric...@unbelievable-machine.com]

Hi,

i have the same issues. I have 4 nodes + garbd. But in our Loadbalancer (F5) only one of this nodes is used at all, so there are no writes to other members of this cluster because only one is used, we just configured it for fallback. Use the next one if one does not work and so on. We also do not have massive writes or selects, it rarely exceeds 200 qps.

Same with duplicate keys. How can that happen if there are no conflicting writes, because only one node is used.

However

[Alex Yurchenko]

Hi Enrico,

Do you imply that that you have deadlock and duplicate key errors even
though you use only one node for writes?

If so

1) Do you have deadlock errors on a "master" node or on "slave" nodes?

2) Did you test your load on a stock MySQL server? It is not impossible
for application to cause these errors naturally: nobody can prevent you
from trying to insert a row with a duplicate key as well as locking rows
in conflicting order (there can easily be a flaw in application logic).