"Select For Update" vs Select
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ı
şöyleBoth 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
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
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
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 ?
## 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';
}
// 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ızupdate booking set user_id = '123' and booked = 't' where seats = 'H3' and booked = 'f';
Örnek
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();
}
