‘select … for update’ получает ‘row share lock’ на табличку. Вот этот лок конфликтует с ‘exclusive’ локом, который нужен для ‘update’ и это наверное то шо тебе нужно, если я правильно понял.

select for update так же с таким же select for update конфликтует - те не получается сделать именно группу воркеров против другой группы воркеров

немного отредактировал исходный пост чтобы было более понятно

1. читать из таблицы блокировок первую строку

2а. если в ней значение группы = текущая группа или нет строк, начать транзакцию, добавить в таблицу блокировок строку с номером процесса и текущей группой, закончить транзакцию, выполнить работу, удалить строку с номером процесса из таблицы блокировок

2б. иначе прервать процесс

или нет строк

для этого нужно сначала lock table сделать чтобы гонки не было, кроме того процесс может умереть и не убрать за собой. проверять его pid? он мог умереть и его pid мог взять другой (если очень долго не проверять)

я придумал другой вариант:

заводим вспомогательную строку как в «mutex» (положим строка в таблице одна для простоты). в строке имя группы, например A или B.

любой процесс:

1) начинаем транзакцию
2) делаем select group_name from semaphore for share;
3) если группа наша - работаем, завершаем транзакцию и выходим
4) если группа не наша - делаем select group_name from semaphore for update skip locked;
5) если ничего не вернулось значит чужие воркеры есть - завершаем транзакцию и выходим
6) если вернулось значение значит чужих воркеров нет - значит мы получили лок.
7) делаем update строки меняя имя группы, завершаем транзакцию и выходим (не работаем чтобы не блокировать коллег по своей группе)

исправленный вариант:

заводим вспомогательную строку как в «mutex» (положим строка в таблице одна для простоты). в строке имя группы, например A или B.

любой процесс:

1) начинаем транзакцию
2) делаем select group_name from semaphore;
3) получаем группу
4) если группа не наша - переходим к шагу 8
5) пока группа наша, лочим - select group_name from semaphore for share;
6) получаем группу
4) если группа не наша - была гонка, завершаем транзакцию и выходим
7) группа наша - работаем, завершаем транзакцию и выходим
8) группа не наша - делаем select group_name from semaphore for update skip locked;
9) получаем группу
10) если ничего не вернулось значит чужие воркеры есть - завершаем транзакцию и выходим
11) если вернулось значение значит чужих воркеров нет - значит мы получили лок.
12) делаем update строки меняя имя группы, завершаем транзакцию и выходим (не работаем чтобы не блокировать коллег по своей группе)

тут есть два минуса:
1) периодический update строки. даже если строка в таблице одна - индекс будет пухнуть. хотя индекс можно вообще удалить в таком случае
2) воркеры одной группы простаивают пока работают воркеры другой группы. это серьезная ботва. нужно менять поведение одной группы воркеров вместо того чтобы делить воркеры на две группы

в пункте 5) то-же skip locked

периодический update строки.

это решается добавлением еще специальных «опорных» строк для каждой из групп чтобы каждая из групп пыталась лочить своего врага, тогда собственно update не нужен

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

Почему такое требование средствами SQL? Можно просто использовать распределенный манагер блокировок.

Я делал примерно по мотивам сообщения Монка, при этом для работы с таблицами блокировок блокировал её эксклюзивно на короткое время с таймаутом, в автономной, что ли транзакции. Или на клиенте.

Да, если клиент умер, то нужно снимать блокировку руками. Блокировка таблицы блокировок снимется сама, а вот блокировка группы воркеров - не снимется.

Т.е. это уже не чисто средствами SQL. Есть впечатление, что голым SQL этого не сделать. И что в общем случае оно всё равно может работать ненадёжно.

Во имя Грязного Хака: ethernet-style

Спросить о «чужих» в списке присоединённых клиентов(нужно как-то отличать, например по логинам или адресам). Если никого нет - работать. Иначе - отсоединиться и проспать случайное время перед повторной попыткой.