-- ****************************************************************************
-- * DEADLOCK situations:                                                     *
-- *                                                                          *
-- * FIRST CONNECTION    SECOND CONNECTION                                    *
-- * begin tran          begin tran                                           *
-- * delete from a       delete from b                                        *
-- * select * from b     select * from a                                      *
-- * - FIRST connection waits for resource b and holds lock on a              *
-- * - SECOND connection waits for resource a and holds b                     *
-- * WE HAVE A DEADLOCK SITUATION HERE                                        *
-- * After a certain amount of time one of the transaction is chosen to be    *
-- * the victim of the deadlock situation. This transaction is aborted and    *
-- * rolledback.                                                              *
-- ****************************************************************************
exec spDeleteObj 'a', 3, 0
create table a
(
	id int identity(1,1) not null,
	col1 char(10)
)
insert into a values ('1')
exec spDeleteObj 'b', 3, 0
create table b
(
	id int identity(1,1) not null,
	col1 char(10)
)
insert into b values ('1')

-- ****************************************************************************
-- * LOCKING WITH CURSORS:                                                    *
-- *                                                                          *
-- * The STANDARD CURSORS control concurrent access through several options.  *
-- * these controls are PESSIMISTIC and OPTMISTIC.                            *
-- * PESSIMISTIC concurrency:                                                 *
-- *   Two types of pessimistic concurrency control:                          *
-- *  - Readonly which does not permit data updates                           *
-- *  - Scroll locked in which rows are locked when they are fetched inside a *
-- *    user-initiated transaction and no other user can gain access to these *
-- *    rows until the locks are released.                                    *
-- * OPTIMISTIC concurrency:                                                  *
-- *  Does not place locks on the database, allowing multiple users to access *
-- *  the same rows, and must resolve any update conflicts that may occur as  *
-- *  a result of simultaneous updates.                                       *
-- *                                                                          *
-- * The UPDATEABLE CURSORS support data modification statements that update  *
-- * rows through position updates. When positioned on a row in an            *
-- * updateable cursor, update or deleteoperations that target the base table *
-- * rows used to build the current row in the cursor can be performed. The   *
-- * positioned updates are performed on the same connection that opened the  *
-- * cursor. This allows the data modification to share the same transaction  *
-- * space as the cursor, and prevents the updates from being blocked by      *
-- * locks held by the cursor. Positioned updates in a cursor can be          *
-- * performed through Transact-SQL by using the WHERE CURRENT OF clause on   *
-- * an UPDATE statement and can be placed within explicit transactions.      *
-- ****************************************************************************