Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
Hi. Welcome back to Module four. Sequel programming to we're currently in less than three concurrency and transactions removing into sub lesson 3.6 concurrency problems, too. In the previous lesson, we briefly went over the different types of concurrency problems you can encounter, which included the dirty reads,
00:19
the lost updates,
00:21
the non repeatable reads and the Phantom reads in this lesson. We're going to look at the different transaction, the isolation levels you can set to help deal with these problems as well as display. One of the problems that occur is using a procedure and a simple table.
00:36
Now the transaction isolation level is a setting for your transactions that you are running in your database.
00:43
So we have four levels to choose from. In my sequel, we have read uncommitted, read, committed, repeatable, read and serialize herbal
00:52
and well, if we'll go to the top, we will notice that read Uncommitted allows all four problems,
00:59
while serialize herbal prevents all four problems.
01:02
So why would you not just choose the
01:06
most or the more strict isolation level and prevent all the problems? Well, there is a tradeoff that you make as you move down the isolation level or up. Depending on your perspective, the performance is impacted, so read uncommitted has better performance than serialize herbal. So
01:25
depending on your application,
01:27
you may not want to take the performance. And as you may not consider these
01:33
these problems to be significant enough,
01:36
and there are other ways to avoid them as well.
01:40
For example, a bank will probably want the strictest isolation level,
01:45
while a CMS like WordPress may not care all that much because they're dealing more with articles and user comments. And if one of those gets accidentally dropped, it's, you know, it's not a huge deal than if someone's check accidentally gets dropped.
02:02
So with that said, let's take a look at how we can set these in my sequel, and then we'll also take a look at a problem
02:09
that ah kind of shows what kind of concurrency issues you could run into.
02:16
So if we go and look at the isolation levels now, you could set these per session or global. I chose just to set them as a ah global setting for this lesson.
02:27
If I highlight this
02:29
statement right here and just quit the play button,
02:31
it'll run that statement.
02:34
I could do that with any of these statements
02:36
to set the isolation level
02:38
to or the global isolation levels. Whatever I choose will not go ahead and leave mine on Reed Uncommitted.
02:46
Now let's take a look at one of the what one of these problems might look like.
02:50
So in this sequel script, I have a few different statements running. I have a drop and recreate procedure running at the top.
02:58
After that finishes I go hood and delete from this count table that I made,
03:02
which is a very simple table. It's two columns, is the I. D. And the counter
03:07
on the counter column is what we're updating with. The next number is like an anchor nip
03:13
increment counter,
03:15
So I didn't delete that or empty the table to start over.
03:20
I then call the procedure I just created up here,
03:24
and I let that run. I then select
03:29
from that table looking for problems. So what I'm doing in this select statement as I'm selecting the counter column, I'm running the count function and I'm grouping by the counter and ordering by count descending. So what that will do is it will show me any columns where the same number got added twice.
03:46
Now, as you can tell by this procedure, the way it's laid out, the idea and this procedure is that I'm just incriminating plus one
03:53
and I'm adding it to the table.
03:54
I don't want to add the same number to the table,
03:58
so if that happens, that could be a problem.
04:01
So let's go ahead and run this. I have to be Beaver sessions open,
04:08
which are basically simulating two connections to the database. So I'm gonna run this and I'm gonna run this at the same time.
04:15
But first, I'm gonna go ahead and get rid of this start transaction line by commenting it outs,
04:28
comment that out,
04:33
out, go. So now we are not we're not using The transactions were just running it without that.
04:42
So let's go ahead and run this
04:46
and then we'll quickly jump over here and run this one.
04:49
So this one's executing, and this one's executing at the same time in parallel.
04:56
So let's see what our results look like.
05:00
Okay.
05:01
So you can see that we have an issue here.
05:04
It's Ah, the counter 15 52 guys considered inserted twice 7 83 got inserted twice. Thio two or three got in sort of twice. So what happened was that these two different processes selected the same counter column, added one to it and inserted at the same time.
05:25
Now, depending on your application and depending on what you're doing, that could be a problem.
05:30
Let's go ahead and turn back on the transactions
05:36
and let's run it again
05:40
around this one again.
05:43
Now they're both executing.
05:45
So we got an air,
05:46
the transition locks the table. So one of the columns aired, or one of the procedures aired out and this was prevented by the use of the transaction. This is actually a good thing. Um, we didn't do any kind of air catch in in this script. So
06:03
of course, when it
06:04
encountered in there, it just stopped and crashed.
06:09
But let's go ahead and take a look at our table to see if we see any problems.
06:14
And no, we don't. We don't have any problems. They both ran for a
06:19
Let's see that each do 1000. So
06:25
our highest number is
06:29
14 10. So one of these didn't run all the way through,
06:31
but it stopped before it accidentally introduced a ah additional count
06:38
that shouldn't be there.
06:41
Have you run that again?
06:42
Run that again
06:44
again. We'll get the same thing.
06:49
This one's still running
06:53
and again, where we've protected ourselves with the use of transactions.
06:57
So there's other things you can do to when you can't use transactions. And in fact, in an upcoming lesson in with triggers, we have to use a different method if we want to update tables safely
07:10
because the transactions aren't actually allowed to be used from triggers and we'll discuss that later lesson. So that finishes this lesson on concurrency problems, too. It's definitely something you want to look at. You want to consider with respect to what your application is trying to accomplish and be aware of the, uh, the
07:27
the type of problems and obstacles you might have to deal with
07:30
when you have procedures running that are inserting data into, ah,
07:35
various tables that rely on that data to make a decision. In any case, go ahead and download. These, uh,
07:44
these documents are these ah procedures. If you are curious about running them yourself, they're attached to the lesson. And ah, I hope to see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introduction SQL training course teaches SQL core concepts that are applied in professional environments. Once completed with the course, students 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