Designing a Transaction and Concurrency Strategy

Practice Labs Module
Time
50 minutes
Difficulty
Intermediate

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: Design the locking granularity level, Design for implicit and explicit transactions, Design for concurrency.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Design the locking granularity level
  2. Design for implicit and explicit transactions
  3. Design for concurrency

Exercise 1 - Design the locking granularity level

Locking hints

Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. The hints specify the type of locking or row versioning the instance of the SQL Server Database Engine uses for the table data. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.

The Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. This means it is possible for a SELECT statement using NOLOCK to be blocked.

It should be noted that even though these hints are available, the query optimizer will generally choose the best types of locks for a given statement. As such, only use these hints when necessary.

  • PAGLOCK forces the lock manager to take locks of the appropriate mode at the page level instead of the row, key or table locks which might normally be acquired. This hint is ignored for SNAPSHOT isolation unless if other hints are declared along with it.
  • ROWLOCK forces the lock manager to take locks of the appropriate mode at the row level instead of the page or table locks which might normally be acquired. This hint is ignored for SNAPSHOT isolation unless if other hints are declared along with it.
  • TABLOCK forces the lock manager to take locks of the appropriate mode at the table level instead of the row, key or page locks which might normally be acquired. This hint should be considered for potential utilization with inserting into heaps and with bulk inserts using the OPENROWSET provider. See the BOL page for more information.
  • TABLOCKX forces the lock manager to take an exclusive lock of the table instead of the appropriate type of lock on the row, key or page which might normally be acquired.
  • UPDLOCK forces the lock manager to take an update lock. This lock will be held until the transaction completes.
  • READPAST tells the lock manager to skip past any rows or pages that are currently locked. This hint is primarily used for queuing systems and has many rules around when it is valid that should be considered before attempting to use it. Note that this is not technically a “granularity” hint.
  • XLOCK forces the lock manager to take exclusive locks. These locks will be held until the transaction completes. This lock type can be specified with ROWLOCK, PAGLOCK or TABLOCK to exclusively lock at the given level.
  • NOWAIT returns a message as soon as a lock is encountered. Equivalent to specifying SET LOCK_TIMEOUT 0. Note that this is not technically a “granularity” hint.

Exercise 2 - Design for implicit and explicit transactions

Nested transactions

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

Exercise 3 - Design for concurrency

Hints

You can specify a range of table-level locking hints by using the SELECT, INSERT, UPDATE, and DELETE statements to modify the default locking behavior of Microsoft SQL Server. Use locking hints only when absolutely necessary. They can adversely affect concurrency.

Transaction isolation level

The isolation level used during the execution of SQL statements determines the degree of isolation of the active group from the concurrently executing activation groups.

Therefore, when activation group G executes an SQL statement the isolation level determines:

  • The degree to which rows retrieved by G and database changes made by G are available to other concurrently executing activation groups.
  • The degree to which database changes made by concurrently executing activation groups can affect G.
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Read uncommitted When this isolation level is used, SQL Server does not issue shared locks while reading data. So you can read an uncommitted transaction which might get rolled back later. This isolation level is called dirty read. It is the lowest isolation level and ensures that physically corrupt data has not been read.

Read committed This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. This ensures that physically corrupt data is not read and never reads data that another application has changed which is not yet committed but it does not guarantee that the data will not be changed before the end of the transaction.

Repeatable read When this isolation level is used, the dirty reads and non-repeatable reads cannot occur. It means that locks are placed on all data that is used in a query and another transaction cannot update the data.

SNAPSHOT

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Serializable This is the most restrictive isolation level. When this level is used, phantom values cannot occur. It prevents users from updating or inserting rows into the data set until the transaction is complete.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.