Transactions

Transactions are units of work within a given application logic that it may be composed of multiple lower-level tasks. Transactions are known also as “Unit Of Work,” a very well-known pattern that keeps track of changes and that logically commits or rolls back them as a whole, also described by Martin Fowler in his Patterns of Enterprise Application Architecture book. Database systems process transactions while maintaining what are known as ACID properties (Atomicity, Consistency, Isolation, and Durability) to guarantee data integrity and accuracy. Canonical examples of business logic for a transaction can be a money transfer between bank accounts or an ATM cash withdrawal, where you want to make sure a number of operations are happening following the “all or nothing” logic. A database platform helps enforcing these ACID properties without requiring complex implementations from an application standpoint: developers won’t need to manually implement logic that reverts update actions if at least one update fails.

Now, you may be very familiar with this sample, and you may be thinking that you’re not going to write a banking application, so you don’t really need transactions. While this line of thought can be apparently correct, keep in mind that even in the simplest application, let’s say a blog engine, you may have the need to use transactions. Let’s say, in fact, that you want to keep track of how many people visit your blog, keeping the count both for the whole blog and for each post you have written. Since you want to be sure you can scale as much as needed, you have two counters, one for the whole blog and one for each article. Each time someone visits a page, you need to increment the counter for the whole website and the counter for that page. What happens if you don’t implement and manage transactions properly?

It could happen that, for any reason, something crashes after the website counter has been updated but before the post counter is updated too. This means that now you have completely lost the information. Consistency is gone. If you try to sum up all the single counters, the result will be different than the site counter. Which value is correct now? You may be able to rebuild the information – maybe – with a lot of dedication, time, and effort. In many cases, you just can’t. Now, for a blog counter, that’s not a big deal (and even this is not true, what if you get paid by the number of visits to your blog?), but it gives you the importance of consistency, and thus transactions, even in places where maybe you haven’t thought. Data consistency should be the cornerstone of every system. Without it, data is just noise.

Luckily for us, in modern database systems, ACID properties are usually achieved through write-ahead logging (WAL) which is a capability that provides atomicity and durability by recording changes in the transaction log at first and only later are written to the database by a recurring background process referred to as checkpoint . The log is usually storing both redo and undo information that will be used to recover whatever transaction was already committed, but not yet written in the database, in situations like recovering from a power loss or during a database restore .

In Azure SQL, you can think of several use cases where using a transaction can help. As an example, you may want to insert, update, or delete multiple rows from a table as a single unit or batch process rather than going one by one. Similarly, you may need to modify the state of a table when, and only when, other tables in the same or in a different database (or even server) have been modified as well. As these systems can process multiple of these operations concurrently, Azure SQL engine will protect consistency, generally, by placing locks on various resources involved in these transactions (base tables, index keys, etc.), with a level of granularity that can span from a single row to the entire database, depending on how concurrency (also known as isolation) settings have been configured. These locks will be released once transactions will be committed or rolled back. Locks are just one of the options, probably the simplest, that Azure SQL can use to make sure data stays consistent. Another, much more sophisticated, is the ability to keep different versions of the same row active at the same time, serving the correct version to the requesting transaction, guaranteeing consistency even if other transactions are changing that very same row. Discussion of this technique, known as row versioning , is way out of the scope of this book: just keep in mind that Azure SQL will use a mix of the two to make sure you have the best possible experience and performance by default.

Applications can control transactions by specifying when a transaction starts and ends, successfully or not, through either Transact-SQL commands or API functions exposed by client drivers. Transactions are usually managed at the connection level: when a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends.

Transactions in Azure SQL are defined explicit when you explicitly define start and end through an API function (e.g., BeginTransaction() method in .NET languages) or by issuing T-SQL statements like BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK.

When you do not explicitly initiate a transaction, but execute a single command against Azure SQL, then the default mode is called auto-commit . In this mode, every T-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. Auto-commit mode is also the default mode in which client drivers like ADO, OLE DB, ODBC, and DB-Library operate.

The third mode we have is called implicit . In this mode, Azure SQL starts a new transaction after the current transaction is committed or rolled back. There is nothing to do to start off a transaction; you only commit or roll back each transaction. This last mode is mainly implemented for compliance and backward compatibility reasons. In most use cases, your applications will use auto-commit when executing simple database commands, and controlling complex transactions is not strictly needed. Switch to explicit mode is recommended when some more complex specific transactional behavior needs to be managed. Transaction mode settings are controllable at connection level by issuing an explicit T-SQL statement. Different client drivers may have different transaction mode default settings and are invoking T-SQL statements for you when a new connection is established, so it’s important to check in the documentation what’s the behavior of the one you’re using.

Local transactions

As an application developer, you will mostly interact with transactions executed within the context of a single Azure SQL database instance, and these are usually referred as local transactions, to differentiate from distributed transactions that we will discuss later on. In this scenario, local transactions are commonly used while wrapped in some more complex business logic inside a Stored Procedure, which is usually the recommended approach to keep transactions short in duration and avoid multiple roundtrips between application and database code. If, for whatever reason, you are not relying on Stored Procedures but are writing your database access command within your application code, you can control transactional behavior by invoking specific commands exposed through client drivers’ APIs.

Here is an example of how to use explicit transactions within a T-SQL script like a Stored Procedure. In our system, there are two main tables: Orders and Inventory ; every time a new order is placed for a given product, we need to decrement its quantity in stock. Business logic in our system must prevent placing a new order for an article that is running out of stock. Out of many ways of implementing such a logic, in our sample we’re wrapping both new order’s placement and decrementing the inventory for the same article in an explicit transaction:

-- Create table structures

CREATE TABLE dbo.Orders (ID int PRIMARY KEY, ProductID int, OrderDate datetime);

CREATE TABLE dbo.Inventory (ProductID int PRIMARY KEY, QuantityInStock int

                            CONSTRAINT CHK_QuantityInStock CHECK (QuantityInStock>-1));

-- Fill up with some sample values

INSERT INTO dbo.Orders VALUES (1,1,getdate());

INSERT INTO dbo.Inventory VALUES (1,100);

-- Begin an explicit transaction

BEGIN TRANSACTION mytran

-- Try executing the unit of work in app logic

BEGIN TRY

      INSERT INTO Orders VALUES (2,1,getdate());

      UPDATE Inventory SET QuantityInStock=QuantityInStock-1 WHERE ProductID=1

      -- If both commands are successful, commit the transaction

      COMMIT TRANSACTION

END TRY

BEGIN CATCH

      -- If there is an error, capture the details

      SELECT

             ERROR_NUMBER() AS ErrorNumber

             , ERROR_SEVERITY() AS ErrorSeverity

             , ERROR_STATE() AS ErrorState

             , ERROR_PROCEDURE() AS ErrorProcedure

             , ERROR_LINE() AS ErrorLine

             , ERROR_MESSAGE() AS ErrorMessage;

      -- Explicitly rollback the transaction

      ROLLBACK TRANSACTION

END CATCH

SELECT * FROM dbo.Orders

SELECT * FROM dbo.Inventory

In this script, after creating and filling a couple of tables, you can find the BEGIN TRANSACTION statement followed by an (optional) name.

The BEGIN TRY block will try to execute all statements until the END TRY, and if they will all be executed without errors, the transaction will be permanently committed by the COMMIT TRANSACTION statement.

We now change the quantity in stock for our product and we set it to zero, as we want to test what happens when running out of stock, and then we try to run the transaction again:

UPDATE dbo.Inventory SET QuantityInStock=0 WHERE ProductID=1

In this case, an error will be thrown while executing the UPDATE Inventory statement as we are violating the CHECK constraint placed on the QuantityInStock attribute. Control will pass from with the BEGIN TRY block to the BEGIN CATCH block. In that block, we’re retrieving all the details related to the latest error happened in that session, but we’re also explicitly calling ROLLBACK TRANSACTION to make sure that modifications made to other tables as part of the same transaction, in the sample INSERT INTO Orders command, will not be persisted, and system’s state will be consistent.

Let’s see how the same behavior can be achieved developing a sample application using Python.

import os

import pyodbc

from decouple import config

server = config('server')

database = config('database')

username = config('username')

password = config('password')

