This repository contains a database schema and several SQL scripts designed to demonstrate SQL Server concurrency issues, deadlocks, and isolation levels for educational purposes.

• SQL Server (2016 or newer)
• SQL Server Management Studio (SSMS) or Azure Data Studio

1. Clone this repository
2. Run the create-database.sql script to create the sample database and populate it with data
3. Review the example scripts and run them following the instructions inside each file

This workshop covers the following concurrency topics:

1. Deadlocks - When two transactions block each other
2. Transaction Isolation Levels - Various isolation levels and their effects:
   • READ UNCOMMITTED
   • READ COMMITTED
   • REPEATABLE READ
   • SERIALIZABLE
3. Concurrency Phenomena:
   • Dirty reads
   • Non-repeatable reads
   • Phantom reads
4. Lock Escalation - How row-level locks can escalate to table-level locks
5. Optimistic Concurrency Control - Using row versioning to detect conflicts

Demonstrates a classic deadlock scenario where two transactions acquire locks in different orders.

Shows how READ UNCOMMITTED isolation level can lead to reading uncommitted data that might be rolled back.

Compares READ COMMITTED with REPEATABLE READ to show how the latter prevents data from changing during a transaction.

Compares REPEATABLE READ with SERIALIZABLE to show how the latter prevents new rows from appearing in result sets.

Shows how SQL Server can escalate from row-level locks to table-level locks when many rows are affected.

Demonstrates optimistic concurrency control using SQL Server's ROWVERSION feature.

The sample database contains the following main tables:

1. Organizations - Companies in the system
2. Branches - Physical locations belonging to organizations
3. Users - People who use the system
4. Products - Items that can be ordered
5. Categories - Product categories
6. Orders - Orders placed by users
7. OrderItems - Line items within orders
8. Inventory - Stock level changes for products
9. AuditLogs - Record of changes to the database

When designing applications to handle concurrency, consider these best practices:

1. Keep transactions as short as possible
2. Access objects in the same order across all transactions
3. Use appropriate isolation levels for your needs
4. Consider optimistic concurrency for low-contention scenarios
5. Use snapshot isolation for reporting queries
6. Be aware of lock escalation thresholds
7. Implement retry logic for deadlock victims
8. Monitor for deadlocks using SQL Server's deadlock graph

• SQL Server Transaction Locking and Row Versioning Guide
• Deadlocks Guide
• Analyze and prevent deadlocks
• Transaction Isolation Levels

Connectionsträng jdbc:sqlserver://localhost:1434;user=sa;password=secure_password123

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide