Members-only Printer-friendly version

1. Understanding T-SQL Queries

The main purpose of creating and maintaining a database is to store data and later make it available through the use of queries. A query is a request for data in a database. Queries in Microsoft SQL Server are written using the Transact-SQL language. There are four statements that form the core of Transact-SQL queries. Understanding these four statements means understanding a big deal of T-SQL. Let’s look at each one at a time.

1.1 SELECT

1.1.1. Overview of the SELECT statement

The SELECT statement, the back-bone of T-SQL queries, is used to retrieve existing data from a SQL Server database. The statement retrieves data from the database and presents it back to the user in one or more result sets. A result set is a tabular arrangement of the data requested by the SELECT statement. Like any other SQL Server table, the result set comprises columns and rows. The full syntax of the SELECT statement is complex and lengthy, but the main clauses can be summarized in seven parts. Understanding how the SELECT query works is very important. All other queries are based directly or indirectly on the SELECT query. The following figure illustrates the main parts of the SELECT statement.

1. The SELECT clause: the SELECT clause is a required clause and is followed by a select list. A select list describes the columns of the result set. It’s a column-separated list of expressions. Each expression points to the source of the data and an alias used as a column title in the result set (if required). Columns returned in the result set are named after the column name in the table by default. If you want the columns returned in the result set to have a different name, you’ll have to define an alias for the column.

2. The INTO clause: the INTO clause is an optional clause and is used when you want to create a new table and insert the resulting rows from the query into it.

3. The FROM clause: the FROM clause is a required clause and is a comma-separated list of table names, view names, and join clauses. Use the FROM clause to:
a. List the tables and/or views containing the columns referenced in the select list. The table or view names can be aliased using the AS clause.
b. Join tables.

4. The WHERE clause: the WHERE clause in an optional clause and is used as a filter that defines the conditions each row in the source tables must meet to qualify for the result set. Only rows that meet the conditions specified in the search_criteria contribute data to the result set. Data from rows that do not meet the conditions are not used.

5. The GROUP BY clause: The GROUP BY clause partitions the result set into groups based on the values in the columns of the group_by_list. For example, the customers table contains a State column. Grouping by the state column will partition or group the results by each customer’s state. This means that customers from California will be listed as group, those from Washington as another, and so on and so forth.

6. The HAVING clause: the HAVING clause is optional and is used as an additional filter applied to the result set. The HAVING clause’s filter is applied only after the result set has been passed through the FROM, WHERE, and GROUP BY clauses.

7. The ORDER BY clause: the ORDER BY clause is optional and is used to define the order in which the rows in the result set are sorted. Use the order_list to specify the result set columns that make up the sort list.

1.1.2. Using the select list

A select list is used to define what information you want from a table and how. In a select list you can specify which column you want to include in the result set and in which order you want them to appear. You can also control the final format of the result set by either limiting the number of rows returned in the result set or by eliminating redundant records or duplicates. When you use the select list, you’re either dealing with table columns, or table rows. Let’s look at each one at a time.

Table columns and the select list

There are two ways you can select columns in a select list. The first is to select all the columns in the table. You can do this by using the asterisks (*) sign. For example, ”SELECT * FROM Customers”, would return all rows and columns in the customers table. The second way is to select specific columns from a table. This is done by listing the column names delimited by commas. For example, ”SELECT Name, Phone, Address FROM Customers”, would return only the name, phone, and address columns for all the rows in customers table. So, if the customers table included an Email column, it would not be returned in the result set.

Table rows and the select list

We’ve just looked at the different ways to select columns in a table. We’ll now look at the different ways to select specific rows from a table. There are two ways you can select specific rows within a table, or in other words, limit the number of rows returned in the result set.

The first way is to select a specific number of rows from a table. You can do this by using the TOP (n) keyword. The TOP keyword specifies that the first n rows of the result set are returned, where n is the number of rows to return. For example, “SELECT TOP 10 * FROM Customers”, would return all columns for the first 10 rows in the customers table only.

The second way is to eliminate duplicate rows from the result set. You use the DISTINCT keyword to do this. For example, a vacations table includes information about each employee who went on a vacation. Information includes the employee’s name, and his vacation destination. It is likely that more than one employee has went to any one destination. To know the different destinations employee’s have went to without including duplicates you would write “SELECT DISTINCT Destination FROM Vacations”.


1.1.3 Using the FROM clause

The FROM clause is the heart or center of every query. It is the clause that tells SQL Server how and which tables it should read for the data required in the select list. The FROM clause has a table_list. The table list can either be used for listing the names of the tables containing the columns used in the select list or joining tables with others. The first function is very easy to do. The following statement would select all columns from the customers table; “SELECT * FROM Customers”. The table name “Customers” placed after the FROM clause tells SQL Server to read the data from that table. SQL Server will produce an error if the table name(s) or column name(s) are wrong. To simply things and better the readability of a query, you can use aliases for table names in a SQL query. Table aliases can be assigned with or without the AS keyword. For example, “SELECT * FROM Customers C” and “SELECT * FROM Customers AS C” are both permissible. What is not permissible is to assign an alias to a table and later use the tables name instead of the alias when referencing the table.
The other function of the FROM clause is joining tables. More detailed information about joins will be discussed later.


1.1.4. Filtering the result set

Result sets can be filtered to include only data that meets a certain criteria. The WHERE and HAVING clauses are used to filter data in a SQL Server query. Data that meets with the criteria defined by either clause are said to be qualified to be included in the result set. Both clauses have a search_condition which can include one or more than one search criteria. The HAVING clause is typically used in conjunction with the GROUP BY clause. Although it can be used without the GROUP BY clause, it isn’t necessary when there is no grouping operation in the query.
The statement SELECT * FROM Customers WHERE State = ‘CA’ would limit the result set to include customers from the State of California only. There are four main types of search conditions that can be used as filter qualifications:

1. Comparison Operators: comparison operators (such as =, < >, <, and >). For example, this query retrieves the rows for employees whose salaries are more than $500

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > 500

2. Lists: suppose you want to retrieve rows for employees whose salaries are 100, 500, or 1000 dollars? This is possible by using List Criteria’s (IN, NOT IN). The values following the IN keyword must be separated by commas and closed in parentheses. The following example retrieves the rows for employees whose salaries are 100, 500, or 1000 dollars.

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary IN (100,500,1000)

3. Range: There are two types of range search conditions in SQL Server; inclusive, and exclusive. An Inclusive range returns any values that match two specified values including the values that limit the search. An Exclusive range returns values that match two specified values excluding the values that limit the search. An example of an exclusive range search condition is the less than and greater than signs (< >). The following example returns all employees who were hired between 01/01/2000 and 31/12/2000 excluding employees who were hired on the two search range dates (01/01/2000 and 31/12/2000).

SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate > 01/01/2000 AND HireDate < 31/12/2002

An Inclusive range is defined by using the (BETWEEN, NOT BETWEEN) keywords. The BETWEEN keyword is like using a less-than-or-equal-to and greater-than-or-equal-to signs (<= >=) only that it’s easier and recommended for inclusive range searches. The following example retrieves rows for employees who were hired between 01/01/2000 and 31/12/2002 including the range limit dates.

SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate BETWEEN 01/01/2000 AND 31/12/2002

4. Pattern Matches: suppose you want to retrieve rows for products whose names start with SH. A possible way is to use complex functions such as SUBSTRING, but that’s not the best solution. SQL Server includes a statement specifically used for that purpose (LIKE, NOT LIKE). The following example retrieves rows for products whose names start with SH.

SELECT ProductName, Price
FROM Products
WHERE ProductName LIKE ‘SH%’

1.1.5. Sorting the result set

The ORDER BY clause in a query is used to sort a result set. A sort can be ascending (ASC) or descending (DESC). If neither is specified, ascending (ASC) is assumed. When you sort a result set, you sort specific columns in a specific way. The ORDER BY clause has an order_by_expression parameter which is required. Use the order_by_expression to specify which column(s) to sort. You can define a sorting mechanism too. You can either sort the values from lowest to highest (Ascending ASC), or from highest to lowest (Descending DESC). Multiple sort columns can be specified. If multiple sort columns are specified, the sort is nested. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. The following three examples show the different ways an ORDER BY clause can be used.

