Locks in SQL Server

        SQL Server handles all locking decisions. It chooses which type of lock to use after it determines the query plan. However, the way you write a query or transaction can affect the type of lock the server chooses. You can also force the server to make certain locks more or less restrictive by including the holdlock, noholdlock, or shared keywords with your queries or by changing the transaction's isolation level. These options are described later in this chapter.

SQL Server has two levels of locking: page locks and table locks. Page locks are generally less restrictive (or smaller) than table locks. A page lock locks all of the rows on the page; table locks lock entire tables. SQL Server attempts to use page locks as frequently as possible to reduce the contention for data among users and to improve concurrency.

SQL Server uses table locks to provide more efficient locking when it determines that an entire table, or most of a table's pages, will be accessed by a statement. Locking strategy is directly tied to the query plan, so the query plan can be as important for its locking strategies as for its I/O implications. If an update or delete statement has no useful index, it does a table scan and acquires a table lock. For example, the following statement generates a table lock:

update account set balance = balance * 1.05

If the update or delete statement uses an index, it begins by acquiring page locks, and only attempts to acquire a table lock if a large number of rows are affected.

Whenever possible, SQL Server tries to satisfy requests with page locks. However, once a statement accumulates more page locks than the lock promotion threshold allows, SQL Server tries to issue a table lock on that object. If it succeeds, the page locks are no longer necessary and are released.

Table locks also provide a way to avoid lock collisions at the page level. SQL Server automatically uses table locks for some commands.

Page Locks

The following describes the different types of page locks:

Shared locks

SQL Server applies shared locks for read operations. If a shared lock has been applied to a data page, other transactions can also acquire a shared lock even when the first transaction is not finished. However, no transaction can acquire an exclusive lock on the page until all shared locks on it are released. That is, many transactions can simultaneously read the page, but no one can write to it while the shared lock exists.

By default, SQL Server releases shared page locks after the scan is complete on the page. It does not hold them until the statement completes or until the end of its transaction. Transactions that need an exclusive page lock wait or "block" for the release of the shared page locks before continuing.

Exclusive locks

SQL Server applies exclusive locks for data modification operations. When a transaction gets an exclusive lock, other transactions cannot acquire a lock of any kind on the page until the exclusive lock is released at the end of its transaction. Those other transactions wait or "block" until the exclusive lock is released, before continuing.

Update locks

SQL Server applies update locks during the initial portion of an update, delete, or fetch (for cursors declared for update) operation when the pages are being read. The update locks allow shared locks on the page, but do not allow other update or exclusive locks. This is an internal lock to help avoid deadlocks. Later, if the pages need to be changed and no other shared locks exist on the pages, the update locks are promoted to exclusive locks.

In general, read operations acquire shared locks, and write operations acquire exclusive locks. However, SQL Server can apply page-level exclusive and update locks only if the column used in the search argument is part of an index.

The following examples show what kind of page locks SQL Server uses for the respective statement (assuming that indexes are used on the search arguments):

select balance from account      Shared page lock

where acct_number = 25

insert account values(34, 500)   Exclusive page lock

delete account                   Update page locks

where balance < 0                Exclusive page locks

update account set balance = 0   Update page lock

where acct_number = 25           Exclusive page lock

Table Locks

The following describes the types of table locks.

Intent lock

An intent lock indicates that certain types of page-level locks are currently held in a table. SQL Server applies an intent table lock with each shared or exclusive page lock, so intent locks can be either intent exclusive locks or intent shared locks. Setting an intent lock prevents other transactions from subsequently acquiring a shared or exclusive lock on the table that contains that locked page. Intent locks are held as long as the concurrent page locks are in effect.

Shared lock

This lock is similar to the shared page lock, except that it affects the entire table. For example, SQL Server applies shared table locks with a select with holdlock that does not use an index, and for thecreate nonclustered index statement.

Exclusive lock

This lock is similar to the exclusive page lock, except that it affects the entire table. For example, SQL Server applies exclusive table locks during the create clustered index command. update anddelete statements generate exclusive table locks if their search arguments do not reference indexed columns of the object.

The following examples show the respective page and table locks issued for each statement (assuming that indexes are used in their search arguments):

select balance from account     Intent shared table lock

where acct_number = 25          Shared page lock

insert account values(34, 500)  Intent exclusive table lock

                                Exclusive page lock

delete account                  Intent exclusive table lock

where balance < 0               Update page locks

                                Exclusive page locks

This next example assumes that there is no index on acct_number; otherwise, SQL Server would attempt to issue page locks for the statement:

update account set balance = 0  Exclusive table lock

where acct_number = 25

Demand Locks

SQL Server sets a demand lock to indicate that a transaction is next in line to lock a table or page. Demand locks prevent any more shared locks from being set. This avoids situations in which read transactions acquire overlapping shared locks, which monopolize a table or page so that a write transaction waits indefinitely for its exclusive lock.

After waiting on several different read transactions, SQL Server gives a demand lock to the write transaction. As soon as the existing read transactions finish, the write transaction is allowed to proceed. Any new read transactions must then wait for the write transaction to finish, when its exclusive lock is released.

Demand locks are internal processes, and are not visible when using sp_lock.

Cloning a SQL Server Database

         The following script will backup and restore a database to the same server with a different name. Tested on  SQL 2012/2008. You just need to set the 4 variables at the top of the script to specify source and destination along with directories to store the backup and restored database files.

USE master;  
GO

/*USER INPUT*******************/
DECLARE @SourceDb varchar(200) = 'SourceDatabaseName'  
DECLARE @DestinationDb VARCHAR(200) = 'DestinationdatabaseName'  
DECLARE @BackupDirectory VARCHAR(200) = 'c:\SQLBackups\'  
DECLARE @UserDbDirectory VARCHAR(200) = 'c:\UserDbs\'  
/******************************/

