EnПессимистическая блокировка записей в SQL
Konstantin Beliaev, 2003
Довольно часто, люди переходящие на SQL-сервер с файловых баз данных типа Paradox спрашивают, как заблокировать запись, чтоб другие не смогли ее редактировать? Обычно им отвечают, что все нормально: если кто-то другой изменит запись в процессе вашего редактирования, сервер вам об этом сообщит при попытке сохранения (это так называемая "оптимистическая" блокировка).Оптимист считает, что мы живем в лучшем из миров.
Пессимист опасается, что так оно и есть...
На самом деле, это не очень приятно – редактировать запись, чтоб в конце узнать, что кто-то изменил ее раньше нас. Решением этой проблемы могла бы быть "пессимистическая" блокировка записей, однако далеко не все SQL серверы ее поддерживают. Тем не менее, есть как минимум два варианта ее ручной организации (весь код, приведенный далее является схематичным, лишь для пояснения принципов работы приложения).
Первый вариант подразумевает использование транзакционного механизма самого сервера. Наша цель – заставить сервер наткнуться на чужие изменения, так давайте что-нибудь изменим, но не будем подтверждать эти изменения (такой способ используется в FibPlus):
StartTransaction;
update Table1 set Field1=Field1 where RecordKey=:RecordKey;
DoEditRecord(RecordKey); // <- здесь пользователь что-то редактирует
SaveRecord(RecordKey); // <- сохранение в БД
Commit;
update Table1 set Field1=Field1 where RecordKey=:RecordKey;
DoEditRecord(RecordKey); // <- здесь пользователь что-то редактирует
SaveRecord(RecordKey); // <- сохранение в БД
Commit;
Поле Field1 может быть любым, но лучше брать то, что не используется в индексах, дабы не вызывать их лишнюю перестройку. Теперь, если вторая транзакция попытается вклиниться со своим апдейтом, то произойдет lock conflict (заметьте, до передачи записи второму пользователю на редактирование), который будет разруливаться в зависимости от SQL сервера и параметров транзакции (например, wait / no_wait).
Плюсы такого решения: используются встроенные механизмы SQL сервера, никаких дополнительных структур, при обрыве соединения незавершенная транзакция автоматически откатывается и запись освобождается.
Тут правда есть и несколько минусов: большинство серверов не позволяют узнать, кем и когда была заблокирована запись, не все сервера адекватно переваривают длительную незавершенную транзакцию, и для использования этого варианта необходимо иметь возможность напрямую управлять транзакциями сервера (что исключено, например, в случае SOAP приложения).
Второй вариант использует дополнительное поле в таблице или отдельную таблицу, в которой записи помечаются как заблокированные. Теперь процедура работы с записью будет такая:
LockRecord(RecordKey);
DoEditRecord(RecordKey);
SaveRecord(RecordKey);
UnLockRecord(RecordKey);
DoEditRecord(RecordKey);
SaveRecord(RecordKey);
UnLockRecord(RecordKey);
Немного поразмыслив, я пришел к выводу что стоит держать 2 поля: имя заблокировавшего и время установки блокировки, назовем их LockUser и LockTime.
Процедура LockRecord сначала пытается обновить эти поля, при условии что они пустые, а потом проверяет, получилось ли это (именно в этом порядке, иначе нам опять понадобится транзакция):
update Table1 set LockUser=USER, LockTime='NOW'
where RecordKey=:RecordKey and LockUser is null;
select LockUser, LockTime from Table1 where RecordKey=:RecordKey;
if LockUser<>USER
then DoException('Запись заблокирована '+LockUser+' в '+LockTime);
where RecordKey=:RecordKey and LockUser is null;
select LockUser, LockTime from Table1 where RecordKey=:RecordKey;
if LockUser<>USER
then DoException('Запись заблокирована '+LockUser+' в '+LockTime);
UnLockRecord выполняет обратную процедуру, очищает эти поля, при условии что запись заблокирована нами:
update Table1 set LockUser=null, LockTime=null
where RecordKey=:RecordKey and LockUser=USER;
where RecordKey=:RecordKey and LockUser=USER;
Основной минус тут один: запись может висеть в заблокированном состоянии сколько угодно, поэтому желательно наличие какого-нибудь администратора, который может принудительно разблокировать любую запись.
Зато плюсов сразу два: вы видите кем и как давно редактируется эта запись, и можете быть уверены, что уж если вы ее заблокировали, то никто другой ее не изменит (для гарантии можно запретить изменять незаблокированные или заблокированные другим юзером записи на уровне БД, при помощи триггеров или VIEW).
Успехов!