The Bit Bucket

I showed earlier how we can retrieve data from a table using a SELECT clause. It can also be used to determine which columns are returned, and which table the data is being retrieved from. But we don't always want all the rows to be returned. The WHERE clause fixes that.

It's important to understand that the WHERE clause limits the rows returned to ones where the expression in the WHERE clause evaluates to a logical TRUE value.

In the example above, we're returning rows where the Size column contains the string value 370ml.

What often confuses users though is the opposite. If I write:

WHERE Size <> '370ml'

then I'm trying to get all the values except 370ml. Some rows might not have a Size specified i.e. they are NULL. You might expect them to be returned because they aren't equal to 370ml, however, the comparison:

NULL <> '370ml'

doesn't return TRUE. It returns NULL. And because we said that the WHERE clause only returns rows where the expression will be TRUE, those rows aren't returned.

This means that the WHERE clause:

WHERE Size <> '370ml'

will return only rows that have a value for Size and it isn't equal to 370ml.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

This is part 7 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI
• Part 3 looked at the impacts of indexing on locking and blocking
• Part 4 looked at what deadlocks really are and how SQL Server handles them
• Part 5 looked at how applications should handle deadlocks
• Part 6 looked at how to avoid deadlocks in the first place

Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.

The Situation

OK, let's start with the following situation:

Your application is calling a stored procedure, and it regularly ends up being involved in deadlocks. To make this easy to demonstrate, let's create a table of data:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Products;
GO

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_dbo_Products PRIMARY KEY,
    ProductName nvarchar(50) NOT NULL,
    UnitPrice decimal(18,2) NOT NULL,
    OnHandQuantity decimal(18,3) NULL
);
GO

INSERT dbo.Products
(
    ProductID, ProductName, UnitPrice, OnHandQuantity
)
VALUES
    (1, N'Product 1', 12.35, 150),
    (2, N'Product 2', 14.30, 155),
    (3, N'Product 3', 15.80, 165);
GO

Then we'll create a stored procedure that can be used to convert a quantity of one product into another product. This can lead to deadlocks:

CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

Note that I added a delay of 10 seconds between the updates to make it easy for us to generate a deadlock. So let's cause one now.

In one query window, execute the following:

-- Query window 1

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 2,
    @FromProductQuantity = 12.0,
    @ToProductID = 3,
    @ToProductQuantity = 1;

And at the same time, in another query window, execute a similar but different query:

-- Query window 2

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 3,
    @FromProductQuantity = 1,
    @ToProductID = 2,
    @ToProductQuantity = 14.1;

No surprise, we get the dreaded deadlock message in one window:

Adding Retry Logic

So how do we fix this? We can either change this procedure to have retry logic, or we can rename this procedure and call it from a wrapper procedure that has the deadlock retry logic.

Let's do the second option this time.

We'll recreate the procedure as ConvertProduct_Inner:

CREATE OR ALTER PROCEDURE dbo.ConvertProduct_Inner
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

And then we'll add a wrapper procedure:

CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @MAXIMUM_RETRIES int = 5;

    DECLARE @RemainingRetries int = @MAXIMUM_RETRIES;

    WHILE (@RemainingRetries > 0) -- retry update for victim
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            EXEC dbo.ConvertProduct_Inner
                @FromProductID = @FromProductID,
                @FromProductQuantity = @FromProductQuantity,
                @ToProductID = @ToProductID,
                @ToProductQuantity = @ToProductQuantity;

            SET @RemainingRetries = 0;

            COMMIT;
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) -- deadlock victim
            BEGIN
                SET @RemainingRetries -= 1;
                PRINT 'Warning: Deadlock occurred';
                PRINT 'Remaining retries: '
                      + CAST(@RemainingRetries AS varchar(20));
            END
            ELSE BEGIN
                SET @RemainingRetries = -1;
            END;
            IF XACT_STATE() <> 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
      END CATCH;
    END;
END;
GO

Now I'll execute both procedures again, and notice that both complete as expected. One has a warning about a deadlock that had occurred but was retried:

This is great, and is something we couldn't do before SQL Server 2005 so many people weren't used to doing it. However, while this will help at the T-SQL end, most other types of retries need to happen at the client end.

 

 

 

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn't find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item)
@RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

The output has the following columns: UserName, RoleName, PathName, FolderName, ItemName, ItemType.

You can see the procedure in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

Apart from data just in a table SQL server can select other things like the ones shown here:

If I say SELECT 2, it just returns the value 2.

If I say SELECT 'Hello', it just returns Hello.

Both of those are examples of what's called a literal value, which is an exact value that doesn't change.

SELECT 4 + 5 is an example of an expression. This is where we can work something out to get the value that needs to be returned. No surprise, that will return 9 just as you'd.

Finally, we can also select from functions. SYSDATETIME()  is a function that returns the current date and time at the server. We don't have to care about how that works internally, we can just SELECT it, and it'll tell us the current date and time.

So we're able to use expressions and values and both can be returned. Functions, though, are programmable objects, which can be used either as values or even as tables in a FROM clause but we'll see more about that in a later post.

Not all SQL engines do this

