Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome to model for sequel programming to this is less than three concurrency and transactions were moving into Sub lesson 3.5 concurrency problems. One.
00:10
And this lesson. We're going to describe the four main types of concurrency problems that occur in my sequel as well as other sequel databases,
00:18
and in the next lesson will describe how you address those problems and when to consider that they might be a larger issue for you than previously than previously thought.
00:29
Now, courtesy problems are related to resource management, and typically the more users of the system that you acquire, the larger the possibility that some of these problems may occur.
00:43
So, for example, consider that we have a process one or user one
00:48
we have processed, too, or user, too.
00:51
Now, let's say process one
00:54
calls the ad employees procedure
00:58
okay, and it goes and it adds, starts adding,
01:02
The employees
01:03
and process, too,
01:04
is selecting employee data.
01:07
So it's going in there to read the data and bring it back.
01:12
Now we've dealt some with transactions and we know that there is a commit feature in which the commit is sent to the database
01:22
to say OK, you have the data, go ahead and save it
01:26
well without being careful. You may be allowing processes to read uncommitted data,
01:33
which means
01:34
maybe process one goes in there,
01:37
insert some data
01:40
and then decides to take that data out. Decides it's not committed.
01:45
And while that was happening, process to win in there.
01:49
Read the uncommitted data on Returned the information to the user.
01:55
So process to is now presenting information to the user
01:59
that does not exist in the database
02:01
because it was uncommitted and processed. One decided for some reason to not commit that data,
02:07
and that's called a dirty read.
02:12
Now, in this example, we have two processes heir to users that we're talking about. But depending on the size of your application, you could very easily have three
02:22
four
02:23
users of the system
02:25
up to, you know, maybe hundreds, maybe thousands of users. And the more users you have, the more likely that something like this is going to happen. And to your end users, it's gonna they're gonna be describing seeing data that was never there.
02:39
So another type of concurrency problem is called the lost update.
02:45
So what is a lost update
02:49
The last update is, if process one here
02:53
comes in,
02:55
select some data from the employee table and decides to make a change based on that selection.
03:02
So it's selected that data. It's decided to change some of that data
03:07
and then
03:09
process to comes in right behind process one,
03:15
see some other data
03:16
and decides to make a change based on that data.
03:21
So Process one
03:23
applies an update
03:25
and then process to without re selecting from the table, because this was happening very, very close to each other. They both selected and read at nearly the same time and made their decisions
03:37
based on what they saw at that time. And let's say that Process one had changed some data
03:42
that would have changed processes to decision.
03:45
And now process one updates the data and process to also updates the data without considering the changes that process one made
03:53
that is considered a lost update.
03:58
The next concurrency problem is called the non repeatable read.
04:02
This occurs when to select statements, try to get the same data, but end up getting different values.
04:10
For example, let's say process, too,
04:14
is selecting data.
04:18
Process three comes in real fast changes the data
04:23
and process. One
04:25
selects that same data, the process to selected but gets different results. And it's because Process three was actually in the middle of their select statements, changing the values
04:35
that's considered a non repeatable read and is considered a type of concur concurrency problem.
04:46
The next type of concurrency problem is called Phantom Reads
04:50
Now. Ah, Phantom Read occurs when you
04:54
select data that is, in the process, a process of being either inserted or deleted.
05:00
An example of this would include if process one.
05:04
It's selecting
05:06
invoices past due
05:10
right as processed, too, is updating whether or not and voices or do or not.
05:15
Um, so let's say process one. Grab some invoices that are past due. But at the same time, process to was marking some of these as not do. And now process one is running off with these do invoices that really don't exist.
05:30
Now, some of these concurrency problems arm or significant, depending on what industry you are in.
05:38
For example, Phantom reads may not be that important for a CMS system like WordPress, where you're just publishing articles and someone just needs to click refresh to see the updated. Ah, article data.
05:50
But in a situation where a bank is using software, that stuff needs to be much more controlled. And they can't be sending out
05:59
past due invoices on accident because of the Phantom read problem. So some of these will not be as Africa ble
06:06
based on
06:08
the type of application you're dealing with.
06:11
So that concludes this lesson on concurrency problems. And the next lesson we'll look at how to implement my sequel features that help prevent these problems. There are some benefits and some drawbacks to actually solving these problems, and in the next lesson will talk about those. I hope to see you there. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor