1. Optimistic Lock
1.1 Без изменения схемы БД
Выборка: select ID, FIRST_NAME, LAST_NAME from PERSON
Обновление: update PERSON set FIRST_NAME = :new_FIRST_NAME, LAST_NAME = :new_LAST_NAME where ID = :id and FIRST_NAME = :old_FIRST_NAME and LAST_NAME = :old_LAST_NAME

if (affectedRows == 0) {
   throw new Exception("Данные были изменены со времени выборки");
}

1.2 С изменением схемы БД
Добавление колонки VERSION INTEGER
Выборка: select ID, FIRST_NAME, LAST_NAME, VERSION from PERSON
Обновление: update PERSON set FIRST_NAME = :new_FIRST_NAME, LAST_NAME = :new_LAST_NAME, VERSION = VERSION + 1 
where ID = :id and VERSION = :old_VERSION

if (affectedRows == 0) {
   throw new Exception("Данные были изменены со времени выборки");
}

2. PESSIMISTIC LOCK
1. С изменением схемы (таблицы БД)
Выборка:
select ID, NAME, LOCKED_ON, LOCKED_BY from PERSON
update PERSON set LOCKED_BY='me', LOCKED_ON = SYSTIMESTAMP
where ID = :id and LOCKED_BY IS NULL;
if (affectedRows == 0) {
   throw new Exception("Строка заблокирована");
}

Обновление:
update PERSON set FIRST_NAME = :new_FIRST_NAME, LAST_NAME = :new_LAST_NAME,
LOCKED_BY = NULL, LOCKED_ON = NULL
where ID = :id and LOCKED_BY='me'

2. Без изменения таблицы
Выборка данных:
select * from PERSON
try {
  insert into PERSON_LOCK(LOCK_ID, PERSON_ID, LOCKED_BY, LOCKED_ON) value (seq.nextval, :person_id, 'me', SYSTIMESTAMP);
} catch (UniqueConstaintViolation) {
   throw new Exception ("Строка заблокирована")
}

Обновление данных:
update PERSON set NAME = :new_NAME 
where ID = :id and exists (
   select * from PERSON_LOCK where PERSON_ID = :id and LOCKED_BY = 'me'
)

if (affectedRows == 0) {
   throw new Exception("Строка заблокирована");
}

delete from PERSON_LOCK where PERSON_ID = :id and LOCKED_BY = 'me'