WEBVTT

00:00.000 --> 00:07.000
Transactions and concurrency control Learning objectives

00:07.000 --> 00:14.000
Understand what a database transaction is and its asset properties

00:14.000 --> 00:20.000
Use Commit to save changes and roll back to undo them in a transaction

00:20.000 --> 00:27.000
Identify common concurrency problems like dirty reads and non-repeatable reads

00:27.000 --> 00:30.000
Understanding transactions

00:30.000 --> 00:36.000
In SQL, some operations are too important, so they are done in two or more steps

00:36.000 --> 00:41.000
Think of a bank transfer. Money comes out of one account and goes into another

00:41.000 --> 00:46.000
What if the first part works, but the second fails? The money would vanish

00:46.000 --> 00:50.000
That's where transactions from SQL come in

00:50.000 --> 00:55.000
In SQL, a transaction is a group of one or more SQL statements

00:55.000 --> 00:58.000
It follows the all or nothing rule

00:58.000 --> 01:02.000
Either all the steps in the transaction finish successfully together

01:02.000 --> 01:07.000
Or if any single step fails, the whole transaction is cancelled and undone

01:07.000 --> 01:10.000
This safety is provided by the asset properties

01:10.000 --> 01:16.000
Adamicity means all the steps in a transaction happen together as one complete unit

01:16.000 --> 01:18.000
Or none of them happen at all

01:18.000 --> 01:24.000
Consistency means the database remains correct and follows all rules before and after the transaction

01:24.000 --> 01:31.000
Isolation means each transaction runs separately, so they do not affect or interfere with one another

01:31.000 --> 01:38.000
Durability means once the transaction is saved, the changes remain permanent even if the system crashes

01:38.000 --> 01:42.000
Controlling transactions with Commit and rollback

01:42.000 --> 01:46.000
In SQL, we control transactions using simple commands

01:46.000 --> 01:49.000
We start a transaction block with start transaction

01:49.000 --> 01:54.000
This tells the database group everything I do next into one unit

01:54.000 --> 01:56.000
Let's see an example

01:56.000 --> 02:02.000
Update accounts set balance equals balance minus 1000, where account ID equals 1

02:02.000 --> 02:09.000
Update accounts set balance equals balance plus 1000, where account CE equals 2

02:09.000 --> 02:17.000
And update accounts set balance equals balance plus 350, where account ID equals 3

02:17.000 --> 02:21.000
Here we are using the Commit command, which works like a save button

02:21.000 --> 02:26.000
If all updates are correct, Commit makes all the changes permanent in the database

02:26.000 --> 02:30.000
As you can see, based on our update, the values in the table have changed

02:30.000 --> 02:34.000
But this change becomes permanent only after we use the Commit command

02:34.000 --> 02:39.000
Similarly, we can use rollback, which works like an undo button

02:39.000 --> 02:44.000
It cancelled the temporary balance update and returned the table to its original state

02:44.000 --> 02:47.000
Concurrency and isolation levels

02:47.000 --> 02:51.000
In SQL databases serve many users at once

02:51.000 --> 02:56.000
What happens when two transactions try to work with the same data simultaneously?

02:56.000 --> 02:59.000
This is concurrency and it can lead to problems

02:59.000 --> 03:02.000
There are three main issues in SQL

03:02.000 --> 03:04.000
In dirty read

03:04.000 --> 03:07.000
Both transactions access the same data

03:07.000 --> 03:11.000
Transaction be update the value but doesn't commit

03:11.000 --> 03:15.000
Transaction A reads that temporarily changed value

03:15.000 --> 03:20.000
Then, transaction be rolls back restoring the original data

03:20.000 --> 03:25.000
Now, transaction A has incorrect data that was never officially stored

03:25.000 --> 03:28.000
In non-repeatable read

03:28.000 --> 03:31.000
Transaction A reads a value

03:31.000 --> 03:34.000
Transaction be updates and commits that value

03:34.000 --> 03:40.000
Transaction A reads the same value again and gets a different result

03:40.000 --> 03:43.000
The data has changed between the two reads

03:43.000 --> 03:45.000
In phantom read

03:45.000 --> 03:49.000
Transaction A runs a query and gets a list of rows

03:49.000 --> 03:53.000
Transaction B deletes rows that meet the same condition and commits

03:53.000 --> 03:58.000
Transaction A runs the same query again but the result set is different

03:58.000 --> 04:01.000
Some existing rows disappear like phantoms

04:01.000 --> 04:04.000
This is called a phantom read

04:04.000 --> 04:07.000
Isolation levels and deadlocks

04:07.000 --> 04:12.000
In SQL, isolation levels determine how transactions interact with each other

04:12.000 --> 04:15.000
Common levels include read uncommitted

04:15.000 --> 04:18.000
Allows dirty reads, the least restrictive read

04:18.000 --> 04:20.000
Committed

04:20.000 --> 04:23.000
Prevents dirty reads but allows non-repeatable reads

04:23.000 --> 04:25.000
Repeatable reads

04:25.000 --> 04:28.000
Prevents both dirty and non-repeatable reads

04:28.000 --> 04:32.000
Serializable prevents all concurrency issues the most restrictive

04:32.000 --> 04:36.000
Think of isolation levels as different types of privacy screens

04:36.000 --> 04:38.000
Between transactions

04:38.000 --> 04:44.000
A higher isolation level provides more privacy but can make communications lower

04:44.000 --> 04:49.000
Sometimes in SQL a situation called a deadlock can occur

04:49.000 --> 04:54.000
Imagine transaction A, locks account 1 and needs account 2

04:54.000 --> 04:58.000
While transaction B locks account 2 and needs account 1

04:58.000 --> 05:01.000
They are stuck waiting for each other forever

05:01.000 --> 05:04.000
The databases solution is smart

05:04.000 --> 05:10.000
It detects the deadlock and automatically rolls back one of the transactions allowing the other to complete

05:10.000 --> 05:12.000
This breaks the impasse

05:12.000 --> 05:19.000
To reduce deadlocks we should generally access tables in the same order in our applications and keep transactions as short as possible

05:25.000 --> 05:29.000
Transactions in SQL help keep your data safe and consistent

05:29.000 --> 05:38.000
With commit and rollback you can control changes easily ensuring your applications stay reliable even with errors or multiple users