SQL error 1213 : Deadlock found when trying to get lock; try restarting transaction

Community Support
1

Hi,

I keep seeing SQL deadlock error in oned.log file. Not sure what else to try.
Is this something that could present a problem later? I get about dozen errors a day, all seems to be related to updating monitored info. I’ve recently ran onedb check command.

TRANSACTION ISOLATION LEVEL is SET to READ-COMITTED, as suggested on email list (Oct 6, 2014).

mysql> SELECT @@TX_ISOLATION;
±---------------+
| @@TX_ISOLATION |
±---------------+
| READ-COMMITTED |
±---------------+

and I’m using InnoDB engine (mysql server 5.6). All tables in ‘opennebula’ database using InnoDB engine as well.

The exact error(s):

Tue Mar 17 09:19:54 2015 [Z0][ONE][E]: SQL command was: REPLACE INTO host_pool (oid, name, body, state, last_mon_time, uid, gid, owner_u, group_u, other
_u, cid) VALUES (189,'BC3-14','<HOST><ID>189</ID><NAME>BC3-14</NAME><STATE>2</STATE><IM_MAD><![CDATA[kvm]]></IM_MAD><VM_MAD><![CDATA[kvm]]></VM_MAD><VN_
MAD><![CDATA[ovswitch]]></VN_MAD><LAST_MON_TIME>1426598394</LAST_MON_TIME><CLUSTER_ID>100</CLUSTER_ID><CLUSTER>RHEL</CLUSTER><HOST_SHARE><DISK_USAGE>0</
DISK_USAGE><MEM_USAGE>13107200</MEM_USAGE><CPU_USAGE>700</CPU_USAGE><MAX_DISK>552960</MAX_DISK><MAX_MEM>49284252</MAX_MEM><MAX_CPU>2400</MAX_CPU><FREE_D
ISK>483524</FREE_DISK><FREE_MEM>30236108</FREE_MEM><FREE_CPU>2378</FREE_CPU><USED_DISK>69437</USED_DISK><USED_MEM>19048144</USED_MEM><USED_CPU>21</USED_
CPU><RUNNING_VMS>4</RUNNING_VMS><DATASTORES></DATASTORES></HOST_SHARE><VMS><ID>68482</ID><ID>72751</ID><ID>72904</ID><ID>72929</ID></VMS><TEMPLATE><ARCH
><![CDATA[x86_64]]></ARCH><CPUSPEED><![CDATA[2793]]></CPUSPEED><HOSTNAME><![CDATA[bc3-14]]></HOSTNAME><HYPERVISOR><![CDATA[kvm]]></HYPERVISOR><MODELNAME
><![CDATA[Intel(R) Xeon(R) CPU           X5660  @ 2.80GHz]]></MODELNAME><NETRX><![CDATA[0]]></NETRX><NETTX><![CDATA[0]]></NETTX><RESERVED_CPU><![CDATA[]
]></RESERVED_CPU><RESERVED_MEM><![CDATA[]]></RESERVED_MEM><VERSION><![CDATA[4.10.2]]></VERSION></TEMPLATE></HOST>',2,1426598394,0,0,1,0,0,100), error 12
13 : Deadlock found when trying to get lock; try restarting transaction

Tue Mar 17 09:42:04 2015 [Z0][ONE][E]: SQL command was: REPLACE INTO datastore_pool (oid, name, body, uid, gid, owner_u, group_u, other_u, cid) VALUES (
144,'RHEL System vol2','<DATASTORE><ID>144</ID><UID>4</UID><GID>0</GID><UNAME>dchebota</UNAME><GNAME>oneadmin</GNAME><NAME>RHEL System vol2</NAME><PERMI
SSIONS><OWNER_U>1</OWNER_U><OWNER_M>1</OWNER_M><OWNER_A>0</OWNER_A><GROUP_U>1</GROUP_U><GROUP_M>0</GROUP_M><GROUP_A>0</GROUP_A><OTHER_U>0</OTHER_U><OTHE
R_M>0</OTHER_M><OTHER_A>0</OTHER_A></PERMISSIONS><DS_MAD><![CDATA[-]]></DS_MAD><TM_MAD><![CDATA[shared]]></TM_MAD><BASE_PATH><![CDATA[/var/lib/one//data
stores/144]]></BASE_PATH><TYPE>1</TYPE><DISK_TYPE>0</DISK_TYPE><CLUSTER_ID>100</CLUSTER_ID><CLUSTER>RHEL</CLUSTER><TOTAL_MB>1048576</TOTAL_MB><FREE_MB>9
60656</FREE_MB><USED_MB>87921</USED_MB><IMAGES></IMAGES><TEMPLATE><SHARED><![CDATA[YES]]></SHARED><TM_MAD><![CDATA[shared]]></TM_MAD><TYPE><![CDATA[SYST
EM_DS]]></TYPE></TEMPLATE></DATASTORE>',4,0,1,1,0,100), error 1213 : Deadlock found when trying to get lock; try restarting transaction
2

I’m also hitting this on 4.14.
A solution would be welcome.

3

This should be fixed, maybe this is because something went wrong during the
update. Please, could you send us the schema of the datastore_pool
and host_pool tables in your database? THANKS!

··· On Fri, Feb 26, 2016 at 12:06 PM, valerio_schiavoni wrote:

valerio_schiavoni https://forum.opennebula.io/users/valerio_schiavoni
February 26

I’m also hitting this on 4.14.
A solution would be welcome.

Visit Topic
https://forum.opennebula.io/t/sql-error-1213-deadlock-found-when-trying-to-get-lock-try-restarting-transaction/441/2
or reply to this email to respond

To stop receiving notifications for this particular topic, click here
https://forum.opennebula.io/t/sql-error-1213-deadlock-found-when-trying-to-get-lock-try-restarting-transaction/441/unsubscribe.
To unsubscribe from these emails, change your user preferences
https://forum.opennebula.io/my/preferences

Ruben S. Montero, PhD
Project co-Lead and Chief Architect
OpenNebula - Flexible Enterprise Cloud Made Simple
www.OpenNebula.org | rsmontero@opennebula.org | @OpenNebula

4

If you send the exact command to execute against the MySQL database, I’d be happy to share the schemas !

5

Did you see this?

It is also added to the docs:
http://docs.opennebula.org/4.12/administration/other_subsystems/mysql.html#configuring-mysql

I tried it and as far as now the error has not showed up again.

$ mysql -u root -p
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;