SELECT ProductName, Price
FROM Products
ORDER BY Price

(In the above query, the result set will include the product name and price. The price column is sorted using the ORDER BY clause. Since ascending (ASC) is the default, values in the price column are sorted from lowest to highest)

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC

(In the above query, the result set will include the product name and price. The price column is sorted using the ORDER BY clause. This time, the values in the price column are sorted from highest to lowest, DESC)

SELECT OrderID, ProductID, Quantity
FROM Products
ORDER BY OrderID DESC, ProductID, Quantity

(In the above query, the result set will include the Order ID, Product ID and Quantity. In this query, multiple columns are included in the sort. The result set is first sorted by the Order ID in descending order, then by the product ID in ascending order, and finally, by the quantity in ascending order)

1.1.6. Join Fundamentals

In the previous sections, we’ve been looking at different ways to query a single table. There are times when you’ll need information from more than one table in a single result set. For example, a customers table contains information about customers. And an orders table includes information about customer orders. Information in the orders table includes the product ordered, the quantity and the sales price. You want to know the sales activity of each customer. Information you want includes the products you sold to the customer, the total sales price, and of course the customer’s information. Simply put, the solution to that problem would be to match every customer to his orders and then add up the sales values and quantities for each customer. It’s possible to include two tables in the FROM clause without using a Join. But this is how it looks like.

As you can see, the customers table is on its own so is the orders table. These two tables shown in Figure 3.2 are not related nor matched. You can match records from the two tables but you’d have to use some complex WHERE clauses and sub queries.

Matching information in two different tables into one result set is accomplished by using joins in SQL Server. Look at the following table. Notice how each order is matched to its customer and vice versa.

When you join two tables, you create a logical relationship between them. You can also specify joining conditions that tables have to meet in order to qualify in the join. A Join is defined in the FROM clause. The following is the syntax for Joins.

FROM first_table JOIN_TYPE second_table [ON (join_condition)]

There are three types of joins in SQL Server; Inner Joins, Outer Joins, Cross Joins, and Self-Joins. We’ll look at each one at a time.

Inner Joins

An inner join is a join in which the values in the columns being joined are compared using a comparison operator. An Inner Join returns all columns in both tables, and returns only the rows for which there is an equal value in the join column. In our previous example, we looked at how the customers and orders table were joined. Let’s take the same example again. This time, we’ll see how rows that don’t match the join condition are excluded from the result set.

Look at Figure 3.4, Sam doesn’t have any matching records in the Orders table. This means that Sam hasn’t placed any orders. If we INNER JOIN these two tables and define the CustomerID as the join column, rows in the customers table that don’t have matching rows in the orders table will be removed and vice versa. The result set will look like this.

Look at the statement in Figure 3.5. The “JOIN” keyword is assumed to be an “INNER JOIN” by default. Since Sam (CustomerID:3) didn’t have any matching records in the orders table, his record in the customers table didn’t qualify in the joined result set. An Inner Join is some times used to make a self-join between two instances of the same table. This is done by joining the table to itself using two aliases. A self join is useful when you want to find matching values for different rows. An example would be finding out which employees live in the zip code area as their managers. Since every employee has a ZIP code column, joining the table to itself would produce the required result.

Outer Joins

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. There are three types of Outer Joins; Left Outer Join, Right Outer Join, and Full Outer Join. Let’s look at each one at a time.

Left Outer Join

Left Outer Joins retrieves all rows from the left table and only those matching from the right. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table. In our previous example, if we defined a LEFT OUTER join instead of an INNER join, the result set would look like this. Notice how the orders table columns are filled with the NULL value.

Right Outer Join

A right outer join is the exact opposite of the left outer join. It indicates that all rows from the right (second) table are to be included in the result set, regardless of whether there is matching data in the left (first) table. When a row in the right table has no matching rows in the left table, the associated result set row contains null values for all select list columns coming from the left table. The following is an example of a right outer join:

SELECT * FROM Customers RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Full Outer Join

To retain the non-matching information by including non-matching rows in the results of a join, use a FULL OUTER JOIN. A FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value. The following is an example of a FULL OUTER JOIN.

SELECT * FROM Customers FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Cross Joins

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. For example, if the customers table contains 11 rows and the orders table contains 22 rows, the Cartesian product would be 242 (11 times 22). The following is an example of a cross join.

SELECT * FROM Customers CROSS JOIN Orders

1.2 UPDATE

The UPDATE statement is used to change existing data values in single rows, groups of rows, or all the rows in a table or view. An UPDATE statement can change the data in only one base table at a time. The UPDATE statement is a very complex T-SQL statement, but we’ll look into the most important parts only. The UPDATE Statement has three main parts.



Let’s look at each one at a time.

1- UPDATE: The first clause is used to define the name of the table to update.
2- SET: the SET clause is a comma-separated list of the columns to be updated and the new value for each column, in the form column_name = expression. The values supplied can either be constants or values selected from columns in another table.
3- WHERE: the WHERE clause is also an optional clause. Use this clause to specify the rows you wish to update. For example, updating a table without a WHERE clause causes the update to be reflected on all rows in a table. If you want to update a limited set of data, as is the case usually, use the WHERE clause to limit the rows that qualify in the update. The following are examples of the UPDATE query.

Example 1. Using a simple UPDATE

UPDATE Customers
SET Country = ‘USA’

Example 2. Using an UPDATE with a WHERE clause

UPDATE Customers
SET Country = ‘U.S.A’
WHERE Country = ‘USA’

The query above updates the country column of only customers who have the value ‘USA’ in the country column.

1.3 INSERT

The INSERT statement adds one or more new rows to a table. The INSERT statement syntax is as follows:

INSERT [INTO] table_or_view [(column_list)] data_values

The statement causes the data_values to be inserted as one or more rows into the specified table. The column_list is a list of column names separated by commas, which can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data. This means that you must specify a value for every existing column in the table unless the column allows NULL values or has a DEFAULT constraint. If the column_list is specified, data values must be supplied in the order they were listed in the column_list. The number of data values must be the same as the number of columns, and the data type, precision, and scale of each data value must match those of the corresponding column. There are two ways to specify the data values but we’ll look into one method only. The VALUES clause method. The VALUES keyword specifies the values for one row of a table. The values are specified as a comma-separated list of expressions whose data type, precision, and scale must be the same as or convertible to the corresponding column in the column_list. If a column_list is not specified, the values must be specified in the same sequence as the columns in the table. If a column_list is specified, the values must be specified in the same sequence as the columns in the column_list. For example, the following statement inserts a new customer in the customers table. This query assumes that the customers table includes the CustomerName and Country column only.

INSERT INTO Customers VALUES(‘Sam’,’USA’)

The following query inserts a new customer in the customers table. This time, the customers table includes a CustomerName, Phone, and Country columns. The query in the first example leaves out the Phone column. This is done by defining a column_list. The queries in the second and third examples will not work either because the data values are not supplied for all the columns in the table or the data values and column_list sequence don’t match.

Example One:

INSERT INTO Customers(CustomerName,Country) VALUES(‘Sam’,’USA’)

Example Two (Won’t Work):

INSERT INTO Customers VALUES(‘Sam’,’USA’)

Example Three (Won’t Work):

INSERT INTO Customers(CustomerName,Phone,Country) VALUES(‘Sam’,’USA’)

1.4 DELETE

The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:

DELETE table_or_view WHERE search_condition

The table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted. Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement. The DELTE query deletes rows, not columns. So the DELETE query doesn’t have a column list. DELETE * or DELETE CustomerName are unacceptable statements. The following example deletes rows from the customers table. The first example deletes all rows from the customers table. The second example deletes only customers who live in the U.S.

Example Two:

DELETE Customers

Example Three:

DELETE Customers WHERE Country = ‘U.S.A.’

2. Transactions Overview

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

Atomicity
A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them are performed.

Consistency
When a transaction is completed, it must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity.

Isolation
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed. Transactions with isolation don’t see data in an intermediate state or “Dirty Data”.

Durability
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

There are three main types on transactions in SQL Server; autocommit, implicit and explicit. Autocommit transactions are the default of SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions. Implicit transactions are invoked using the SET IMPLICIT_TRANSACTIONS ON statement. In Implicit transactions, every statement automatically starts a new transaction. When that transaction is completed, the next statement starts a new transaction. An explicit transaction is one in which you explicitly define both the start and end of the transaction. An explicit transaction can also be called user-defined transactions.

There are three main commands used to start, stop or rollback transactions.

BEGIN TRAN: marks the starting point of an explicit transaction for a connection.

COMMIT TRAN: Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.

ROLLBACK TRAN: Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.

3. Cursors

Data Integrity is major aspect in client/server environments where multiple users concurrently access data resources. In SQL Server, cursors are provided to ensure the usability and integrity of data returned in result sets. In other words, a cursor is an extension of a result set. In a simple result set, data is retrieved from a data repository and represented as one or more rows to a user or application. In a complex client/server environment, users will concurrently access data, thus making data available to more than one user at a time. If a single piece of information is made available to more than one user at a time, lost updates, dirty reads and other kinds of concurrency problems are encountered. In order to prevent such situations, cursors with effective locking mechanisms must be used. There are three types of cursors in SQL Server; T-SQL cursors, API cursors, and Client Side cursors. We will focus on T-SQL cursors only for now.

3.1 Transact-SQL Cursors

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts. T-SQL cursors make the contents of more than one result set available to other T-SQL statements. There are four types of T-SQL cursors, Forward-only, Static, Keyset-driven, and Dynamic:

Forward-Only Cursors

There are three main points that make forward-only cursors different from other T-SQL cursors and they are:

• Forward-only cursors are not scrollable
• The rows are not retrieved from the database until they are fetched.
• The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.
• Once the rows are fetched, since no scrolling is supported, changes made by other users are not visible.

Static Cursors

A static cursor is like using an offline page from the internet. Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in the tempdb database. The cursor has the following main characteristics:

• Static cursors are scrollable
• The complete result set of a static cursor is built in tempdb when the cursor is opened.
• A static cursor is read-only and always displays the result set as it was when the cursor was opened.
• The cursor does not reflect any changes made in the database. Rows deleted from the database after the cursor was opened are displayed. Rows inserted into the database after the cursor was opened are also displayed.
• No changes are reflected unless the cursor is closed and reopened.
• Some API’s identify the static cursor as a snapshot cursor.

Dynamic Cursors

Dynamic cursors are the exact opposite of Static cursors. Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb. The following are the characteristics of a Dynamic cursor:

• Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor.
• All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

Keyset-driven Cursors

Keyset-driven cursors lie in between Dynamic cursors and Static cursors. They detect most changes but at less expense than Dynamic cursors. The following are the characteristics of a Keyset-driven cursor:

• Keyset-driven cursors are scrollable
• Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened.
• Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened.
• An attempt made to fetch deleted rows after the cursor was opened returns a “row missing” status.
  

3.2 Creating T-SQL Cursors

The DECLARE CURSOR T-SQL statement is used to create cursors in SQL Sever. The following is the DECLARE CURSOR statement syntax.

For simplicity reasons, the statement syntax shown in Figure 3.8 is a combination of the declaration, opening, closing, and scrolling of cursors collectively known as the “declare cursor statement syntax”.

1. The first part of a cursor statement is always the declaration. It’s the part where you define the type and properties and allocate system and memory resources for the cursor. The cursor_name is the name of the cursor used throughout the session. Every session can use only one instance of a cursor name. Cursor names must conform to rules for identifiers. The cursor_type is the type of the cursor; Static, Dynamic, Keyset, or Forward-only. The select_statement is the data the cursor will use and make available to other resources. SELECT INTO statements are not allowed in cursor declarations. Any other valid select statement can be used.

2. Once you declare the cursor, memory and resources are allocated for the cursor. In order to use the cursor, you have to open the cursor. Cursor_name is the name of the cursor you want to open/start.

