Pessimistic locking vs Serializable transaction isolation level

Question

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?

Lientery

@pringi there's no explanation of difference of the two mentioned in my question – Lientery

Sorry. This one: #22646726 – Disapprobation

@Disapprobation so with locking I won't be able to read data from table and with serializable level I'll be able to read and unable to write in case of two parallel transactions? – Lientery

"...in both cases the table gets locked..." That's not generally true in either case. – Offen

Answer 1

(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.

Corundum

So basically when I set transaction isolation level via SQL it is a directive to DBMS to handle my actions in a specific way, which seems to be the same as pessimistic concurrency control, isn't it? – Lientery

@LeonidBor: Think about it this way. Optimistic and pessimistic concurrency control give you two choices. SQL transaction isolation levels give you four choices. "Concurrency" is one topic; "transaction isolation level" is another topic. Trying to map concurrency directly to transaction isolation levels is probably going to cause trouble in the long run. Especially since transaction isolation levels are defined in negative terms--things that don't happen. – Offen

Answer 2

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

Hawser

Answer 3

Isolation level is a restriction on what data are viewable within a transaction.

Serializable isolation succeeds if only no data has been changed by other transactions prior to commit. The inherent implementation should not generate deadlocks.

Serializable isolation is expensive as additional checks are made to assure consistency. Thus a less restrictive isolation is used and access to critical sections is synchronized through Pessimistic/Optimistic locking.

Pessimistic lock is a try to lock a resource, otherwise transaction fails. This is Fail Fast pattern from the Transaction perspective, this is a true source of dead-locks. This is not a transaction isolation, it is a type of synchronization over concurrently accessed database content.

Optimistic lock is not a lock at all, it is just a version check prior to commit.

Whatsoever