In this chapter, we will cover cursors, transactions, and concurrency:
Cursors
These are mechanisms to code non-set-based operations into T-SQL statements. In general, their use is considered bad practice, but can be useful when building stored procedures if used properly.
Transactions
Transactions are used to group together similar operations into one atomic logical operation. Transactions are important, as it can be dangerous to write stored procedures that modify data, without using transactions. Transactions allow us to construct stored procedures in the ‘either all or nothing’ manner, where if one part fails, any changes that have already been applied will be undone.
Concurrency
Concurrency is related to multi tasking of operations. In databases, we are concerned with the number of queries that can be executed at the same time, especially making use of the same resources and data. Under concurrency, we will look at locks, which are the markers used by SQL Server to decide whether the commands can be executed concurrently or not. We will also discuss methods to maximize concurrency by using some programming schemes to limit SQL locking between read-only and read-write sessions.
Initially, these topics might seem unrelated; however, upon deeper discussion, we will discover that they are related. Now, let's examine each in detail.