Race condition when creating records in an empty table

[mybaldbird]

I have a model DiskStatus which acts_as_nested_set. After tracing some downstream issues I noticed a race condition occurred when creating two DiskStatuses in concurrent threads. This is what I found in my log:

14:25:50.783870 [Thread 1] DiskStatus Load (3.1ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
14:25:50.784446 [Thread 1] CACHE (0.0ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1  [["LIMIT", 1]]
14:25:50.785998 [Thread 1] SQL (0.3ms)  INSERT INTO `disk_statuses` (`snapshot_id`, `disk_id`, `capacity`, `disk_parent_id`, `capture_type`, `lft`, `rgt`, `created_at`, `updated_at`) VALUES (25, 4, 25769802240, 2, 1, 1, 2, '2017-05-09 18:25:50', '2017-05-09 18:25:50')
14:25:50.787259 [Thread 2] DiskStatus Load (3.5ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
14:25:50.787984 [Thread 2] DiskStatus Load (0.3ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
14:25:50.789614 [Thread 2] SQL (0.5ms)  INSERT INTO `disk_statuses` (`snapshot_id`, `disk_id`, `capacity`, `disk_parent_id`, `capture_type`, `lft`, `rgt`, `created_at`, `updated_at`) VALUES (26, 3, 34356994560, 1, 1, 1, 2, '2017-05-09 18:25:50', '2017-05-09 18:25:50')

Note that the lft and rgt columns receive the same values for both inserts. Compare this with a different occasion where the race condition did not happen:

12:15:24.709600 [Thread 1] DiskStatus Load (0.4ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
12:15:24.710455 [Thread 1] DiskStatus Load (0.2ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
12:15:24.712112 [Thread 1] SQL (0.4ms)  INSERT INTO `disk_statuses` (`snapshot_id`, `disk_id`, `capacity`, `disk_parent_id`, `capture_type`, `lft`, `rgt`, `created_at`, `updated_at`) VALUES (23, 4, 25769802240, 2, 1, 1, 2, '2017-05-09 16:15:24', '2017-05-09 16:15:24')
... SNIP ...
12:15:24.800331 [Thread 2] DiskStatus Load (0.4ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` ORDER BY `disk_statuses`.`rgt` desc LIMIT 1
12:15:24.801546 [Thread 2] DiskStatus Load (0.2ms)  SELECT  `disk_statuses`.* FROM `disk_statuses` WHERE `disk_statuses`.`id` = 23 LIMIT 1 FOR UPDATE
12:15:24.803239 [Thread 2] SQL (0.2ms)  INSERT INTO `disk_statuses` (`snapshot_id`, `disk_id`, `capacity`, `disk_parent_id`, `capture_type`, `lft`, `rgt`, `created_at`, `updated_at`) VALUES (24, 3, 34356994560, 1, 1, 3, 4, '2017-05-09 16:15:24', '2017-05-09 16:15:24')

It looks like a lock is only placed on a record if one is found, which normally allows for atomic creation. But when no record is found, there doesn't seem to be any locking mechanism occurring, which can result in the scenario shown in the first log snippet.

This is probably just a missed edge case when implementing #70.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.