driver= '{ODBC Driver 17 for SQL Server}'

# Connecting to Azure SQL Database with autocommit=True

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password,autocommit=True)

def prepare_database():

    try:

        # Executing database preparation tasks

        cursor = cnxn.cursor()

        cursor.execute("""IF (EXISTS (SELECT *  FROM INFORMATION_SCHEMA.TABLES

                          WHERE TABLE_SCHEMA = 'dbo'

                          AND  TABLE_NAME = 'Orders'))

                          BEGIN

                              DROP TABLE [dbo].[Orders];

                          END

                          IF (EXISTS (SELECT *  FROM INFORMATION_SCHEMA.TABLES

                          WHERE TABLE_SCHEMA = 'dbo'

                          AND  TABLE_NAME = 'Inventory'))

                          BEGIN

                              DROP TABLE [dbo].[Inventory];

                          END

                          CREATE TABLE [dbo].[Orders] (ID int PRIMARY KEY, ProductID int, OrderDate datetime);

                          CREATE TABLE [dbo].[Inventory] (ProductID int PRIMARY KEY, QuantityInStock int

                            CONSTRAINT CHK_QuantityInStock CHECK (QuantityInStock>-1));

                          -- Fill up with some sample values

                          INSERT INTO dbo.Orders VALUES (1,1,getdate());

                          INSERT INTO dbo.Inventory VALUES (1,0);

                          """)

    except pyodbc.DatabaseError as err:

        # Catch if there’s an error

        print("Couldn't prepare database tables")

def execute_transaction():

    try:

        # Switching to autocommit = False, our app can now control transactional behavior

        cnxn.autocommit = False

        cursor = cnxn.cursor()

        cursor.execute("INSERT INTO Orders VALUES (2,1,getdate());")

        cursor.execute("UPDATE Inventory SET QuantityInStock=QuantityInStock-1 WHERE ProductID=1")

    except pyodbc.DatabaseError as err:

        # If there’s a database error, roll back the transaction

        cnxn.rollback()

        print("Transaction rolled back: " + str(err))

    else:

        # If database commands were successful, commit the transaction

        cnxn.commit()

        print("Transaction committed!")

    finally:

        cnxn.autocommit = True

prepare_database()

execute_transaction()

Listing 7-1

Example of using transactions in Python

In this example, you can see how the auto-commit property plays an important role when controlling transactions with pyodbc in Python. We’re opening the connection with autocommit=True, which means that each command will be automatically committed once executed. In the prepare_database() method in fact, we’re executing some preparation activities in the database, and these will be executed as a single transaction. In the execute_transaction() method though, we’re explicitly switching to autocommit=False, so that we can programmatically control when to commit or roll back our logical unit of work. Remember that our application logic is enforced with a CHECK constraint that prevents placing new orders when a given product is out of stock.

By executing this code sample, you will get back this error message and database tables will not be modified as the transaction is rolled back:

Transaction rolled back: ('23000', '[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] The UPDATE statement conflicted with the CHECK constraint "CHK_QuantityInStock". The conflict occurred in database "WideWorldImporters-Full", table "dbo.Inventory", column 'QuantityInStock'. (547) (SQLExecDirectW)')

In this very simple example, we’re reacting to the exception raised when we’re getting an error executing a database operation, but there are other scenarios where our application logic requires to check on a specific state of a given entity in the database (by querying a table) and then decide whether commit or roll back the entire unit of work as a whole.

Naming a transaction can be a useful practice that improves code readability and, maybe more important, will be reflected in various transaction-related system views simplifying the debugging process. BEGIN TRANSACTION [transaction_name] starts a local transaction, but nothing is effectively written in the transaction log until further data manipulation actions such as INSERT, UPDATE, or DELETE are triggering an update to the log (although it may be that other activities like acquiring locks to protect the transaction isolation level of SELECT statements can happen).

An application or a script can begin, and even commit or roll back, a transaction where another transaction already exists. Azure SQL does not really provide direct support for nested transactions, and committing a transaction opened within another has absolutely no effect. Only outer transactions will effectively control what happens to various data manipulation activities executed on various transactions in between, committing or rolling back everything. As application developers, we should not really use this approach to avoid misleading behaviors and consequences.

