Transaction Isolation Levels Relation with Locks on Table

Transaction isolation levels relation with locks on table

I want to understand the lock each transaction isolation takes on the table

For example, you have 3 concurrent processes A, B and C. A starts a transaction, writes data and commit/rollback (depending on results). B just executes a SELECT statement to read data. C reads and updates data. All these process work on the same table T.

• READ UNCOMMITTED - no lock on the table. You can read data in the table while writing on it. This means A writes data (uncommitted) and B can read this uncommitted data and use it (for any purpose). If A executes a rollback, B still has read the data and used it. This is the fastest but most insecure way to work with data since can lead to data holes in not physically related tables (yes, two tables can be logically but not physically related in real-world apps =\).
• READ COMMITTED - lock on committed data. You can read the data that was only committed. This means A writes data and B can't read the data saved by A until A executes a commit. The problem here is that C can update data that was read and used on B and B client won't have the updated data.
• REPEATABLE READ - lock on a block of SQL(which is selected by using select query). This means B reads the data under some condition i.e. WHERE aField > 10 AND aField < 20, A inserts data where aField value is between 10 and 20, then B reads the data again and get a different result.
• SERIALIZABLE - lock on a full table(on which Select query is fired). This means, B reads the data and no other transaction can modify the data on the table. This is the most secure but slowest way to work with data. Also, since a simple read operation locks the table, this can lead to heavy problems on production: imagine that T table is an Invoice table, user X wants to know the invoices of the day and user Y wants to create a new invoice, so while X executes the read of the invoices, Y can't add a new invoice (and when it's about money, people get really mad, especially the bosses).

I want to understand where we define these isolation levels: only at JDBC/hibernate level or in DB also

Using JDBC, you define it using Connection#setTransactionIsolation.

Using Hibernate:

<property name="hibernate.connection.isolation">2</property>

Where

• 1: READ UNCOMMITTED
• 2: READ COMMITTED
• 4: REPEATABLE READ
• 8: SERIALIZABLE

Hibernate configuration is taken from here (sorry, it's in Spanish).

By the way, you can set the isolation level on RDBMS as well:

• MySQL isolation level,
• SQL Server isolation level
• Informix isolation level (Personal Note: I will never forget about SET ISOLATION TO DIRTY READ sentence.)

and on and on...

Relationship between Isolation Level and Locks

Well, locks are partly an implementation detail. They are used (in SQL Server) to give you the guarantees that the isolation levels give you according to the standard. For example, SERIALIZABLE gives you as-if single-threaded access to the database. This is done by S-locking all data you have read in order to freeze it.

Locks are not purely a detail because the very details of a locking scheme leak out. They are detectable. Also, you can control locks using hints to create behavior that is not specified in the standard. Usually, one would do that to obtain stronger guarantees than before.

Deadlock in transaction with isolation level serializable

As mysql documentation on SERIALIZABLE isolation level says:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE

The clause on autocommit does not apply here, since you explicitly start a transaction.

This means that in the first scenario both transactions obtain a shared lock on the same record. Then the first transaction (T1) tries to execute an update, which needs an exclusive lock. That cannot be granted, since T2 holds a shared lock. Then T2 tries to update, but cannot due to T1 holding a shared lock.

Whether you use an atomic update or a select ... for update statement to lock records, depends on the application logic you need to apply. If you need to fetch the record's data an do some complex calculations with those before updating the record, the use the select ... for update approach. Otherwise, go for the atomic update.

Does TRANSACTION ISOLATIoN LEVEL SERIALIZABLE create READ lock

TRUNCATE TABLE will acquire a exclusive shema modify lock on the table preventing all users from reading from the table (unless they use TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH(NOLOCK)) and writing to the table (no exceptions for writing). The exclusive lock will be released at COMMIT TRANSACTION.

EDIT: As Martin Smith pointed out in his comment below the truncate table will acquire a schema modify lock. Meaning there are no other user will be able to read or modify the table whatsoever until a commit or rollback has taken place.

Prevent lost updates with high transaction isolation levels: Is this a common misconception?

The problem here is that you are asking what Isolation Level, as defined by the SQL Standard, is needed to sort out a concurrency anomaly that is not part of this definition.

SQL Standard only defines how Isolation Levels (Read Uncommited, Read Commited, Repeatable Read, Serializable) map to Dirty Read, Non-Repeatable Read and Phantom Read anomalies. No mention of Lost-Update, so this - as you rightly pointed out - depends on how isolation levels are implemented by a specific DBMS.

Supposedly REPEATABLE_READ is enough to prevent Lost-Update on PostgreSQL and SERIALIZABLE is needed to prevent it on MySQL and Oracle.

Here are some interesting posts about Oracle and PostgreSQL/MySQL

Setting Isolation level in a database / table

You can set the transaction isolation level for a session. Or for the server. But not for one table.

As a workaround, you can set the desired isolation level immediately before you operate on that single table. Just stay aware that you've set the isolation level for your session.

Docs

i understand that increasing the isolation level can impact performance

Have you measured the impact?

---

Related Topics