-
Improvement
-
Resolution: Unresolved
-
Minor
-
None
-
5.0
-
None
There are cases where record id does not matter, plus there is some unique constraint and records might be added concurrently, see https://tracker.moodle.org/browse/MDL-84070
I think that it would be better to add a new method to DML that inserts or updates record depending on its existence.
- MariaDB has https://mariadb.com/kb/en/insert-on-duplicate-key-update/
- PostgreSQL has similar in https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
- MS SQL Server and Oracle - we could use the workaround from
MDL-84070or https://www.geeksforgeeks.org/upsert-operation-in-sql-server/
example use:
$preference = new stdClass(); |
$preference->userid = $user->id;
|
$preference->name = $name;
|
$preference->value = $value;
|
$DB->upsert_record('user_preferences', $preference, ['userid', 'name']); |
Compared to:
$retry = 0; |
$saved = false; |
|
|
while (!$saved && $retry++ < 2) { |
if ($preference = $DB->get_record('user_preferences', ['userid' => $user->id, 'name' => $name])) { |
if ($preference->value === $value && isset($user->preference[$name]) && $user->preference[$name] === $value) { |
// Preference already set to this value. |
return true; |
}
|
$DB->set_field('user_preferences', 'value', $value, ['id' => $preference->id]); |
$saved = true; |
} else { |
$preference = new stdClass(); |
$preference->userid = $user->id;
|
$preference->name = $name;
|
$preference->value = $value;
|
try { |
$DB->insert_record('user_preferences', $preference); |
$saved = true; |
} catch (dml_write_exception $e) { |
// We have an insert race, so just ignore and try again. |
$saved = false; |
}
|
}
|
}
|
Benefits should hopefully be:
- faster - 1 db request only for PostgreSQL and MariaDB/MySQL
- safer - concurrent inserts should not cause errors
- easier to read code
- duplicates
-
-
- Open
-