Chapter 9

1. Database Processing Chapter 9 Managing Multi-user Databases

2. Multi-User Issues • Concurrency Control • Database Reliability • Database Security • Database Administration

3. Multi-User Databases • Database usage • Must plan for managing transactions

4. Chapter 9 Transactions Trans 6 Trans 4 Trans 3 Trans 5 Trans 7 Trans 1 Trans 2 (TIME)

5. Finite State Machine: exam question review process Version 2 Final Edit Question Question Submitted by author Edit Question Generate Review Send back to Author Assign Course/LU Re-assign LU to Skill Author Edit Review Final Edit Get Reviews Send to Beta Question has Problem Release for review Send to Final Edit Send to Exit Reject Question Reject Question Send to Review Beta Exam Generate Beta Stats Beta Edit Question Reject Put on Beta Exam Put on Exit Exam Problem Author Review Exit Exam Send to Review Generate Exam Stats Reject Question Send to Beta Send to Exit Retire Question Retired Transactions occur concurrently.

6. Lock Granularity

7. Remedy for inconsistencies caused by concurrent processing • Resource Locking “disallow sharing by locking data that are retrieved for update”

8. Lock Terminology • Implicit locksplaced by the DBMS • Explicit locksplaced by command • Lock granularitythe size of the lock • Exclusive lock from access of any type • Shared lock from change but not read

9. Lock Granularity • Lock granularitythe size of the lock • Can lock at the row-level, key (index), page, Table, or Database level • There is a trade-off between cost of system overhead and concurrency • Concurrency cost: restricted access to object; some transactions may have to wait.

10. Lock Mode • Share lock (read only) • Shared locks allows concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the data has been read. • Exclusive lock • Denies access of any kind to concurrent transactions

11. Serializable Transactions “a scheme for processing concurrent transactions” • Two-phased locking • Growing and Shrinking phase • Once a lock is released, transaction can not place any more locks.

12. Chapter 10 Serializable Even though the transactions are physically executed concurrently in time… Trans 6 Trans 4 Trans 3 Trans 5 Trans 7 Trans 1 Trans 2 (TIME)

13. Chapter 10 Serializable The logical result is the same as if they where executed in sequence, one after the other. • We affect this through adjusting the transaction isolation level and resource locking. Trans 1 Trans 2 Trans 3 Trans 4 Trans 5 Trans 6 Trans 7 (TIME)

14. Serializable • To avoid a lost update, transactions must be isolated at the Repeatable read level or higher. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION UPDATE QUERYSUBMISSION SET SQLSUBMISSION = ‘/*Basic structure of CREATE…’ WHERE QUERYID = 84 COMMIT TRANSACTION

15. Transaction • Concept of a Transaction • A series of Reads and Writes to the database • 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: • Part of a broader area: Transaction Management Source: Microsoft documentation

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

21. ACID • Transaction boundaries • Controlled at the connection level • BEGIN & COMMIT BEGIN TRANSACTION Marks the starting point of an explicit, local transaction. Syntax BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [WITH MARK [ 'description' ] ] ]

22. ACID • COMMIT TRAN • A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. • A COMMIT also frees resources, such as locks, used by the transaction.

23. ACID • BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. • If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.

24. ACID • ROLLBACK TRAN • Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside a transaction. • It also frees resources held by the transaction. • A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures or triggers, use the RAISERROR or PRINT statements. RAISERROR is the preferred statement for indicating errors. • ROLLBACK is expensive…

25. ACID • ROLLBACK TRAN • Rollback and rollforward are possible because the dbms maintains transaction logs. • You can view SQL Server transaction logs with the undocumented Database Console command: • DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}]) • DBCC log (NorthWind, type=2)

26. ACID • savepoint • A savepoint allows you to rollback a portion of a transaction rather than the entire transaction • Savepoints allow you to commit part of a transaction while rolling back the remainder. • A bit risky though. There is still the chance that an error affects a change made prior to the savepoint.

27. ACID • Consistency • When completed, a transaction 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.

28. ACID • Consistency is maintained by rolling back the transaction to the point where it started (or locking resources).

29. ACID • Consistency • Another issue is whether the transaction will be able to see it’s own changes • It is possible to write statements that cause rows to be excluded by a subsequent SELECT statement. • @@Identity provides access to identity value created by INSERT statement. • However, it is not always that simple…

30. ACID (An excerpt from dbo.InsertSkillQuestion ALTER PROC dbo.InsertSkillQuestion : : BEGIN TRAN BEGIN : : INSERT INTO tblSkillQuestion (LuSkillID, attribute list…) VALUES (@LuSkillID, @Question, variable list…) --We have to use SCOPE_IDENTITY() because @@Identity returns the --last identity value. The last identity value is --the row inserted into the log file generated by trigInsertSkillQuestionLog DECLARE @LastIdentity AS int SET @LastIdentity = SCOPE_IDENTITY() --Insert row in status log file EXEC InsertlogSkillQuestionStatus @LastIdentity, variable list… END IF @@ERROR <> 0 COMMIT TRAN ELSE ROLLBACK TRAN END Creates an identity value that is stored in @@Identity. The value of @@Identity is not visible to the EXEC InsertLogSkillQuestionStatus.

31. ACID • 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, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed. • We’ll look at example problems shortly

32. ACID • 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.

33. Chapter 7 Time Travel... “Can a cube that does not last for any time at all, have a real existence?” H.G. Wells: The Time Machine (1895)

34. Deadlock Locking solves one problem, but introduces another… “deadly embrace”; each transaction waiting for a resource that the other person has locked

35. Page 314