Skip to content

Instantly share code, notes, and snippets.

@gbritton1
Last active June 14, 2024 13:42
Show Gist options
  • Save gbritton1/a3ccb4333ba2dcce59fd0abf002a0e31 to your computer and use it in GitHub Desktop.
Save gbritton1/a3ccb4333ba2dcce59fd0abf002a0e31 to your computer and use it in GitHub Desktop.
Managing SQL Server Database Concurrency
@okproject
Copy link

okproject commented Jun 16, 2019

Hi @gbritton1 , where is the example db link for the course ?

@gbritton1
Copy link
Author

gbritton1 commented Jun 16, 2019 via email

@jeremygithubuser
Copy link

hi @gbritton1 , the script to create the demo db is not there?

@gbritton1
Copy link
Author

gbritton1 commented Jul 31, 2019 via email

@gbritton1
Copy link
Author

gbritton1 commented Aug 10, 2019 via email

@gbritton1
Copy link
Author

@jeremygithubuser the script to create the demo db was uploaded to the exercise files on Aug 5, 2019. Thanks so much for the catch!

@gbritton1
Copy link
Author

gbritton1 commented Sep 25, 2023 via email

@pescadorbob
Copy link

Hi gbritton1, the new script was uploaded, but it doesn't run. The first few lines reference mdf files from your local machine. Is that what you meant?

image

@pescadorbob
Copy link

I altered it with my own local dirs and it worked fine.

@gbritton1
Copy link
Author

gbritton1 commented Jun 13, 2024 via email

@pescadorbob
Copy link

Thanks Gerald. I am enjoying your course. It is helping me understand isolation in ways that I hadn't before. I wanted to share one difficulty I spent hours working on understanding last night with the demo in Module 4, Read Committed.

I hadn't learned about READ_COMMITTED_SNAPSHOT ON yet. The default on a database is for that to be OFF. But, in the create database script, it turns it on.

61 ALTER DATABASE [BobsShoes] SET READ_COMMITTED_SNAPSHOT ON 

I couldn't understand why when you ran the example, the script would block on the shared row lock, but when I ran what I thought was the same thing, it would just return the same results as before, instead of blocking. The results from your video would only happen when READ_COMMITTED_SNAPSHOT was set to OFF.

I can only imagine that is because when you exported your database, it took the last value of it, and yours happened to have that as the last value.

I searched the internet and found this post explaining what I see you explain in Module 5.

This helped me understand it and reproduce the behavior you demonstrated.

Thanks again for your course and I wish you all the best.

@pescadorbob
Copy link

One other question from module 4: Read Committed, time 2:54, you say,

"if you tried the same experiment from module 3, read uncommitted with the phantom reads. If you tried it, you'll know that the phantom reads are still there."

Well, I tried the suggested homework, and I didn't get phantom reads. In my test, when doing it with:
TRANSACTION ISOLATION LEVEL READ COMMITTED
READ_COMMITTED_SNAPSHOT OFF

the second

'select * from Orders.Orders;'

blocked because of the row lock on the new Orders insert.

Did you just say that to see who was paying attention, or... did I miss something?

I checked with one of my technical coaches, and he agreed with me that you don't get phantom reads in 'READ COMMITTED' only in 'READ UNCOMMITTED'.

What am I missing?

@gbritton1
Copy link
Author

gbritton1 commented Jun 14, 2024 via email

@pescadorbob
Copy link

Thanks for your prompt reply @gbritton1. Even though this may not be the channel you planned for feedback.

Could you also comment on the phantom reads using READ COMMITTED? I'd like to close out my doubts on that today.

I loved the straightforward way your course lets me explore, hands-on, concurrent behavior using multiple windows. Even one of my technical coaches was impressed because it leaves nothing up to timing chance.

Thanks for your insightful course!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment