Multiuser databases (Chapter 9 - Kroenke )

1. Multiuser databases (Chapter 9 - Kroenke)

2. DBA responsibilities (Kroenke) • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Provide for database recovery • Managing the DBMS • Maintaining the data repository.

3. Problem: • Make two copies of the C# university application • Run each on different machines. • In both, locate the same student record to be deleted. • Set up breakpoints in the delete button’s click method and step to the stored procedure call in each application. • Delete the record in one app. • Try to delete the record in the other. You will get a message similar to the following, which contradicts what is on the screen.

5. Another type of problem: • Locate the same student record in both applications and set breakpoints at the ExecuteNonQuerycall in the student update form’s save button click method. • Change the credits to 88 in one and update. • Change the credits to 77 in the other. • Update one immediately followed by the other.

6. Both windows show what the user entered. • You’ll see (by going back to the main form and returning to the student form) the 1st update did not stick. • Or you can click the Update Student button to see seemingly conflicting information • The result is what is called a lost update.

7. Some general classifications of concurrency problems:

8. Lost Update Problem: • Figure 9-5 on page 327 of text. • B’s update of the item is based on incorrect info (never saw A’s change) • A’s update lost

9. Uncommitted dependency problem: • B gets and updates P • A gets P (or updates P) • B encounters a problem and restores the original value it had for P • In database lingo, this is called a rollback • A’s future action depends on uncommitted change; or A’s update negated due to rollback

10. Inconsistent Analysis Problem: • Three accounts: A, B, and C: A has $1000, B has $2000; C has $3000

11. time Final sum is incorrect.

12. Transaction • Logical Unit of Work or a series of actions that must ALL be done or NONE at all. • Sometimes called atomic. • A transaction is usually delimited by a begin and either a commit or rollback.

13. Commit • Write all changes to the database. • Transaction is done.

14. Rollback • Restore the database to its status prior to the beginning of the transaction. • As if the transaction never started.

15. Example • Delete a record that is referenced from other locations. • Must delete record and all references or do not delete anything.

16. Example • Create an order for a customer and update the customer record reflecting the cost of the order. • If, for some reason, the application had no access to the customer record, it would need to do a rollback and remove the order.

17. Solutions • Prevent one app from accessing a record if another is about to change it. • That is - put a lock on it. • When accessing a record that is locked, wait for the resource to be released. • The app goes into a wait state until the lock is released, at which point the application resumes at the point at which it went to sleep.

18. Implicit lock • Lock placed automatically by the DBMS. Most common. • Explicit lock • Lock placed using a specified command.

19. Lost Update Problem with locks • Figure 9-6 on page 328 of text. • Assumes explicit locks.

20. Lock granularity: • Locks at the database level (large granularity), table level, disk block level, record level, or even field level (small granularity). • Large granularity can cause more conflicts but easier to administer. • Small granularity is the reverse.

21. Exclusive lock • Lock item from any type of access. • Any application wanting that item must wait until the lock is released.

22. Shared lock • Allow read access but not write access until the lock is released.

23. Serializable Transactions: • Processing concurrent transactions to produce logically consistent results.

24. Example time Locks were used, but incorrectly:

25. Two-phased locking: • Locking (or growing) phase • acquire locks, release none • Release (or shrinking) phase • release locks, acquire none, typically part of rollback or commit. • The example in the previous slide did not implement two phased locking. • A was allowed to lock x, then release x, then lock y.

26. time

27. Deadlock (Deadly embrace) • Two activities each waiting for the other to release something. • Figure 9-7 on page 329 • Can involve more than two activities if there is a circular chain of activities, each waiting on the next.

28. Deadlocks have been studied extensively. • Impossible to prevent without significant side effects such as forcing all activities to requests ALL locks at the same time or forcing all activities to lock resources in the same order. • Algorithms exist to build data structures representing activities and resources and to look for cycles in those structures.

29. Concurrency control in ADO.NET: [http://msdn2.microsoft.com/en-us/library/22hhd212(vs.71).aspx]

30. Transactions in ADO.NET and SQL Server • Somewhat awkward since .NET is a disconnected architecture but here are some things you can do in the student update form (though, there are other ways):

31. Add SqlTransactionmyTrans; as a class variable in the studentUpdate form. • Add myTrans = conStudent.BeginTransaction(); at the beginning of the save button’s click method. • Add myTrans as a 3rd parameter to the line creating the SqlCommand object, dbCommand. • Add myTrans.Commit(); to the end of the button save click method but prior to conStudent.Close().

32. Step through the two apps and attempt to update the same record. • When the first hits ExecuteNonQuerya lock is set. • When the other hits ExecuteNonQuery, it waits until the first executes commit. • If the first is delayed for too long, the other times out.

33. Note that both apps are still allowed to see the same record and one of them loses its update. • Another option is:

34. Keep SqlTransactionmyTrans; as a class variable in the student update form. • Move myTrans = conStudent.BeginTransaction(); to the form load method after opening the connection. • Add myTransas a 3rd parameter to the line creating the SqlCommandobject, dbCommandin each of the form load and save methods. • Keep myTrans.Commit(); at the end of the button save click method prior to closing the connection.

35. Step through as before • By itself this does not really change anything • In the form’s load method the call to executenonquery is not blocked. • The findStudentstored procedure referenced only retrieves data. Thus, no locks are set.

36. There is a select for update command but SQL Server does not support it. • It DOES however, allow you to specify With (XLOCK) as part of the SQL command. • Can put this clause after the From clause of the last select command in the findStudent stored procedure. • Can also specify With (XLOCK, ROWLOCK) to lock one row only.

37. NOTE: • This example is for illustrative purposes only. • There is a serious side effect to locking records when they are retrieved for viewing as it could cause long waits for other applications.

38. Another NOTE: • The previous example works well as long as triggers are disabled. If a trigger is enabled for updates, the locks behave in unexpected ways because the trigger can update other records and initiate additional locks, sometimes causing deadlock. • Example: If I try to update two different student records, the results can be hard to predict. • In management studio, I can right click on the Student table trigger and select disable and the problem is resolved.

39. Processing transactions [http://msdn2.microsoft.com/en-us/library/w97s6fw4(vs.71).aspx]

40. Types of concurrency control [http://msdn.microsoft.com/en-us/library/cs6hb8k4(VS.71).aspx]

41. Pessimistic locking: • Locks are issued, the transaction is processed, and locks are released during the commit or rollback (end of transaction). • Assume conflicts are frequent and forces synchronization. • Locks typically held for longer periods of time.

42. Optimistic locking: • transactions proceed unsynchronized and conflicts checked ONLY when commit occurs. • If conflict occurs, transaction is redone. Assumes conflicts are rare. • Advantage: lock held only during final phase (commit) and locks are held less time. Disadvantage: could force activities to be repeated many times. • See Kroenke page 330. • See Kroenke page 445.

43. ACID properties of a transaction: Atomic, consistent, isolated, and durable. [http://msdn.microsoft.com/en-us/library/aa719484(VS.71).aspx]

44. Atomic: all or none: • If a record is deleted, all references to it must also be deleted. • If changes are needed for all customers in a zip code, than all or none of the customers must be updated.

45. Consistent: • Constraints that exist prior to a transaction must exist after it. • If a student record contains a GPA reflecting course grades, then it must reflect new courses taken that are added to the database.

46. Statement level consistency: • All rows affected by an SQL statement are protected against other changes. Example on page 332-333 • Transaction level consistency: • All rows affected by all SQL statements in a transaction are protected. Example on page 332-333.

47. Isolated: • Isolated has different meanings dependent on context. First some definitions: • Dirty read: • A transaction reads data that has been updated but not yet committed by another transaction. The data could be rolled back and the data that was read would be incorrect.

48. Nonrepeatable read: • a transaction rereads data it previously read and finds it has changed due to a commit by another transaction. • Phantom reads: • Rereads data and finds new records (inserted by a committed transaction) that were not present previously.

49. Isolation levels define which of these can occur (1992 SQL Standard) • See Figure 9-11 on page 333 • User specifies isolation level and the DBMS creates and manages locks appropriately.

50. The connection object’s BeginTransactionmethod allows the specification of an isolation level as a parameter. • Put the cursor on BeginTransactionand press F1 for help to see options. • Can also put myTransin a debug watch window and view its IsolationLevelvalue. • [http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx]