Exploring locking in SQL Server

Exploring locking in SQL Server

by | Oct 29, 2023 | DATA ENGINEERING

Introduction

In the realm of SQL Server, managing data involves various intricate processes that impact the integrity and consistency of stored information. Critical aspects like locking, data modification, and committing changes play a pivotal role in ensuring the reliability and stability of a database system in large data workloads.

Understanding Locking

In SQL Server, locking plays a fundamental role in managing concurrent access to data. Locks control resource contention, ensuring the isolation and integrity of data modifications performed by different transactions. There are different types of locks, such as shared locks, exclusive locks, and update locks. For instance, when a SELECT statement reads data, it acquires shared locks, while UPDATE or DELETE statements acquire exclusive locks to prevent other transactions from modifying data concurrently.

Data Modification

When modifying data in SQL Server, it’s essential to consider the effects on the integrity of the database. Operations include INSERT, UPDATE, and DELETE. For example, an UPDATE statement alters existing records, while DELETE eliminates records from the database. Inserting new records impacts the primary key constraints and relationships within the database.

Committing Data

Committing changes in SQL Server is an essential step in finalising modifications to the database. The COMMIT statement ensures that all changes made by a transaction are successfully applied and persisted in the database. If changes are not committed, they remain in a pending state and are not visible to other transactions. Conversely, the ROLLBACK statement reverses modifications made by a transaction if there’s an error or an intentional rollback.

Practical Examples

Example 1 – Locking Scenarios:

Consider a scenario where two users concurrently access the same table. User A performs an UPDATE operation on a set of records while User B simultaneously attempts to modify the identical records. This scenario might lead to contention due to conflicting locks, impacting data consistency.

Example 2 – Data Modification and Committing:

Assume a banking system where a transaction involves transferring funds between two accounts. The operation requires updating the balance in both accounts while ensuring the atomicity of the transaction. Committing the changes ensures that the funds are transferred accurately and durably stored in the database.

To prevent table locks in SQL scripts or statements, consider the following strategies:

  • To improve concurrency and minimise locks on tables, consider using snapshot isolation levels like READ COMMITTED SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION. These levels store a version of the data when changes are made, which allows for concurrent read access. Using snapshot isolation, you can reduce the chances of blocking other users from accessing the same data.
  • Implementing table partitioning divides large tables into smaller, more manageable units based on criteria such as date ranges. This strategy helps distribute data across multiple partitions, reducing contention and the likelihood of table-level locks.
  • Column store indexes can significantly enhance the performance of queries for large workloads, particularly regarding analytical queries on extensive tables. They are highly efficient when dealing with large-scale data warehouse scenarios where analytical queries aggregate vast amounts of data. By organising data by column rather than row, these indexes reduce I/O and memory requirements, resulting in faster query execution and reducing the impact of locking.
  • Another effective strategy is utilising the column store index’s compression and storage capabilities. Compared to traditional indexes, column store indexes have better compression, allowing more data to be stored in memory and reducing the need for frequent I/O operations. This can significantly improve query performance without causing locks due to reduced disk access. Bear in mind that the decision to implement column store indexes in a data workload is context-dependent and varies based on factors such as the size of the dataset, the nature of queries executed, and the performance requirements. 

Conclusion

Understanding the intricacies of locking, data modification, and committing data in SQL Server is crucial for maintaining data integrity, concurrency, and overall database performance. Practising these concepts with a comprehensive grasp of their implications ensures efficient and reliable database management.

This article aims to provide a broad understanding of the core concepts surrounding locking, data modification, and committing data in SQL Server, along with practical examples to illustrate their significance in database management.

Batch Script Waiting File Monitor

Batch Script Waiting File Monitor

by | Oct 18, 2023 | DATA ENGINEERING

The file monitor batch script is purposefully designed to wait for files in a folder before subsequent downstream processes are started. This is useful in integration applications where there are dependencies on such files or resources to be present before those processes ca n be initialised.

Rem Desc   : This script when activated waits for files in a folder to be present. When  the specified is available it then proceeds to start a SQL Agent job to kick subsequent processes.

@echo off
setlocal enabledelayedexpansion

