How Pessimistic lock works in database,does Isolation level has to do any thing with it?

Question

I was reading about database locking(pessimistic,optimistic) mechanism,

session 1:
t1: open transaction:
t2: sleep(3 sec)
t5: updte user set name='x' where id =1

session 2: t2:update user set name='y' where id=1

my doubts are: 1. What will happen at t5 2. does It has to do any thing with Isolation level?if yes what will be the behavior in different isolation level. 3. Does database(mysql,oracle) only do pessimistic locking?

Answer 1

Let me answer your questions in a reverse order bacause this way I do not have to repeat certain parts.

3. Since optimistic locking means that the records read in a transaction are not locked, optimistic locks cannot be implemented. You should not really use the term optimistic lock, use optimistic concurrency control instead. The pessimistic locking strategy is the one that involves database level locks, which are implemented by all rdbms that use transactions - including mysql with innodb.

Mysql does not have any database level support for optimistic concurrency control. This does not mean that other rdbms do not support OCC either. You need to check out their manuals.

2. Isolation levels do not affect the outcome of the scenario described in the question, since there is no select there, only 2 atomic updates and the field referenced in the where clause is not updated.

Isolation levels mainly influence how data is read by transactions, not how they can update it.

1. The outcome of the scenario described in the question depends on which session issues the update first and how long that transaction is open. Whichever session executes the update first will make the change and sets an exclusive lock on the index record. The other transaction will not be able to execute the update until the first transaction completes. If the first transaction runs for a long time, then the other one may time out while waiting for the lock to be released.