So why are nested transactions allowed at all? Well, a common scenario where they can be used is when you are writing Stored Procedures containing transactional logic that can be invoked either stand-alone or from an outer procedure that may have itself already initiated a transaction. A good practice is always to check if there’s already an open transaction on that connection using the @@TRANCOUNT system variable and then decide if to start a new one or just participate in the one started by the outer procedure and marking the start of the inner part of the transaction with a SAVE TRANSACTION savepoint_name.

Every time a new BEGIN TRANSACTION is executed, @@TRANCOUNT will be incremented by 1. Same will be decremented by 1 every time COMMIT TRANSACTION will be executed. In case of a ROLLBACK TRANSACTION, all transactions will be rolled back and @@TRANCOUNT will be reset to zero, with the single exception of the ROLLBACK TRANSACTION savepoint_name which will not affect @@TRANCOUNT counter in any way.

When more complex transactional logic is required in fact, developers can define a location in the transaction flow where to return if only a portion of the transaction is conditionally canceled or rolled back: this is usually referred as a savepoint . If a transaction is rolled back to a savepoint with a ROLLBACK TRANSACTION savepoint_name, then the control flow must proceed to completion, optionally executing more T-SQL statements if needed, and a COMMIT TRANSACTION statement to complete it successfully. Otherwise, it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name.

A T-SQL example that summarizes all these concepts is as follows.

USE AdventureWorks2012;

GO

IF EXISTS (SELECT name FROM sys.objects

           WHERE name = N'SaveTranExample')

    DROP PROCEDURE SaveTranExample;

GO

CREATE PROCEDURE SaveTranExample

    @InputCandidateID INT

AS

    -- Detect whether the procedure was called

    -- from an active transaction and save

    -- that for later use.

    -- In the procedure, @TranCounter = 0

    -- means there was no active transaction

    -- and the procedure started one.

    -- @TranCounter > 0 means an active

    -- transaction was started before the

    -- procedure was called.

    DECLARE @TranCounter INT;

    SET @TranCounter = @@TRANCOUNT;

    IF @TranCounter > 0

        -- Procedure called when there is

        -- an active transaction.

        -- Create a savepoint to be able

        -- to roll back only the work done

        -- in the procedure if there is an

        -- error.

        SAVE TRANSACTION ProcedureSave;

    ELSE

        -- Procedure must start its own

        -- transaction.

        BEGIN TRANSACTION;

    -- Modify database.

    BEGIN TRY

        DELETE HumanResources.JobCandidate

            WHERE JobCandidateID = @InputCandidateID;

        -- Get here if no errors; must commit

        -- any transaction started in the

        -- procedure, but not commit a transaction

        -- started before the transaction was called.

        IF @TranCounter = 0

            -- @TranCounter = 0 means no transaction was

            -- started before the procedure was called.

            -- The procedure must commit the transaction

            -- it started.

            COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        -- An error occurred; must determine

        -- which type of rollback will roll

        -- back only the work done in the

        -- procedure.

        IF @TranCounter = 0

            -- Transaction started in procedure.

            -- Roll back complete transaction.

            ROLLBACK TRANSACTION;

        ELSE

            -- Transaction started before procedure

            -- called, do not roll back modifications

            -- made before the procedure was called.

            IF XACT_STATE() <> -1

                -- If the transaction is still valid, just

                -- roll back to the savepoint set at the

                -- start of the stored procedure.

                ROLLBACK TRANSACTION ProcedureSave;

                -- If the transaction is uncommittable, a

                -- rollback to the savepoint is not allowed

                -- because the savepoint rollback writes to

                -- the log. Just return to the caller, which

                -- should roll back the outer transaction.

        -- After the appropriate rollback, echo error

        -- information to the caller.

        DECLARE @ErrorMessage NVARCHAR(4000);

        DECLARE @ErrorSeverity INT;

        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE();

        SELECT @ErrorSeverity = ERROR_SEVERITY();

        SELECT @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.

                   @ErrorSeverity, -- Severity.

                   @ErrorState -- State.

                   );

    END CATCH

GO

Listing 7-2

Example of using transactions in a T-SQL script

