Managing SQL Server Database Concurrency

links.md

Link to this page: https://git.io/fhA7L

Links to software used in this course:

• SQL Server Downloads (LocalDB, used in the course, is in the SQL Server Express installation):
  • https://www.microsoft.com/en-ca/sql-server/sql-server-downloads
• Azure Data Studio (formerly SQL Server Operations Studio):
  • https://docs.microsoft.com/en-us/sql/azure-data-studio
• SQL Server Management Studio (SSMS):
  • https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms

Docker Containers for SQL Server:

• Windows

  • docker pull microsoft/mssql-server-windows-developer

• Linux

  • docker pull microsoft/mssql-server-linux

Links to Microsoft documentation on concurrency and transactions:

• Transaction Locking and Row Versioning Guide
  • https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
• SET TRANSACTION ISOLATION LEVEL
  • https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
• Transaction Related Dynamic Management Views and Functions
  • https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/transaction-related-dynamic-management-views-and-functions-transact-sql
  • https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql
• Extended Events
  • https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events
• Transactions
  • https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql
• TRY...CATCH
  • https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql
• SET XACT_ABORT
  • https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql
• SET LOCK_TIMEOUT
  • https://docs.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql

Snapshot Isolation

• Snapshot Isolation in SQL Server
  • https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
• ALTER DATABASE SET Options
  • https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options

Related Pluralsight Courses:

• SQL Server: Introduction to Extended Events
  • https://app.pluralsight.com/library/courses/sqlserver-basicxevents
• Handling Errors in SQL Server
  • https://app.pluralsight.com/player?course=sql-server-2008-database-development-pt1 Module 8
  • https://app.pluralsight.com/player?course=sql-server-2012-querying-pt2 Module 2
• XML support in SQL Server
  • https://app.pluralsight.com/library/courses/sql-server-xml

Miscellaneous SQL Server documentation links:

• Configuration Functions
  • https://docs.microsoft.com/en-us/sql/t-sql/functions/configuration-functions-transact-sql

Ebooks and other resources:

• Defensive Database Programming
  • https://www.red-gate.com/community/books/defensive-database-programming
• SQL Server Concurrency: Locking, Blocking and Row Versioning
  • https://www.red-gate.com/community/books/sql-server-concurrency
• Troubleshooting SQL Server: A Guide for the Accidental DBA
  • https://www.red-gate.com/community/books/accidental-dba
• sp_whoisactive from Adam Machanic
  • http://whoisactive.com/

New script to be uploaded soon On Wed, Jul 31, 2019, 12:03 PM Gerald Britton <notifications@github.com> wrote:

…

I'll check it out On Wed, Jul 31, 2019, 10:51 AM jeremygithubuser ***@***.***> wrote: > hi @gbritton1 <https://github.com/gbritton1> , the script to create the > demo db is not there? > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > < https://gist.github.com/a3ccb4333ba2dcce59fd0abf002a0e31?email_source=notifications&email_token=AC5R66DTLK6OUECFO7FHN4LQCGRHNA5CNFSM4HYQEEA2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFWIGO#gistcomment-2986087 >, > or mute the thread > < https://github.com/notifications/unsubscribe-auth/AC5R66GBDV6GFRCNC4UPCFTQCGRHNANCNFSM4HYQEEAQ > > . > — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <https://gist.github.com/a3ccb4333ba2dcce59fd0abf002a0e31?email_source=notifications&email_token=AC5R66A4WQHR5L5DA2O375TQCGZTTA5CNFSM4HYQEEA2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFWIL4#gistcomment-2986174>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AC5R66BDTS3HI6TZCUXKRJ3QCGZTTANCNFSM4HYQEEAQ> .

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

Not sure which course this is for. Though I think, if you have a full subscription you can download the exercise files from Pluralsight

…

On Mon, Sep 25, 2023 at 1:58 AM deepakgupta91 ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ did not find the db script in any of demo excercise. could you please help me on it. — Reply to this email directly, view it on GitHub <https://gist.github.com/gbritton1/a3ccb4333ba2dcce59fd0abf002a0e31#gistcomment-4703039> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC5R66CWJT24F76EHWYLMGLX4EMQPBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA4TIOJUGQZTAMNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you authored the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

-- Gerald Britton, Pluralsight Author LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton Pluralsight Courses: https://bit.ly/2WB2TdX <https://bit.ly/2WB2TdX>

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?

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

Yes any local filepaths need to be customized to fit your environment.

…

On Thu, Jun 13, 2024, 12:34 AM Brent Fisher ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ 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.png (view on web) <https://gist.github.com/assets/6820638/8d29fd84-6591-40ca-80c8-ff40978aacea> — Reply to this email directly, view it on GitHub <https://gist.github.com/gbritton1/a3ccb4333ba2dcce59fd0abf002a0e31#gistcomment-5087350> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC5R66FN4JI6BKDFGHYOCSTZHEONJBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA4TIOJUGQZTAMNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you authored the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

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.

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?

Good catch Brent! Indeed I did export the create database statement when I should have removed rcsi. I'll see what I can do to get the script updated.

…

On Fri, Jun 14, 2024, 1:18 AM Brent Fisher ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ 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? — Reply to this email directly, view it on GitHub <https://gist.github.com/gbritton1/a3ccb4333ba2dcce59fd0abf002a0e31#gistcomment-5088565> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC5R66HH7MLBF6R777QPPKLZHJ4JLBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA4TIOJUGQZTAMNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you authored the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

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!