I kinda understand the purpose of entity locking and transaction isolation level, but can't get the difference between pessimistic locking and serializable level. As I understand, in both cases the table gets locked and no other transaction can access it, so in both cases actions to prevent concurrent modifications are taken by the DB, which looks like there's no difference. Could someone please explain if there actually is difference here?
2 Answers
(I don't assume you're using ObjectDB. You'll probably get better answers if you edit your question, and include the specific database you're using with JPA.)
I don't like the terms optimistic locking and pessimistic locking. I think optimistic concurrency control and pessimistic concurrency control are more accurate. Locks are the most common way to deal with concurrency control problems, but they're not the only way. (Date's chapter on concurrency in An Introduction to Database Systems is about 25 pages long.)
The topics of transaction management and concurrency control aren't limited to the relational model of data or to SQL database management systems (dbms). Transaction isolation levels have to do with SQL.
Pessimistic concurrency control really means only that you expect the dbms to prevent other transactions from accessing something when the dbms starts processing your request. Behavior is up to the dbms vendor. Different vendors might prevent access by locking the entire database, locking some tables, locking some pages, or locking some rows. Or the dbms might prevent access in some other way that doesn't directly involve locks.
Transaction isolation levels are how SQL tries to solve concurrency control problems. Transaction isolation levels are defined in SQL standards.
The serializable transaction isolation level guarantees that the effect of concurrent, serializable transactions is the same as running them one at a time in some particular order. The guarantee describes the effect--not any particular kind of concurrency control or locking needed to achieve that effect.
Pessimistic locking normally involves writelocks to the database to do changes in a safe and exclusive way. This is normally done by doing select ... for update. This will prevent or delay other connections from doing their own select ... for update or changes on the locked records in the database until the transaction of the first connection is completed.
Serializable Isolation Level does not need to be concerned with changes but makes sure that after the transaction started, the result of reads will always stay the same (except changes by the transaction itself) until that transactions ends. To support this "Non-MVCC"-DBMS must set many locks (on each record read by the connection working serializable) in the database and therefore might hinder concurrency very much.
The same effect can also be achieved without locking when databases provide MVCC as do Oracle, MySql-INNODB, MariaDB, Postgres
Related