Deadlock found when trying to get lock; try restarting transaction...

Moderator: crythias

Post Reply
ndhvu275
Znuny advanced
Posts: 139
Joined: 06 Nov 2012, 09:02
Znuny Version: 3.x, 4.x and 5.x
Real Name: Vu Nguyen
Company: INFOdation
Location: Netherlands
Contact:

Deadlock found when trying to get lock; try restarting transaction...

Post by ndhvu275 »

Hello all,

Sometime, I am get a Deadlock error logs when creating tickets. It took over 1 week, but I cannot solve it. Please help me
Server info
- OTRS 4.0.9 installed on CentOS 6
- MySQL db servers use Galera cluters

Error log

Code: Select all

Jul 26 19:15:51 otrs02 GenericInterfaceProvider-11[11224]: [Error][Kernel::System::Ticket::TicketCreate][Line:452]: Deadlock found when trying to get lock; try restarting transaction, SQL: '#012            INSERT INTO ticket (tn, title, create_time_unix, type_id, queue_id, ticket_lock_id,#012                user_id, responsible_user_id, ticket_priority_id, ticket_state_id,#012                escalation_time, escalation_update_time, escalation_response_time,#012                escalation_solution_time, timeout, service_id, sla_id, until_time,#012                archive_flag, create_time, create_by, change_time, change_by)#012            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, ?, ?, 0, ?,#012                #012        '2017-07-26 19:15:51'#012    , ?, #012        '2017-07-26 19:15:51'#012    , ?)'
Jul 26 19:15:51 otrs02 GenericInterfaceProvider-11[16536]: [Info][Kernel::System::Ticket::TicketCreate] New Ticket [2017072611050128/GoBoss escalati] created (TicketID=7903579,Queue=Outbound::BNS Denials,Priority=3 normal,State=Open)
Jul 26 19:15:51 otrs02 GenericInterfaceProvider-11[16279]: [Error][Kernel::System::Ticket::TicketCreate][Line:452]: Deadlock found when trying to get lock; try restarting transaction, SQL: '#012            INSERT INTO ticket (tn, title, create_time_unix, type_id, queue_id, ticket_lock_id,#012                user_id, responsible_user_id, ticket_priority_id, ticket_state_id,#012                escalation_time, escalation_update_time, escalation_response_time,#012                escalation_solution_time, timeout, service_id, sla_id, until_time,#012                archive_flag, create_time, create_by, change_time, change_by)#012            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, ?, ?, 0, ?,#012                #012        '2017-07-26 19:15:51'#012    , ?, #012        '2017-07-26 19:15:51'#012    , ?)'
Jul 26 19:15:51 otrs02 GenericInterfaceProvider-11[11354]: [Error][Kernel::System::Ticket::TicketCreate][Line:452]: Deadlock found when trying to get lock; try restarting transaction, SQL: '#012            INSERT INTO ticket (tn, title, create_time_unix, type_id, queue_id, ticket_lock_id,#012                user_id, responsible_user_id, ticket_priority_id, ticket_state_id,#012                escalation_time, escalation_update_time, escalation_response_time,#012                escalation_solution_time, timeout, service_id, sla_id, until_time,#012                archive_flag, create_time, create_by, change_time, change_by)#012            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, ?, ?, 0, ?,#012                #012        '2017-07-26 19:15:51'#012    , ?, #012        '2017-07-26 19:15:51'#012    , ?)'
Cluster config in my.cnf

Code: Select all

datadir=/var/lib/mysql
user=mysql
log-error=/var/log/mysql/mysqld.log

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2, node#3 and node#4
wsrep_cluster_address=gcomm://10.10.64.19,10.10.64.20,10.10.64.21,10.10.64.22

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=10.10.64.19

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name="GLOPNET1"

# Authentication for SST method
wsrep_sst_auth="######"


max_connections=2000
max_allowed_packet=128M
performance_schema=off
innodb_file_per_table=1
bind-address = 0.0.0.0
skip-name-resolve
innodb_flush_method=O_DIRECT
innodb_log_file_size=500M

innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=12
innodb_additional_mem_pool_size=64M

tmpdir = /data/mysqltmp
datadir = /data/db

open_files_limit = 1024000
Hope someone can help me. Thanks

Vu Nguyen
OTRS 3.x, 4.x on CentOS/Windows
MySQL database
External customer backend with MySQL, MSSQL
Customization
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: Deadlock found when trying to get lock; try restarting transaction...

Post by jojo »

- outdated OTRS version
- unsupported database setup
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
ndhvu275
Znuny advanced
Posts: 139
Joined: 06 Nov 2012, 09:02
Znuny Version: 3.x, 4.x and 5.x
Real Name: Vu Nguyen
Company: INFOdation
Location: Netherlands
Contact:

Re: Deadlock found when trying to get lock; try restarting transaction...

Post by ndhvu275 »

Thanks Mod for your recommend. But it's really an issue on production, just post to hope someone met and solved the same issue
OTRS 3.x, 4.x on CentOS/Windows
MySQL database
External customer backend with MySQL, MSSQL
Customization
ndhvu275
Znuny advanced
Posts: 139
Joined: 06 Nov 2012, 09:02
Znuny Version: 3.x, 4.x and 5.x
Real Name: Vu Nguyen
Company: INFOdation
Location: Netherlands
Contact:

Re: Deadlock found when trying to get lock; try restarting transaction...

Post by ndhvu275 »

Anyone met this issue, please share a bit. Thanks
OTRS 3.x, 4.x on CentOS/Windows
MySQL database
External customer backend with MySQL, MSSQL
Customization
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: Deadlock found when trying to get lock; try restarting transaction...

Post by jojo »

As I already mentioned it is an unsupported database setup. So it is not a matter of OTRS software but of your database setup. (which makes it quite unlikely that it is used by anyone else)
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
Post Reply