Select For Update

"Select For Update" vs Select

Açıklaması şöyle

The difference between select and select for update is that select for update locks the rows returned as a response of the query. This means no other transaction can make any changes to those rows till the transaction holding the lock commits/abort.

Burada önemli olan şey tüm transaction'ların "Select For Update" kullanması. Açıklaması şöyle

Both transactions should use the FOR UPDATE locking. If the first transaction doesn’t acquire the write locks, the lost update can still happen.

"Select For Update" vs Repeatable Read

Açıklaması şöyle

When you read a record under Repeatable Read, you get a read-lock, but other transactions can also get a read lock, which might prevent you from making an update later. Using FOR UPDATE informs any other transactions which request a read lock that they should wait until you're finished updating the record.

Eğer Repeatable Read kullanırsak ve iki transaction update işlemi çalıştırırsa, hata olarak şunu alırız.

pq: could not serialize access due to concurrent update

Ama aslında amacımız bir transaction'ın başarısız olması değil, sadece "Lost Update" problemi olmaması. 

"Select For Update" Uses Row Share Lock

Açıklaması şöyle

The select ... for update acquires a ROW SHARE LOCK on a table. This lock conflicts with the EXCLUSIVE lock needed for an update statement, and prevents any changes that could happen concurrently.

Örnek

Bir örnek şöyle

Case 1: Consider you are on Book My Show trying to book tickets for your next movie. You and Mr X selected the same seats for a show and proceeded for checkout. Ideally only one of you should be able to book your ticket. However, is it possible that both of you are able to book the exact same seat ?

Hatalı kod şöyle

## Psuedo code for booking 
if (seatsAvailable(List<Seats> selectedSeats)) {
    bookTickets(selectedSeats);
    sendEmailNotification(customer_id);
}
// These are two individual transactions from db point of view
seatsAvailable() {
  Select booked from booking where seats = 'H3';
  return !booked;
}
bookTickets() {
  update booking set customer_id = '123' and booked = 't' where
  seats = 'H3';
}

Doğru kod için şöyle yaparız

// This is a single transaction from db point of view
begin;
seatsAvailable()-> Select * from booking where seats = 'H3' FOR UPDATE;
bookTickets()-> update booking set customer_id = '123' and booked = 't' where seats = 'H3';
commit;

Eğer atomic yapmak istersek şöyle yaparız

update booking set user_id = '123' and booked = 't' where seats = 'H3' and booked = 'f';

Örnek

Bir örnek şöyle

Case 2: Consider a case where you are trying to purchase goods that are worth more than your current e-wallet balance. Is it possible to successfully complete the transaction thereby making your balance negative ?

Örnek

master_counter tablosundan bir değer çekip, daha sonra payment_code tablosuna bir kayıt eklemek isteyelim. İşimiz bitince de master_counter tablosunu güncelleyelim. Şöyle yaparız.

BEGIN;
SELECT counter FROM master_counter FOR UPDATE; // notice this
// Do Addition to counter in application
// Apply Generator Logic in Application
INSERT INTO payment_code(payment_code) VALUES(generated_value_with_counter);
UPDATE master_counter SET counter=<new_value_from_application>;
COMMIT;

Örnek - Spring

Elimizde şöyle bir kod olsun

@Query(value = "select * from products where id = ?1 for update", nativeQuery = true)
Optional<Product> findByIdWithWriteLock(Long productId);

Şöyle kullanalım

@Transactional
public Optional<Order> placeOrder(Long productId, Long userId) {
  Optional<Product> product = productRepository.findByIdWithWriteLock(productId);
  product.orElseThrow(() -> new RuntimeException("Invalid product id"));

  if (product.get().getAvailableUnits() > 0) {
    productRepository.decrementAvailableUnitsCountBy1(productId);
    Order newOrder = new Order(userId, productId);
    orderRepository.save(newOrder);
    return Optional.of(newOrder);
  }
  return Optional.empty();
}