The logic here is checking if an existing transaction already exists before modifying the database. If that is the case, then it first creates a savepoint, and if the data modification fails, it rolls back only to that savepoint without involving any previous activities executed in the outer transaction.

Not all database drivers provide direct control over transaction names or savepoints: for example, pyodbc doesn’t provide methods to invoke beginning of a transaction (with or without name) or savepoint, but you can always access these functionalities by simply executing the equivalent T-SQL statements.

In other languages like C# and .NET Core, you can instead control all aspects of your transactional logic, as demonstrated by the following example.

 using (SqlConnection connection = new SqlConnection(connectionString))

{

    connection.Open();

    SqlCommand command = connection.CreateCommand();

    SqlTransaction transaction;

    // Start a local transaction.

    transaction = connection.BeginTransaction("SampleTransaction");

    // Must assign both transaction object and connection

    // to Command object for a pending local transaction

    command.Connection = connection;

    command.Transaction = transaction;

    try

    {

        command.CommandText =

            "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";

        command.ExecuteNonQuery();

        transaction.Save("FirstRegionInserted");

        command.CommandText =

           "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";

        command.ExecuteNonQuery();

        // Rollback to a savepoint

        transaction.Rollback("FirstRegionInserted");

        // Only first insert will be considered

        // Attempt to commit the transaction.

        transaction.Commit();

        Console.WriteLine("Only first insert is written to database.");

    }

    catch (Exception ex)

    {

        Console.WriteLine("Commit Exception Type: {0}", ex.GetType());

        Console.WriteLine("  Message: {0}", ex.Message);

        // Attempt to roll back the transaction.

        try

        {

            transaction.Rollback("SampleTransaction");

        }

        catch (Exception ex2)

        {

            // This catch block will handle any errors that may have occurred

            // on the server that would cause the rollback to fail, such as

            // a closed connection.

            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());

            Console.WriteLine("  Message: {0}", ex2.Message);

        }

    }

}

Listing 7-3

Example of using transactions in a .NET Core app

Azure SQL offers several Dynamic Management Views (DMVs) to monitor transactions running in a given database instance. They start with the sys.dm_tran_* prefix. The following example returns detailed information on open transactions at the database level:

SELECT * FROM sys.dm_tran_database_transactions WHERE database_id = db_id()

Distributed transactions across cloud databases

Relational database management systems have traditionally supported transactions across multiple databases and even multiple servers, usually referred as distributed transactions. These capabilities are enabled through specific components, like distributed transaction coordinators and monitors (like Microsoft Distributed Transaction Coordinator, or MS DTC), and two-phase commit protocols like XA. Generally speaking, these underlying technologies were designed to run within traditional data center environments, with low-latency, tightly coupled local connectivity and specific hardware configurations. In highly distributed systems and standardized cloud environments though, design patterns have evolved to remove dependencies on these traditional approaches, moving instead into more decoupled, asynchronous patterns like Saga which are usually managed outside of the database tier.

As per the time of writing, MS DTC is supported only in Azure SQL Managed Instance, and you are able to take full advantage of it. But what if you need distributed transaction support in Azure SQL Database? You’ll be happy to know it is still possible to implement distributed transactions across multiple Azure SQL Database instances using Elastic Transactions, which are available for .NET-based applications.

With this approach, Azure SQL Database will effectively coordinate the distributed transaction on behalf of MS DTC, and applications can connect to multiple instances and execute a transaction controlled through System.Transaction classes, with mandatory requirement of .NET 4.6.1 or greater, while unfortunately this is not yet supported by .NET Core at the time we’re writing this. The following picture visualizes how various components are interacting with each other to support this scenario.

Let’s see how this can be implemented in our applications through a simple example:

using (var scope = new TransactionScope())

{

    using (var conn1 = new SqlConnection(connStrDb1))

    {

        conn1.Open();

        SqlCommand cmd1 = conn1.CreateCommand();

        cmd1.CommandText = string.Format("insert into T1 values(1)");

        cmd1.ExecuteNonQuery();

    }

    using (var conn2 = new SqlConnection(connStrDb2))

    {

        conn2.Open();

        var cmd2 = conn2.CreateCommand();

        cmd2.CommandText = string.Format("insert into T2 values(2)");

        cmd2.ExecuteNonQuery();

    }

    scope.Complete();

}

As you can see, usage is quite straightforward: TransactionScope is doing all the heavy lifting by establishing an ambient transaction (an ambient transaction is one that lives in the current thread), and all connections opened within that scope will participate in that transaction. If these are pointing to multiple databases, then the transaction is automatically elevated to a distributed transaction. When the scope is complete, transactions will commit.

Elastic Transactions can also involve databases belonging to multiple logical servers, but to enable that, these servers will need first to be entered into a mutual communication relationship by invoking the New-AzSqlServerCommunicationLink PowerShell commandlet.

We can monitor the status of a distributed transaction using the same DMVs mentioned for local transactions (e.g., SELECT * FROM sys.dm_tran_active_transactions), but for distributed transactions, the UOW (Unit Of Work) column will identify the different child transactions that belong to the same distributed transaction. All transactions within the same distributed transaction carry the same UOW value.

Locking and non-locking options

Database engines are generally multi-user systems, where operations performed by a given user may or may not affect tables and rows used by others concurrently. By specifying an isolation level for an individual transactional command, or as a default for a session or all sessions in a database, we can define the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels define which concurrency side effects, such as dirty reads (read data from a row that has been modified by another running transaction not yet committed) or phantom reads (rows added or removed by another transaction to the records being read), are allowed. They control things like whether locks are taken when data is read, what type of locks are requested, and how long the read locks are held. If locks are not used to provide consistency, isolation level helps the engine to understand when different versions of the same rows can be deleted as no other transactions are using it. When read operations are referencing rows modified by another transaction, isolation levels define if they get blocked until the exclusive lock on the row is freed; if locks are not used, they define which committed version of the row that existed at the time the statement or transaction started should be retrieved; if isolation is set low enough, you can even read uncommitted data. While this is permitted, you should almost never use this ability. Reading uncommitted data means that you are reading something that has the chance to be rolled back; this means that your application may start to do something based on the data that, for example, has been entered by mistake and thus undone.

An important concept to understand is that changing transaction isolation level does not affect how the locks are acquired to protect data modifications. Transaction always gets an exclusive lock on any data modified and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. Locks are not only a synchronization mechanism but also carry metadata which is very useful to the database engine to know what’s going on anytime it is needed. What can be changed is how locks are used. Luckily, we don’t have to go into nitty-gritty details, as we can express the desired behavior we want, in terms of consistency and concurrency, and Azure SQL will use the correct locks for us.

Before moving further, let’s clarify some of the terminology just used, especially around locks. A Shared Schema Lock (SCH-S) is used to make sure that no one can change the table definition while you are using it. In a highly concurrent system, it could happen that someone will try to DROP a table or ALTER it while you are running your SELECT. Azure SQL will protect your SELECT by placing a SCH-S lock so that those commands who would like to change the schema will have to wait until your SELECT is done.

A Range Lock is used to protect a running transaction from data that doesn’t exist yet, but that, if allowed to be created, could interfere with the running transaction.

The Azure SQL engine manages concurrency between multiple users executing transactions against databases by requesting locks on data. Locks have different modes, such as shared or exclusive. Lock modes define the level of dependency the transaction has on the data. Locks cannot be granted for a transaction if they would conflict with the type of a lock already granted on that data to another transaction. If a transaction requests a lock type that conflicts with a lock that has already been granted on the same data, Azure SQL will pause the requesting transaction until the first lock is released. When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. All locks held by a transaction are released when the transaction completes (either commits or rolls back).

Applications do not typically request locks directly; rather, locks are managed internally by a part of the Azure SQL engine, called the lock manager. When processing a Transact-SQL statement, Azure SQL determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager, a subsystem that exists within the Azure SQL engine. The lock manager grants the locks if there are no conflicting locks held by other transactions.

Azure SQL has multi-granular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking (lock structures are held in memory), Azure SQL lock resources automatically work at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, is expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. However, it has a lower overhead because fewer locks are being maintained. Azure SQL automatically selects the best lock granularity based on the amount of data that it needs to lock, trying to balance between resource costs and concurrency impact.

