What are pessimistic and optimistic locking? How to obtain a pessimistic lock without changing the isolation level?

Pessimistic locks assume that conflicts when accessing data are likely, so the lock is acquired immediately upon reading or writing to prevent other transactions from making changes.

Optimistic locks assume that conflicts are rare, so data is read without locks, and conflict checking occurs at the time of writing (for example, through version numbers).

To obtain a pessimistic lock without changing the isolation level, explicit locks can be used at the SQL query level, for example, in JDBC:

// Obtaining a pessimistic lock on a row
String sql = "SELECT * FROM table WHERE id = ? FOR UPDATE";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();

Here, FOR UPDATE locks the selected rows for other transactions without changing the global transaction isolation level. This allows implementing a pessimistic lock locally for a specific query.