It's worth noting that not all SQL database engines allow us to have a SELECT statement without a FROM clause. For example, in Oracle, you can't just say SELECT 4 + 5. Instead, they have a dummy table called dual, which is a single column, single row table with a dummy value. Then you can say SELECT 4 + 5 FROM dual; instead.

I'm glad SQL Server doesn't require this.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

When I'm writing SQL Server scripts, I often need to store the name of SQL Server objects (like databases, schemas, tables, procedures, etc.) in variables.

That can also happen when I'm creating tables. I might need to store a SQL Server object name (like a login name or a user name) in a column of a table.

So which data type should be used? varchar(100), varchar(200), nvarchar(max), etc. etc. ??

The answer is: sysname

sysname is a built-in data type that's currently stored as nvarchar(128).

While you could use nvarchar(128), that wouldn't be a great idea. If the SQL Server team ever need to change the size of object names, you'll have a problem if you'd used nvarchar(128). If, as recommended, you'd used the sysname data type, you'll be fine.

This is part 6 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI
• Part 3 looked at the impacts of indexing on locking and blocking
• Part 4 looked at what deadlocks really are and how SQL Server handles them
• Part 5 looked at how applications should handle deadlocks

Today, though, I want to look at how to try to avoid deadlocks in the first place.

You Can't

Let's start with the bad news. You can't totally avoid deadlocks without doing something like serializing all access to the database.

As I mentioned in Part 5, you need to expect and handle deadlocks when they occur.

Now we have the bad news out of the way, let's look at things that can help to minimize them. Even though we make our applications capable of handling them, I want to see as few of them happening as possible.

Fix the query performance

Part one of this series talked a bit about this. I often see significant blocking issues (and associated deadlocks) completely disappear when the queries run quickly.

The longer that a query runs for, the longer it's likely to hold locks. We want to minimize that time. Indexing is your friend here.

Minimize the locking (volume and duration)

The fewer things that are blocked, and the shorter time that they're blocked, the less chance there are for deadlocks in the first place. There are four key aspects of this:

First, (no surprise), indexing is a key issue here. If you don't give SQL Server suitable indexes to let it find just the data that you need to lock, you shouldn't be surprised when it ends up locking entire partitions or tables.

Second, the design of your tables is also an issue. A properly normalized database schema is easier to work with in terms of minimizing locks.

Third, you need to use the lowest isolation level that works for your queries. Part two of this series talked about using RCSI. Often that will really help. Your biggest pain point though is going to come from applications that insist on using Serializable as an isolation level. Unfortunately, many Microsoft applications default to using this (I'm looking at you Biztalk's SQL Adapter), as do many other things in Windows like Component Services. Whenever you have a choice and it's appropriate, reduce the isolation level to the lowest you can work with.

You might be wondering about the use of NOLOCK here. Clearly it might help if you can deal with the risk. Basically, you're giving up consistency for reduced locking and performance. If you're working only with archive data that isn't changing, it might be an ok option.

If you're working with data that's being actively changed, it's a significant risk. It's hard to explain duplicated or missing rows on reports, and even harder to explain data on your report that doesn't exist in the database because it was rolled back.

I also see people trying to put NOLOCK on INSERT, UPDATE, DELETE. Here's a hint: that's not going to work.

Fourth, you need to hold locks for the shortest time that you possibly can. I'm not talking about holding locks across user interactions, that would be just plain dumb. You need to get as much work done as quickly as possible. If you want high concurrency, holding transactions across multiple round trips to the database server isn't clever, yet I see it all the time.

Don't create a proc to insert an order header, then another proc to insert an order detail line, and then call them one by one within a transaction started by the client application. Instead, pass the whole order (or even multiple orders where it makes sense) directly to a procedure on the SQL Server that does the whole job at once. Table-valued parameters are your friend here.

Locks are typically happening within transactions so you need to keep the transaction durations very short. And that means that you need to manage them properly. Start them only when needed and commit them or roll them back as soon as possible.

The transaction handling in JDBC is a great example of how not to handle transactions.

It turns on chained mode (which means that SQL Server starts a transaction automagically when a data modification occurs), and then just periodically runs a command that says "if there is a transaction, commit it". Please don't do this. At least not if you're looking for high performance and/or concurrency.

Serialize the workload

One useful, (but possibly contentious) method of reducing deadlocks, is to try to ensure that you lock objects in the database in the same order within all your logic.

I remember a site that I went to in the 1980s where they had a rule that tables were always used in alphabetical order. That sounded like a bad idea at the time, and it still sounds like a bad idea.

However, you can make a big difference to deadlocks by using tables in the same logical order every time. For example, if you acquire an order number from a sequence, then using it in an order header, then write the order detail lines, write an audit entry, etc. and keep to that same type of order, you'll have a lot less deadlocks to deal with, as it will naturally serialize the work to be done.

When all else fails

Even with all the above, you still will likely have some deadlocks. Apart from all the handling options, you might consider exerting some control over who loses when a deadlock occurs. Perhaps you have a background task that you don't mind dying and restarting. In that case, you can set its deadlock priority low so it offers itself as a victim, rather than just having SQL Server work out who's written the least to the transaction log.

In the next post, I'll look at retry logic at the server end.