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/

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!