MySQL deadlock

Hi,

During the loading test (50 users at same time) of our application, we
encountered deadlocks when we execute the save-final action :

<property as="xs:string" name="oxf.fr.detail.process.send.egov.*">
                save-final
                then send(
                        uri =
"http://localhost:9080/eDMS/eFormsPersistenceLayer/storeXml?dossierId={xxf:get-request-parameter('dossierId')}&amp;dataType=data",
                        parameters = "app form document form-version
language",
                        content = "metadata",
                        method = "PUT"
                )
                then result-dialog
                recover error-message("database-error")
        </property>

Deadlock seems occur when Orbeon is saving index information :

*** (1) TRANSACTION:
TRANSACTION 29302424, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 2936, 2 row lock(s)
MySQL thread id 591, OS thread handle 0x7f2581efa700, query id 163000
localhost 127.0.0.1 orbeon updating

*DELETE FROM orbeon_i_current
      WHERE document_id = '0030a453ffe4660d8fd2cb03258774f0bacb7cbf'   AND
            draft       = 'Y'*

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1062 page no 5 n bits 208 index `orbeon_i_current_i1`
of table `orbeon_egov_sbk`.`orbeon_i_current` trx id 29302424 lock_mode X
locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 14; compact format; info
bits 0
0: len 4; hex 80000104; asc     ;;
1: len 1; hex 4e; asc N;;
2: len 6; hex 000001beb7be; asc       ;;
3: len 7; hex 1f000001f10982; asc        ;;
4: len 7; hex 583c2bb4000000; asc X<+    ;;
5: len 7; hex 583c2feb000000; asc X</    ;;
6: len 9; hex 73626b6f7262656f6e; asc sbkorbeon;;
7: len 9; hex 73626b6f7262656f6e; asc sbkorbeon;;
8: len 18; hex 65706d732d61646d696e6973747261746f72; asc
epms-administrator;;
9: len 6; hex 6f7262656f6e; asc orbeon;;
10: len 7; hex 6275696c646572; asc builder;;
11: len 4; hex 80000001; asc     ;;
12: len 30; hex
643434646434383662323430323761336236363366373636663462373533; asc
d44dd486b24027a3b663f766f4b753; (total 40 bytes);
13: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 29302426, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
10 lock struct(s), heap size 1184, 37 row lock(s)
MySQL thread id 589, OS thread handle 0x7f2582db4700, query id 163009
localhost 127.0.0.1 orbeon preparing

&lt;b>DELETE FROM orbeon_i_control_text WHERE data_id IN (
   SELECT data_id
     FROM orbeon_i_current
  WHERE document_id = '7525e79da873b39dbc2d88861f83555ccf10851b'
)*

We are using MySQL as persistence layer via the following configuration in
properties-local.xml :

 <property as="xs:string" name="oxf.fr.persistence.provider.*.*.*"
value="mysql" />
 <property as="xs:string" name="oxf.fr.persistence.mysql.datasource"
value="mysql-orbeon" />
 <property as="xs:boolean" name="oxf.fr.persistence.mysql.autosave"
value="false" />


We have tried the following action :

1) Execute the save-final but don't send xml to the webservice : *no
deadlock*
2) Send the xml to the webserevice but don't execute save-final : *no
deadlock*
3) Change the persistence layer to use existdb : *no deadlock*
4) Change the perstence layer to save form data in existdb and form
definition in MySQL : *no deadlock*

Looking on google I found that it might be due to an index problem but it
seems ok :

mysql> SHOW INDEX FROM orbeon_i_current;
+------------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name            | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment | Index_comment |
+------------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orbeon_i_current |          0 | orbeon_i_current_i1 |            1 |
data_id     | A         |        1807 |     NULL | NULL   |      | BTREE    
|         |               |
| orbeon_i_current |          0 | orbeon_i_current_i1 |            2 | draft      
| A         |        1807 |     NULL | NULL   |      | BTREE      |        
|               |
| orbeon_i_current |          1 | orbeon_i_current_i2 |            1 |
data_id     | A         |        1807 |     NULL | NULL   |      | BTREE    
|         |               |
+------------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


mysql> SHOW INDEX FROM orbeon_i_control_text;
+-----------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name                 |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |
Null | Index_type | Comment | Index_comment |
+-----------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orbeon_i_control_text |          1 | orbeon_i_control_text_i1 |          
1 | data_id     | A         |         245 |     NULL | NULL   |      | BTREE    
|         |               |
+-----------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

Thank you for you precious help


--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Thank you for the detailed report. I will look into it this coming week, and
will then follow-up on this thread.

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Thank you for your help.

A simple workaround is to disable indexation because we don't use this
functionality but I don't know if is it possible?

--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

UP thanks.

--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

It isn't: you can't bypass the indexes. Orbeon Forms really relies on those
index tables being present.

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

I also tried with only one form definition in database and there's it works
...

--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Hi Vincent,

I am looking at the 2 queries here, which I copied below for reference, and
I'm not sure how we could have a deadlock between them. I understand they
could both be using an index on `orbeon_i_current`, but essentially
whichever one managed to get a lock on that index first should complete, and
then in turn the other one should complete. Are you sure that those are the
only 2 queries?

Also, I'm puzzled by your first point where you say you're only getting a
deadlock when you're also doing a `send()` to your service. Does your
service access the same database? Does it call the Orbeon Forms REST API?


DELETE FROM orbeon_i_current
WHERE
    document_id = '0030a453ffe4660d8fd2cb03258774f0bacb7cbf' AND
    draft = 'Y'

DELETE FROM orbeon_i_control_text WHERE data_id IN (
    SELECT data_id
    FROM orbeon_i_current
    WHERE document_id = '7525e79da873b39dbc2d88861f83555ccf10851b'
)

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Hello,

Yes my webservice is calling Orbeon Forms REST API to retrieve form
definition metadata by calling this url : /persistence/form/egov/jmeter
(jmeter is the form name that I created for my loading test). I made a cache
to improve performance by not calling this Orbeon Form REST API at every
calls.

--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Hi Vincent,

Interesting: you're saving the data, and then potentially calling the API to
read a form definition. So you're doing a `GET` call the persistence API,
right? In that case, I'm surprised that the 2 queries involved in the
deadlock are both `DELETE`, as your `GET` wouldn't result in any `DELETE`
being issued. Are you sure there are no other SQL queries involved in the
deadlock?

If not, I would recommend that you connect a to app with a debugger (e.g.
IntelliJ), reproduce the deadlock, and then check what thread exactly is
blocked where in the code. Is this something you would be able to do do? (Or
do you by any chance have a PE subscription, in which case we might be able
to provide further help on this?)

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Hi Vincent,

Is this still a current issue for you? If so, did you get a chance to make
any progress on this, and maybe try some of the suggestions in my previous
message?

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].