:: Set variables
set "folderPath=\\bhxappfs01\InhouseApps\UAT\Blue Yonder\Outbound"  REM Path to the folder to monitor
set "ssisJobName=Dashboard_CostPriceDeltaOutput"  REM Name of the SQL Server Agent job

:: File patterns to look for
set "patterns=PMM_DB_IB01_ PMM_DB_IB02_ PMM_DB_ADJ_ PMM_DB_BR02_"

:loop
:: Initialize an array to keep track of found files
set "foundFiles="

:: Loop through the file patterns and find the most recent files
for %%P in (%patterns%) do (
    set "mostRecentFile="
    for /f "delims=" %%F in ('dir /b /o-d "%folderPath%\%%P" 2^>nul') do (
        if not defined mostRecentFile (
            set "mostRecentFile=%%F"
            set "foundFiles=!foundFiles! %%F"
        )
    )
)

:: Check if all four files are found and output their filenames and extensions
for %%P in (%patterns%) do (
    echo Looking for %%P...
    if not "!foundFiles: %%P=!"=="" (
        set "filename="
        for %%F in (!foundFiles: %%P=!) do (
            set "filename=%%~nF"
            set "extension=%%~xF"
            echo %%P found: !filename!!extension!
        )
    ) else (
        echo %%P not found. Waiting for all files...
        goto continueWaiting
    )
)

:: All four files are found; trigger the SSIS job
echo All required files found. Triggering the SSIS job...
sqlcmd -S YOUR_SERVERNAME -Q "EXEC msdb.dbo.sp_start_job @job_name = '%ssisJobName%'"

:continueWaiting
:: Pause for a specified time (e.g., 5 minutes)
timeout /t 300 >nul
goto loop

</pre>
BLOG

BLOG

by | Jul 6, 2023 | DATA ENGINEERING

Blog

Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed molestie, velit ut eleifend sollicitudin, neque orci tempor nulla, id sagittis nisi ante nec arcu.

Exploring locking in SQL Server

Exploring locking in SQL Server

by | Oct 29, 2023 |

Introduction In the realm of SQL Server, managing data involves various intricate processes that impact the integrity and consistency of stored information. Critical aspects like locking, data modification, and committing changes play a pivotal role in ensuring the...

Evaluating Retail Performance

Evaluating Retail Performance

by | Jul 6, 2023 |

The concept of "same-store sales" (SSS) in the retail industry refers to a metric used to evaluate the performance of existing stores within a retail chain in the most recent accounting period relative to the revenue generated in a similar period in the past. Usually,...

Accurate Stock Management: Why it should matter

Accurate Stock Management: Why it should matter

by | Oct 17, 2024 |

Abstract Inventory management is one of the most critical factors influencing the profitability and efficiency of a retail business. Maintaining accurate stock levels ensures that products are available at the right time and location, reduces loss due to shrinkage or...

Adding a new person on Telleion MIS

Adding a new person on Telleion MIS

by | Aug 4, 2024 |

To add a new person to your school's information repository through the Telleion platform, follow these steps: 1. Log In to Telleion Software: Open your web browser and navigate to https://telleiondemo.purplewells.com. Enter your username and password to log in to the...

Batch Script Waiting File Monitor

Batch Script Waiting File Monitor

by | Oct 18, 2023 |

The file monitor batch script is purposefully designed to wait for files in a folder before subsequent downstream processes are started. This is useful in integration applications where there are dependencies on such files or resources to be present before those...

BLOG

BLOG

by | Jul 6, 2023 |

Blog Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed molestie, velit ut eleifend sollicitudin, neque orci tempor nulla, id sagittis nisi ante nec arcu. Follow Along Class aptent taciti sociosqu ad litora torquent per...

Follow Along

Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed molestie, velit ut eleifend sollicitudin, neque orci tempor nulla, id sagittis nisi ante nec arcu.

Free Courses

Duis egestas aliquet aliquet. Maecenas erat eros, fringilla et leo eget, viverra pretium nulla. Quisque sed augue tincidunt, posuere dui tempor.

Premium Courses

Duis egestas aliquet aliquet. Maecenas erat eros, fringilla et leo eget, viverra pretium nulla. Quisque sed augue tincidunt, posuere dui tempor.

Ready to get started?

Get in touch, or create an account

Create Account
Contact Us