Comment 4 for bug 1492919

Revision history for this message
Russell Boyatt (russell-boyatt) wrote :

To summarise what I've posted in the forum messages, we've looked at this issue with multiple versions of Mahara and different database platforms.

Using a JMeter test plan that:

1) GET main index page
2) POST a login,
3) GET list of portfolios pages, i.e. /view/index.php
4) GET choose template page, i.e. /view/choosetemplate.php
5) POST to /view/choosetemplate.php the action equivalent to select a page to copy (with all the appropriate form parameters)

Running this against Mahara 1.9, 1.10, or 15.04 and MySQL 5.5.44 - deadlock bug occurs when multiple users copy pages at the same time. It's relatively easy to trigger this, and I've included an example of the deadlock below.

With PostgreSQL it's a different story. Running with either PostgreSQL 9.3 does not produce the deadlock errors, even when scaling up the test to large numbers of simulated users copying pages. Tested with Mahara 15.04 and the current master branch from git. We believe this is also true on PostgreSQL 8.4 (but have done far less testing with that version).

Example MySQL deadlock message (from 'SHOW ENGINE INNODB STATUS'):
***************************************************************************
150916 20:46:31
*** (1) TRANSACTION:
TRANSACTION 1621D, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1024, 5 row lock(s), undo log entries 2
MySQL thread id 222, OS thread handle 0x66480450, query id 9755 localhost mahara update
INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (17, 1, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2207 n bits 80 index "viewrowscolu_vie_ix" of table "maharamaster"."view_rows_columns" trx id 1621D lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 1620F, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
19 lock struct(s), heap size 1024, 12 row lock(s), undo log entries 7
MySQL thread id 219, OS thread handle 0x662ff450, query id 9771 localhost mahara update
INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES ('15', 1, '3')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2207 n bits 80 index "viewrowscolu_vie_ix" of table "maharamaster"."view_rows_columns" trx id 1620F lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 8; hex 800000000000000f; asc ;;
 1: len 8; hex 8000000000000019; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2207 n bits 80 index "viewrowscolu_vie_ix" of table "maharamaster"."view_rows_columns" trx id 1620F lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
***************************************************************************