Одновременно получить (выбрать) или создать (вставить) новую строку в общем SQL без конфликтов

У меня есть система, которая имеет сложный первичный ключ для взаимодействия с внешними системами и быстрый, небольшой непрозрачный первичный ключ для внутреннего использования. Например: внешний ключ может быть составным значением - чем-то вроде (заданное имя (varchar), фамилия (varchar), почтовый индекс (char)), а внутренний ключ будет целым числом («идентификатор клиента»).

Когда я получаю входящий запрос с внешним ключом, мне нужно найти внутренний ключ - и вот сложная часть - выделить новый внутренний ключ, если у меня его еще нет для данного внешнего ID.

Очевидно, что если у меня только один клиент общается с базой данных одновременно, это нормально. ВЫБРАТЬ customer_id ИЗ клиентов ГДЕ given_name = 'foo' И ... , затем ВСТАВИТЬ В ЗНАЧЕНИЯ клиентов (...) , если я не т найти значение. Но если потенциально много запросов поступает из внешних систем одновременно, и многие из них могут поступать для ранее неслыханного клиента сразу, возникает состояние гонки, когда несколько клиентов могут попытаться ВСТАВИТЬ новый row.

Если бы я изменял существующую строку, это было бы легко; просто сначала SELECT FOR UPDATE , чтобы получить соответствующую блокировку на уровне строки, перед выполнением UPDATE . Но в данном случае у меня нет строки, которую я могу заблокировать, потому что эта строка еще не существует!

Пока что я придумал несколько решений, но у каждого из них есть довольно серьезные проблемы:

1. Перехватите ошибку на INSERT , повторите попытку всей транзакции сверху. Это проблема, если в сделке участвует десяток клиентов, особенно если входящие данные потенциально говорят об одних и тех же клиентах в разном порядке каждый раз. Можно застрять во взаимно рекурсивных тупиковых циклах, когда каждый раз конфликт возникает у другого клиента. Вы можете смягчить это с помощью экспоненциального времени ожидания между попытками повторной попытки, но это медленный и дорогой способ разрешения конфликтов. Кроме того, это немного усложняет код приложения, так как все должно быть перезапущено.
2. Используйте точки сохранения. Запустите точку сохранения перед SELECT , перехватите ошибку на INSERT , а затем вернитесь к точке сохранения и снова SELECT . Точки сохранения не являются полностью переносимыми, и их семантика и возможности немного отличаются между базами данных; самая большая разница, которую я заметил, заключается в том, иногда кажется, что они гнездятся, а иногда нет, поэтому было бы неплохо, если бы я мог их избегать. Однако это лишь смутное впечатление - оно неточно? Стандартизированы ли точки сохранения или, по крайней мере, практически согласованы? Кроме того, точки сохранения затрудняют параллельное выполнение операций в той же транзакции, потому что вы, возможно, не сможете точно сказать, сколько работы вы будете откатывать, хотя я понимаю, что мне, возможно, просто нужно жить
3. Получите некоторую глобальную блокировку, например блокировку на уровне таблицы, используя оператор LOCK ( oracle mysql postgres ). Это, очевидно, замедляет эти операции и приводит к большому количеству конфликтов блокировок, поэтому я бы предпочел избежать этого.
4. Получите более детализированную, но специфичную для базы данных блокировку. Я знаком только с Postgres ' способ сделать это , который определенно не поддерживается в других базах данных (функции даже начинаются с " pg_ "), так что это опять же проблема переносимости. Кроме того, способ postgres для этого потребует, чтобы я каким-то образом преобразовал ключ в пару целых чисел, в которые он может не вписываться. Есть ли более лучший способ получить блокировки для гипотетических объектов?

Мне кажется, что это обычная проблема параллелизма с базами данных, но мне не удалось найти для этого много ресурсов; возможно, просто потому, что я не знаю канонической формулировки. Можно ли сделать это с помощью простого дополнительного синтаксиса, Австралия: 2 человека Германия: 2 человека. Если мы посчитаем процентное соотношение каждого значения к сумме по всему списку, мы получим: ...

Предположим, у нас есть список элементов с целым числом:

USA:       3 people
Australia: 2 people
Germany:   2 people

Если мы посчитаем процентное соотношение каждого значения к сумме по всему списку, мы получим:

USA:       3/(3+2+2)*100 = 42.857...%
Australia: 2/(3+2+2)*100 = 28.571...%
Germany:   2/(3+2+2)*100 = 28.571...%

и если мы округлим его, мы получим :

USA:       43%
Australia: 29%
Germany:   29%

Сумма 43 + 29 + 29 = 101 не равна 100, и это выглядит немного странно для пользователя программы. Как бы вы решили эту проблему?