Swv,

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

У Oracle есть механизм select... for update skip locked.

Правда я вообще не понимаю откуда в этой задаче могут быть блокировки и зачем они автору, если ему просто нужно раздать клиентам из таблички данные которых у них еще нет, то все гораздо проще, либо таймстемп, либо завести change number, соответственно клиент запрашивает все данные change number которых больше чем change number последнего сообщения полученного клиентом. Если проблема в попытках проапдейтить таблицу шапки, так не надо класть последний таймстемп/change number в нее.



Когда сообщение взято из очереди его больше там нет, внезапно кому то захочется перечитать все сообщения за последние сутки (данные на клиенте были потеряны), куда очередь прикладывать?

Создайте таблицу пользователь - чат - номер сообщения, строка таблицы является семафором, сообщения добавляются по следующему алгоритму: транзакция открывается, update строки с номером сообщения - инкремент номера, insert в таблицу сообщений нового сообщения с полученным на предыдущем шаге номером, транзакция закрывается. Транзакции через семафор, быстрые и короткие, никаких дедлоков быть не должно. На клиенте хранится номер последнего принятого сообщения, если он отличается от номера сообщения в таблице "пользователь - чат - номер сообщения" (читатели не должны блокировать писателей), значит есть новые сообщения. У пользователя может быть не одно устройство с клиентской частью чата, на разных устройствах может быть разное состояние, т.е. последнее полученное сообщение и соответственно его номер может отличаться.