Chapter 10

1. Chapter 10 Protecting Data Integrity in a Multiuser Environment

2. Chapter Objectives • Develop code that implements database transactions • Develop code that commits and rolls back transactions • Identify the types of problems that occur when multiple users access a database concurrently

3. Chapter Objectives • Implement database, recordset, page-level, and record-level locking strategies • Develop error-handling procedures that support concurrent use of a database • Replicate and synchronize replicas and partial replicas of a database

4. Using Transactions to Protect Against System Failure • Transaction • Logical unit of work • Each unit of work comprises one or more physical database operations • When a transaction completes successfully, changes that the transaction made to the database arecommittedto the database

5. Using Transactions to Protect Against System Failure • Whenever a transaction aborts, changes that it made to the database before aborting are not committed to the database • Changes are rolled back • Database fields that contain changes return to their original state

6. Transaction Processing in VBA • Three methods of the Connection object to define the beginning or end of a transaction in VBA: • BeginTrans • Denotes the beginning of the transaction • CommitTrans • Denotes the end of a successful transaction • Rollback Trans • Denotes the end of an unsuccessful transaction

7. Additional Transaction Features and Caveats • UseTransaction property • When set to Yes, a transaction is automatically defined for the query • Therefore, BeginTrans and CommitTrans methods are unnecessary • FailOnError property • When set to Yes, the query results are automatically rolled back when the first error occurs

8. Additional Transaction Features and Caveats Figure 10-1 Setting the Use Transaction and Fail On Error properties in query Design view

9. Additional Transaction Features and Caveats • Access supports a maximum of five levels of nested transactions • When one transaction is nested inside another, commitments of transactions are temporary until the final CommitTrans method executes • If that method does not execute or the corresponding RollbackTrans method executes, all nested transactions are rolled back

10. Additional Transaction Features and Caveats • Connection object • Controls a transaction • Recordsets that use a particular connection are rolled back when the RollbackTrans method is applied to the Connection object • When data are updated through bound forms, Access developers have little control over the definition of a transaction • Access controls the updates

11. Solving Concurrent Processing Problems • Concurrent transactions • Transactions that overlap • Inconsistent analysis • Type of concurrency issue • Locking mechanisms • Prevent more than one transaction at a time from modifying a data object

12. Solving Concurrent Processing Problems • Granularity • Level of locking • Write lock • Allows other users to read but not update the locked records • Prevents the lost update problem, but not prevent the inconsistent analysis problem

13. Solving Concurrent Processing Problems • Read lock • Prevents other users from reading or writing locked records • Prevents both the inconsistent analysis problem and the lost update problem • Pessimistic locking • Locks a page or record as soon as the first field in the record is changed and releases a lock after the record is written

14. Solving Concurrent Processing Problems • Optimistic locking • Locks a page or record when the Update method is encountered in VBA and releases the lock after record is written • Isolation level • Indicates the degree to which the transaction should be independent of other transactions • Dirty read • Prevented by specifying that transaction should not read changes made by another transaction until the other transaction is complete

15. Locking Records at the Database Level • You can lock the entire database through the Open window Figure 10-2 Opening a database in Exclusive made

16. Locking Records at the Database Level • You can lock databases through startup command lines and VBA code Figure 10-3 Record locking defaults within Advanced tab of Options dialog box

17. Locking Records at the Database Level Figure 10-4 Opening a database in Exclusive mode through a shortcut

18. Locking Records at the Database Level • Mode property of the Connection object • Used to specify whether other users can access the database • When set to the intrinsic constant adModeShareExclusive, other users cannot open a connection to the database

19. Locking Records at the Database Level Table 10-1 Legal values for the Mode property

20. Setting the Locking Characteristics of Bound Forms • RecordsLocks property of a form and Advanced tab of the Options dialog box are used to set locking characteristics of bound forms Table 10-2 Setting the locking characteristics of bound forms

21. Setting the Locking Characteristics of Bound Forms • Recordset-level locks • Specified by setting the RecordLocks property to All Records in the form property sheet or to 1 in VBA • Edited Record option • Locks a record (or page) as soon as a user begins to change the value of a field • No Locks option • Does not lock the record until all of the changes have been made

22. Record-Level Locking Techniques in VBA • Locktype property • Used to adjust locking strategy of recordsets other than those associated with bound forms • adLockPessimistic • Employs pessimistic locking • adLockOptimistic • Employs optimistic locking