DECLARE @LogicalFileName VARCHAR(200) = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@SourceDb)  AND type <> 1)  
DECLARE @LogicalLogFileName VARCHAR(200) = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@SourceDb)  AND type = 1)  
DECLARE @BackupFile VARCHAR(200) = @BackupDirectory + @SourceDb + '.bak'            
DECLARE @Query NVARCHAR(1000)  
SET @query = 'BACKUP DATABASE ' + @SourceDb + ' TO DISK = ' + QUOTENAME(@BackupFile,'''')  
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DestinationDb + ' FROM DISK = ' + QUOTENAME(@BackupFile,'''')  
SET @query = @query + ' WITH MOVE ' + QUOTENAME(@LogicalFileName,'''') + ' TO ' + QUOTENAME(@UserDbDirectory + @DestinationDb + '.mdf' ,'''')  
SET @query = @query + ' , MOVE ' + QUOTENAME(@LogicalLogFileName,'''') + ' TO ' + QUOTENAME(@UserDbDirectory + @DestinationDb + '_log.ldf','''')  
EXEC (@query)  

SQL Server Isolation Levels

       Isolation level is required to isolate a resource and protect it from other transactions. This is achieved with the help of locks but what locks are needed and how they can be established is decided on the isolation level set on the database level. If low level of Isolation is set, it allows multiple users to access the resources concurrently but it may result in many  concurrency related problems like phantom reads, dirty reads etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less number of concurrent access and it may result in data blocking.

Isolation levels in SQL Server control the way locking works between transactions.

SQL Server 2008 supports the following isolation levels

·         Read Uncommitted

·         Read Committed (The default)

·         Repeatable Read

·         Serializable

·         Snapshot

Before I run through each of these in detail you may want to create a new database to run the examples, run the following script on the new database to create the sample data. 

Note : You’ll also want to drop the IsolationTests table and re-run this script before each example to reset the data.

CREATE TABLE IsolationTests 

(

    Id INT IDENTITY,

    Col1 INT,

    Col2 INT,

    Col3 INTupdate te

)

INSERT INTO IsolationTests(Col1,Col2,Col3) 

SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3

Also before we go any further it is important to understand these terms….

1. Dirty reads: - This situation happens when a transaction tries to read a data by some other concurrent transaction which is not committed yet. There is a risk, that this other transaction may never be committed, leaving the original transaction with wrong data.

2. Lost updates: - It happens when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by the some another transaction updating the same data concurrently. 

3. Repeatable reads: - Repeatable reads condition occurs when a transaction tries to read a data multiple times and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data. 

4. Phantom reads:-This condition happen when a transaction needs to execute a same query twice (or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set reruns by  the execution of the query.

Read Uncommitted

             This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.

As you can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could be in a half way state if an update was running in another transaction causing some of your rows to come back with the updated values and some not to. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

To see read uncommitted in action lets run Query1 in one tab of Management Studio and then quickly run Query2 in another tab before Query1 completes.

Query1

BEGIN TRAN 

UPDATE IsolationTests SET Col1 = 2 

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10' 

ROLLBACK

Query2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM IsolationTests

Notice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn't wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.

There is a syntactic shortcut for querying data using the read uncommitted isolation level by using the NOLOCK table hint. You could change the above Query2 to look like this and it would do the exact same thing.

SELECT * FROM IsolationTests WITH(NOLOCK)

Read Committed

This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes. Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and Repeatable reads.

You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by running the following Queries in separate tabs as you did with Read Uncommitted.

Query1

BEGIN TRAN 

UPDATE Tests SET Col1 = 2 

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10' 

ROLLBACK

Query2

SELECT * FROM IsolationTests

Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback. The reason no isolation level was specified is because Read Committed is the default isolation level for SQL Server. If you want to check what isolation level you are running under you can run “DBCC useroptions”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.

Repeatable Read

This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try to modify these records are forced to wait for the read transaction to complete. This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

As before run Query1 then while its running run Query2

Query1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 

BEGIN TRAN 

SELECT * FROM IsolationTests 

WAITFOR DELAY '00:00:10' 

SELECT * FROM IsolationTests 

ROLLBACK

Query2

UPDATE IsolationTests SET Col1 = -1

Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

Serializable

This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

You know the drill by now run these queries side by side…

Query1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN 

SELECT * FROM IsolationTests 

WAITFOR DELAY '00:00:10' 

SELECT * FROM IsolationTests 

ROLLBACK

Query2

INSERT INTO IsolationTests(Col1,Col2,Col3) 

VALUES (100,100,100)

You’ll see that the insert in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.

Snapshot

This provides the same guarantees as serializable. So what's the difference? Well it’s more in the way it works, using snapshot doesn't block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

So on the plus side your not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.

To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE IsolationTests 

SET ALLOW_SNAPSHOT_ISOLATION ON

If you rerun the examples from serializable but change the isolation level to snapshot you will notice that you still get the same data returned but Query2 no longer waits for Query1 to complete.

Summary

       You should now have a good idea how each of the different isolation levels work. You can see how the higher the level you use the less concurrency you are offering and the more blocking you bring to the table. You should always try to use the lowest isolation level you can which is usually read committed.

Computed Columns

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

CREATE TABLE dbo.Products

(

    ProductID int IDENTITY (1,1) NOT NULL

  , QtyAvailable smallint

  , UnitPrice money

  , InventoryValue AS QtyAvailable * UnitPrice

);

-- Insert values into the table.

INSERT INTO dbo.Products (QtyAvailable, UnitPrice)

VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.

SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue

FROM dbo.Products;

CREATE TABLE t2 (a int, b int, c int, x float, 

   y AS CASE x 

         WHEN 0 THEN a 

         WHEN 1 THEN b 

         ELSE c 

      END)

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

·         Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

·         A computed column cannot be the target of an INSERT or UPDATE statement.

Note:There is NO way to alter computed column. You will have to drop and recreate it

SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more scenarios  like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command.

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2

Step 1: Get the current database files Logical Name and Physical Location

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation

, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID('AdventureWorks2012');

 Step 2: Take the Database offline

USE master

GO

ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

Note: In the above T-SQL query, I used WITH ROLLBACK IMMEDIATE option. Please be careful when using this option as it rolls back those incomplete transactions immediately. You may exclude this option, but have to wait till all the transactions are committed to take the database offline.

Step 3: Move the database files physically in the Windows OS to the new location

Step 4: Use ALTER DATABASE to modify the FILENAME to new location forevery file moved

Only one file can be moved at a time using ALTER DATABASE.

USE master

GO

ALTER DATABASE AdventureWorks2012

MODIFY FILE 

( NAME = AdventureWorks2012_Data, 

FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path

USE master

GO

ALTER DATABASE AdventureWorks2012 

MODIFY FILE 

( NAME = AdventureWorks2012_Log, 

FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path

Step 5: Set the database ONLINE

USE master

GO

ALTER DATABASE AdventureWorks2012 SET ONLINE;

Step 6: Now, verify the database files Physical location

USE master

GO

SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status 

FROM sys.master_files 

WHERE database_id = DB_ID('AdventureWorks2012');