Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction while inserting and then commiting data in temporary table

[Shem]

Dear Friends

Kindly look out below problem and help me.

I got the deadlock error like "Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction" while executing below function in mysql version 5.6.23 in galera cluster.
If same function gets execute without cluster then no error is coming.
If I executes only statements (and not whole function) which are written in function then also no such error is coming.
below is a  example which replicate my actual function.

-- CREATE SYNTAX

CREATE FUNCTION new_function() RETURNS int(11)
BEGIN

DROP TEMPORARY TABLE IF EXISTS test.TEMP_1;

CREATE TEMPORARY TABLE test.TEMP_1 (test_code VARCHAR(6));

INSERT INTO test.TEMP_1 values ('D12432');
-- with some other operations

DROP TEMPORARY TABLE test.TEMP_1;

INSERT INTO test.TEMP_2 
select * from TEMP_1;

RETURN 1;
END

-- CALLING SYNTAX
SELECT test.new_function();
commit;

[Philip Stoev]

Hello,

I tried to reproduce the problem using the example stored function, however
unfortunately I could not get it to work. The example does not create the
TEMP_2 table at all, and drops the TEMP_1 table just prior to SELECT-ing
from it.

A more complete example would be much appreciated.

Thank you.

Philip Stoev

--
You received this message because you are subscribed to the Google Groups
"codership" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shem]

Hello,
Sorry for improper example. plz refer below example. I m using mysql 5.6.23 with galera cluster 3. plz note that during execution of this function, auto commit is false so manually commit is require after fuction execution as per given below example.

-- CREATE SYNTAX

CREATE FUNCTION test.new_function() RETURNS int(11)

BEGIN

DROP TEMPORARY TABLE IF EXISTS test.TEMP_1;

CREATE TEMPORARY TABLE test.TEMP_1 (test_code VARCHAR(6));

INSERT INTO test.TEMP_1 values ('D12432');
-- with some other operations

DROP TEMPORARY TABLE test.TEMP_1;

RETURN 1;
END

-- CALLING SYNTAX
SELECT test.new_function();
commit;