mahara-contributors team mailing list archive

[Aaron Wells <1514608@xxxxxxxxxxxxxxxxxx>]

Okay, I was able to replicate this locally by hand, by having multiple
users copy the same collection at the same time, and adding a command of
"sleep(1)" into the execute_sql() function in lib/dml.php to increase
the amount by which the queries overlap.

1. Set up a Mahara site with five users.
2. Have one of the users create a collection with several pages and artefacts.
3. Set the collection so that it is shared with all registered users, and it can be copied, and the owner retains view access to copies.
4. In four browser simultaneously:
4.1. Log in as one of the other four users.
4.2. View the copyable collection
4.3. Click the "copy" button (but don't yet click "Collection" in the confirmation popup)
5. Edit lib/dml.php, adding "sleep(1);" on line 138, inside of the execute_sql() function
6. In each of the four browsers in quick succession, click the "Collection" button.

Expected result: It should copy the collection successfully in all four sessions.
Actual result: Some of the sessions get a deadlock error message. In my case, two of them did, and they both deadlocked while trying to insert into the "view_rows_columns" table.

[WAR] d9 (lib/errors.php:747) Failed to get a recordset: mysqli error: [1213: Deadlock found when trying to get lock; try restarting transaction] in adodb_throw(INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (64, 1, 3), )
Command was: INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (?, ?, ?) and values was (64,1,3)
Call stack (most recent first):
log_message("Failed to get a recordset: mysqli error: [1213: De...", 8, true, true) at /home/aaronw/www/mahara/htdocs/lib/errors.php:97
log_warn("Failed to get a recordset: mysqli error: [1213: De...") at /home/aaronw/www/mahara/htdocs/lib/errors.php:747
SQLException->__construct("Failed to get a recordset: mysqli error: [1213: De...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:1055
insert_record("view_rows_columns", object(stdClass)) at /home/aaronw/www/mahara/htdocs/lib/view.php:756
View->commit() at /home/aaronw/www/mahara/htdocs/lib/view.php:581
View::_create(array(size 5), "4") at /home/aaronw/www/mahara/htdocs/lib/view.php:377
View::create_from_template(array(size 5), "12", "4", true, false) at /home/aaronw/www/mahara/htdocs/lib/collection.php:286
Collection::create_from_template(array(size 3), 1) at /home/aaronw/www/mahara/htdocs/lib/view.php:6141
copyview("6", 0, null, 1) at /home/aaronw/www/mahara/htdocs/view/copy.php:32
[WAR] d9 (lib/errors.php:747) Failed to get a recordset: mysqli error: [1452: Cannot add or update a child row: a foreign key constraint fails ("mahara"."view_rows_columns", CONSTRAINT "viewrowscolu_vie_fk" FOREIGN KEY ("view") REFERENCES "view" ("id"))] in adodb_throw(INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (64, 1, 3), )
Command was: INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (?, ?, ?) and values was (64,1,3)
Call stack (most recent first):
log_message("Failed to get a recordset: mysqli error: [1452: Ca...", 8, true, true) at /home/aaronw/www/mahara/htdocs/lib/errors.php:97
log_warn("Failed to get a recordset: mysqli error: [1452: Ca...") at /home/aaronw/www/mahara/htdocs/lib/errors.php:747
SQLException->__construct("Failed to get a recordset: mysqli error: [1452: Ca...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:1055
insert_record("view_rows_columns", object(stdClass)) at /home/aaronw/www/mahara/htdocs/lib/view.php:756
View->commit() at /home/aaronw/www/mahara/htdocs/lib/view.php:704
View->__destruct() at /home/aaronw/www/mahara/htdocs/lib/view.php:377
View::_create(array(size 5), "4") at /home/aaronw/www/mahara/htdocs/lib/view.php:377
View::create_from_template(array(size 5), "12", "4", true, false) at /home/aaronw/www/mahara/htdocs/lib/collection.php:286
Collection::create_from_template(array(size 3), 1) at /home/aaronw/www/mahara/htdocs/lib/view.php:6141
copyview("6", 0, null, 1) at /home/aaronw/www/mahara/htdocs/view/copy.php:32
[WAR] d9 (lib/dml.php:1055) Failed to get a recordset: mysqli error: [1452: Cannot add or update a child row: a foreign key constraint fails ("mahara"."view_rows_columns", CONSTRAINT "viewrowscolu_vie_fk" FOREIGN KEY ("view") REFERENCES "view" ("id"))] in adodb_throw(INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (64, 1, 3), )
Command was: INSERT INTO "view_rows_columns" ("view", "row", "columns") VALUES (?, ?, ?) and values was (64,1,3)
Call stack (most recent first):
insert_record("view_rows_columns", object(stdClass)) at /home/aaronw/www/mahara/htdocs/lib/view.php:756
View->commit() at /home/aaronw/www/mahara/htdocs/lib/view.php:704
View->__destruct() at /home/aaronw/www/mahara/htdocs/lib/view.php:377
View::_create(array(size 5), "4") at /home/aaronw/www/mahara/htdocs/lib/view.php:377
View::create_from_template(array(size 5), "12", "4", true, false) at /home/aaronw/www/mahara/htdocs/lib/collection.php:286
Collection::create_from_template(array(size 3), 1) at /home/aaronw/www/mahara/htdocs/lib/view.php:6141
copyview("6", 0, null, 1) at /home/aaronw/www/mahara/htdocs/view/copy.php:32

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1514608

Title:
  MySQL deadlock issues with simultaneous users

Status in Mahara:
  In Progress

Bug description:
  The patch in Bug 1492919 resolved the deadlock issues for some users,
  but others are still reporting that they are getting deadlocks. This
  seems to be an issue unique to MySQL, and it can happen with as few as
  2 or 3 concurrent users.

  It has been reported across all stable versions. Strangely, it seems
  to be a relatively recent issue (i.e. we never heard this with Mahara
  1.8 or 1.7, etc), so this suggests it was triggered by something that
  changed fairly recently. One possible candidate is our switch to the
  mysqli driver (before that Mahara wasn't doing transactions at all, in
  mysql).

  See also:
  * https://mahara.org/interaction/forum/topic.php?id=7309
  * https://mahara.org/interaction/forum/topic.php?id=7432

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1514608/+subscriptions