3. Once you open the cursor, you need to scroll through the data. There are four ways to scroll a cursor. NEXT Returns the result row immediately following the current row. PRIOR Returns the result row immediately preceding the current row. FIRST Returns the first row in the cursor and makes it the current row. LAST Returns the last row in the cursor and makes it the current row. The cursor_name is the name of the cursor you want to fetch the data from. Before you can fetch data, a cursor must be opened.

4. Closing the cursor once the cursors job is complete is a very important step. Just like how declaration is important to allocate resources, closing is important to free up system resources. If you close a cursor, you can open it again without any declaration. If you deacllocate a cursor, you remove it from the memory and free up the resources. You have to declare it again in order to open it.

3.3 Cursor Locking

Locking ensures transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results. Locking addresses the solution to one main problem; concurrency. Concurrency is when multiple users access data at the same time. If locking is not available, several problems may occur referred to as concurrency problems. Let’s take a look at the different types of concurrency problems.

1. Lost Update: Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. For example, two airline reservation agents retrieve available seats on a specific flight. Agent1 reserves the seat for customer1. Then agent2 reserves the seat for customer2 thus resulting in a lost update for customer1’s reservation. This problem can be prevented if a retrieved seat can not be retrieved again by another agent until the agent either reserves the seat or cancels the transaction.

2. Uncommitted Dependency (Dirty Read): Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. For example, an airline reservation agent request available seats on a plane. Concurrently, another agent requests available seats on the same plane. If there was only one available seat, each reservation agent can inform the customer that a seat is available on the flight. Now suppose agent1 reserves the seat for customer1 while agent2 and the customer2 are negotiating on ticket price and other issues. Once customer2 is ready and agent2 decides to reserve the seat, there is no seat available on the flight because agent1 has already reserved the seat for customer1.

3. Inconsistent Analysis (Non-repeatable reads): Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. Inconsistent analysis is similar to uncommitted dependency however, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. For example, a travel agency manager wants to know which seat a specific customer has reserved. Customer1 is currently reserving a seat on a flight. But because he changes his mind often, he asked for a seat change three times (seat A, seat B, and seat C). Now suppose the travel agency manager retrieves the customer’s reservation information after the customer reserves seat A. The manager uses that information to do other complimentary services for the customer. Now if the travel agent retrieves the data again, he ends up with a different seat, seat B. He then bases his data on this new information, thus rendering his previous read inconsistent or non-repeatable. This problem can be prevented if the manager reads the reservations information only after the customer confirms his reservation.

4. Phantom Reads: Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. For example, a travel agency manager requests a customer reservations report. He then uses that report to give out special offers and bonuses to frequent customers. Now suppose one customer cancels his reservation. The report used by the manager is no longer based on true information. This problem can be prevented if no reservations are cancelled while the manager is working on his report. An example could be preparing the report when the travel agent office is closed.

To prevent such concurrency problems from happening, concurrency control mechanisms must be used. There are two types on concurrency control mechanisms; Optimistic and Pessimistic.

1. Optimistic Concurrency: Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely (but not impossible), and allows transactions to execute without locking any resources. Only when attempting to change data are resources checked to determine if any conflicts have occurred. If a conflict occurs, the application must read the data and attempt the change again.

2. Pessimistic Concurrency: Pessimistic concurrency control locks resources as they are required, for the duration of a transaction. Unless deadlocks occur, a transaction is assured of successful completion. Pessimistic Concurrency control is the default for SQL Server.

Click here for further information about implementing locking in cursors.

Deadlocks

A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. For example, transaction1 holds an exclusive lock on the order details table. Transaction1 will not be committed unless it updates the product quantity in the products. Transaction2, on the other hand, holds an exclusive lock on the products table. Transaction2 will not be committed unless it updates the quantity information in the order details table. No transaction can move on thus resulting in a deadlock. Deadlocks can be prevented in transactions that access the same resources if they access them in the same order. If transaction1 and transaction2 accessed the order details and products table in the same order, each transaction would complete its task and let go of the resource thus allowing the other transaction to complete.