We have a simple postgres database:

book:   id: primary key, integer   title: varchar   borrowed: boolean   borrowed_by_user_id: foreign key user.id  user:   id: primary key, integer   name: varchar   blocked: boolean 

And now I would like to “borrow a book” b to a user u.

So, I make a transaction: (pseudocode)

Let’s assume READ COMMITED isolation level.

b.id = 1, u.id = 1  begin transaction; book = select for update book where id = 1 user = select user where id = 1  if book.borrowed and not user.blocked: (*)     update book.borrowed = true, book.borrowed_by_user_id = 1 else:    error -> book is taken or user is blocked  commit; 

and it doesn’t work. Because it allows to borrow a book to blocked user. When? For example if an admin would blocked user u after executing statement (*) So, let’s to repair it:

begin transaction; book = select for update book where id = 1 user = select *for update* user where id = 1  if book.borrowed and not user.blocked: (*)     update book.borrowed = true, book.borrowed_by_user_id = 1 else:    error -> book is taken or user is blocked  commit; 

But, it introduces a deadlock.

How to resolve problems like that?