Azure SQL often must acquire locks at multiple levels of granularity to fully protect a resource. This group of locks at multiple levels of granularity is called a lock hierarchy. For example, to fully protect a read of an index, an instance of the Azure SQL may have to acquire Shared locks on rows and Intent Shared locks on the pages and table. Intent Locks are something used to indicate that, in the future, lock could be acquired on that resource. This information helps the lock manager to avoid future conflicts.

As developers, we can control how our applications are dealing with different isolation levels and concurrency through specific T-SQL statements and client driver APIs like Microsoft.Data.SqlClient or SQL Server JDBC Driver.

In addition to drivers’ APIs and connection-level isolation settings, table hints can also override default behaviors of Azure SQL engine for the duration of data manipulation language (DML) statements’ execution and let you control things like lock type, granularity, and so on by specifying a locking method. For example, using a table hint of type HOLDLOCK (or its equivalent, SERIALIZABLE) makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed. ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken (as opposed to TABLOCK or PAGLOCK).

Note that not all the hints are compatible with different DML command types, and to get all details about this interesting topic, you can follow this link in official product documentation: https://aka.ms/uathaaqh.

As we mentioned, the default isolation level for Azure SQL is Read Committed Snapshot Isolation (RCSI) , and this provides the best trade-off between concurrency and data integrity, letting readers and writers don’t block each other for most scenarios. As a best practice, you should limit the usage of other transaction isolation levels or statement-level table hints to very specific use cases where you need specific behaviors. In all other cases, default settings will work just fine. At the same time, rest assured that if you need to go into great detail and fine-tune even just a single statement, that can be done. This amazing flexibility is unique in the database cloud market!

Natively compiled procedure

Natively compiled Stored Procedures are T-SQL Stored Procedures compiled to machine code, rather than interpreted by the query execution engine as regular Stored Procedures. They work in conjunction with In-Memory OLTP tables in Azure SQL to provide very fast response times for frequently executed queries on performance-critical parts of an application. Performance benefits of using a natively compiled Stored Procedure increases with the number of rows and the amount of logic that is processed by the procedure, and works best when performing aggregations; nested loops joins on large number of row; multi-statement select, insert, update, and delete operations; or complex procedural logic, such as conditional statements and loops.

When we use traditional interpreted Transact-SQL statements to access In-Memory OLTP tables, query processing pipeline looks very similar to disk-based tables, with the exception of the fact that rows are not retrieved from classical buffer pools but from the in-memory engine:

When using natively compiled Stored Procedures, the process is split into two parts. First parser, algebrizer and optimizer create optimized query execution plans for all the queries in the Stored Procedure, and then Stored Procedure gets compiled into a native code and stored in a .dll file:

When the Stored Procedure is invoked, the In-Memory OLTP runtime locates the DLL entry point for the Stored Procedure, machine code in the DLL is executed, and the results are returned to the client:

There are few restrictions on operators supported within a natively compiled Stored Procedure. For example, the syntax SELECT * FROM table is not supported, and you need to specify the complete column list. For more information on these details, you can look at product documentation at https://aka.ms/ncsp.

Optimizing database roundtrips

Reducing the number of roundtrips between your application tier and the database tier is likely the single most important optimization you can think about when designing or troubleshooting an application working with Azure SQL. Wherever your application logic permits, batching operations to Azure SQL Database and Azure SQL Managed Instance significantly improves the performance and scalability of your applications. Batching calls to a remote service is a well-known strategy for increasing performance and scalability. There are fixed processing costs to any interactions with a remote service, such as serialization, network transfer, and deserialization. Packaging many separate transactions into a single batch minimizes these costs.

The multitenant characteristics of Azure SQL mean that the efficiency of the data access layer correlates to the overall scalability of the database. In response to usage in excess of predefined quotas, Azure SQL can reduce throughput or respond with throttling exceptions. Efficiencies, such as batching, enable you to do more work before reaching these limits.

There are several batching techniques we can adopt to make our applications more efficient, and these apply to most programming languages and frameworks. Let’s start expanding on these techniques by starting from various batching strategies you should consider.

If you want to know more