23. The CursorType Property • CursorType property of a recordset object • Indicates how to respond when others make changes • Values of CursorType relevant for Jet Engine • adOpenStatic indicates that once recordset is created, it should not reflect changes, new records, or deletions made by other users • adOpenKeyset indicates that the transaction cannot view the results of another transaction until the other transaction is committed

24. The IsolationLevel Property • IsolationLevel property • Used to control how Access behaves in a multiple-user environment • Indicates how you should respond when others make changes • Property of a Connection object

25. The IsolationLevel Property • Specifies when recordsets can view changes by users • If it is set to adXactReadUncommitted: • Recordset can view changes by others before they are committed in a transaction • A dirty read is possible • adXactReadCommitted • Indicates that the transaction cannot view the results of another transaction until the other transaction is committed

26. The IsolationLevel Property Table 10-3 IsolationLevel values and descriptions

27. Problems with Locking • Deadlock • Situation where each transaction is waiting for the other to release a locked resource and neither transaction can finish • Update Retry Interval • The time, in milliseconds, that Access waits before trying to obtain the record again • Number of Update Retries • Number of times Access will attempt to obtain a record

28. Handling Concurrency Errors • If an error handler detects a locked database, recordset, or record, the error-handling routine typically waits and then repeats the statement through the use of theResumestatement • SQLState property of an Error object • Returns a value that identifies the error according to the ANSI SQL standard

29. Handling Concurrency Errors Table 10-4 Commonly used values of SQL State

30. Handling Concurrency Errors • Number property of an Error object • Returns a long integer that is determined by the database provider and the connection • Status property of a recordset object • May be consulted to gather information about the success or failure of an operation

31. Handling Concurrency Errors Table 10-5 Values of the recordset Status property

32. Handling Concurrency Errors Table 10-5 Values of the recordset Status property (continued)

33. Database Replication • Database replication • Creates copies of all or part of a database • Each copy cab reside on a different computer • Synchronization • A process used to periodically distribute updates from one copy to other copies of the database

34. An Overview of Replication and Synchronization Steps • Design Master • Copy of the database that can propagate structural changes • Two issues must be resolved during the process of creating the Design Master: • The design of the database should be analyzed to determine whether it adequately supports replication • You must determine which objects will be replicated

35. An Overview of Replication and Synchronization Steps • Design Master replica set • Consists of databases that share the same Design Master database • Types of replicas: • Complete replica • Contains all of the data from replicable tables • Partial replica • Contains only the rows that meet a particular criterion

36. An Overview of Replication and Synchronization Steps • Types of views replicas can have: • Global replicas • Can be used to create other replicas and can be synchronized with any replica • Can create and then serve as a synchronization hub for local replicas • Local replicas • Can create only other local replicas and must be synchronized with the global replica that was used to create it

37. An Overview of Replication and Synchronization Steps • Replicas created from a particular global replica, directly or transitively through a local replica, define a replica set Figure 10-6 Replica sets

38. An Overview of Replication and Synchronization Steps • During synchronization, aconflict occurs when: • The same record and column in two different replicas have been updated • Two records share the same primary key and when one replica deletes a record that has a referential integrity relationship with a record in another replica

39. Replication Within VBA • Microsoft Jet and Replication Object Library (JRO) • Contains object that support methods that perform activities similar to the activities carried out when you replicate and synchronize through the menus • Must be checked in the References window before you can use the replication objects

40. Replication Within VBA Table 10-6 Replication objects and methods

41. Replication Within VBA • You can create partial replicas by using theCreateReplica, Append, and PopulatePartialmethods • Partial replicas are not populated through the CreateReplica method • PopulatePartial method adds data to the replica

42. Chapter Summary • Features such as transactions, locking, and replication help Access developers prepare an application to function in complex environments • Notion of a transaction • Controls how concurrent access occurs • Transaction • Logical grouping of database operations that define a unit of work

43. Chapter Summary • One way that transactions control database processing is by defining when changes affected by the transaction may actually be applied to the database • CommitTrans and RollbackTrans connection methods • Handle problems with committing a transaction’s effects to a database

44. Chapter Summary • In Access, to control problems caused by the actions of multiple users, transactions use a locking scheme • Pessimistic locking • Locks a record as soon as it is edited • Optimistic locking • Locks a record when it is about to be updated

45. Chapter Summary • Replication • Used when more than one site needs access to an application, but network access is not available or desired • Replica • Copy of an application • Partial replica • Filters a table, so that the records in the replica are a subset of the original table’s records

46. Chapter Summary • Synchronization of replicas • Process where updates made in one replica are propagated to other replicas • Replication difficulties occur when two replicas modify the same column and record • A conflict resolution function is used to determine whether the correct value was placed in the database