Concurrency, Cursors, and Transactions
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 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 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.
KeywordsSelect Statement Repeatable Read Line Item Temporary Table Isolation Level
Unable to display preview. Download preview PDF.