1. SERIALIZABLE isolation level
In this lesson, we will discuss the SERIALIZABLE level.
2. SERIALIZABLE
SERIALIZABLE is the most restrictive isolation level.
Here you can see the syntax for setting SERIALIZABLE.
3. Isolation level comparison
SERIALIZABLE, similar to REPEATABLE READ and READ COMMITTED, prevents dirty reads. As REPEATABLE READ, SERIALIZABLE prevents non-repeatable reads, and it also prevents phantom reads.
4. Locking records with SERIALIZABLE
We can lock records under SERIALIZABLE, using a query with a WHERE clause based on an index range. This query will lock those records so that no other transaction can insert or change this data.
However, if the query doesn't have a WHERE clause based on an index range, the query will lock the complete table.
Let's look at both cases in action and demonstrate how the SERIALIZABLE isolation level prevents phantom reads.
5. SERIALIZABLE - query based on an index range
Let's start with a query based on an index range.
In this example, transaction 1, under the SERIALIZABLE isolation level, selects those records from the customers table whose customer_id is between 1 and 3. customer_id is an index, so only these records will be locked. With this configuration, no other transaction will be able to modify these records or insert new records with a customer_id between 1 to 3 until transaction 1 finishes. Note that this query returned only one customer.
6. SERIALIZABLE - query based on an index range
After this selection, transaction 2 starts. It tries to insert a new record into the customers table, with the customer_id equals to 2. As this customer_id is between 1 and 3, matching the requirements of the select query of transaction 1, this operation will have to wait until transaction 1 finishes.
7. SERIALIZABLE - query based on an index range
If within Transaction1 we select the customers whose customer_id is between 1 and 3 again, we will get the same result we got the first time we selected the customers with a customer_id between 1 and 3. We avoided the phantom record.
8. SERIALIZABLE - query based on an index range
When transaction 1 finishes, the insert statement is executed.
9. SERIALIZABLE - query based on an index range
If transaction 2 had inserted a customer with the customer_id equals to 200, it would have added the record instantly. That's because the customer_id doesn't match the requirements of the select query of transaction 1.
10. SERIALIZABLE - query not based on an index range
Let's show an example where there is a query that is not based on an index range.
This example shows a transaction which selects, under the SERIALIZABLE isolation level, every field from the customers table. As it selects without an index range, the complete table will be locked.
After that, transaction 2 tries to insert a customer. As the complete table is locked, it will have to wait until transaction 1 ends.
11. SERIALIZABLE - query not based on an index range
If transaction 1 fires the same select query again, we will get the same result we got in the first select query. We avoided the phantom record.
12. SERIALIZABLE - query not based on an index range
Finally, transaction 1 finishes and the insert statement is executed.
13. SERIALIZABLE - summary
Let's summarize SERIALIZABLE. It prevents dirty, non-repeatable, and phantom reads, giving good data consistency.
By contrast, you can be blocked by a SERIALIZABLE transaction.
You can use it when data consistency is a must.
14. Let's practice!
Now it is your turn to practice!