AUDITING OPERATING SYSTEMS & NETWORKS
ACTIVITY No. 6
CATIBOG, MARYNISSA M.
BSA 4203
I. Database
Deadlock how is a lockout different from a deadlock? Give an accounting example to illustrate why a
database lockout is necessary and how a deadlock can occur. Use actual table names in your example.
A lock is acquired when a process accesses a data item, and another concurrent process may also
need that data item. Locking data allows it to behave as expected based on that data. For example,
when reading data, you typically want to make sure you're reading the most recent data. When
updating data, you must ensure that other processes do not update the data at the same time. Locks
are a mechanism used by SQL Server to protect data integrity during transactions. Deadlock occurs
when a process is blocked waiting for a second process to complete its work and release a lock, and at
the same time a second process is blocked waiting for the first process to release a lock. It happens. In
a deadlock situation, the processes are already blocking each other, so external intervention is required
to resolve the deadlock. For this reason, SQL Server has a deadlock detection and resolution
mechanism in which one process is selected as the "deadlock victim" and must be terminated to allow
the other process to continue operating. The victim process receives a very specific error message
indicating that it has been selected as the deadlock victim, and you can restart the process through
code based on this error message. Deadlock is considered a serious situation in the database world
because the process simply terminates automatically. Deadlocks can and should be prevented.
Deadlocks are resolved by SQL Server and no manual intervention is required.
Lockout. The example below illustrates how two different transactions are being processed against the
same AR control account within the same time frame. The individual logic steps are shown in their
actual sequence of execution. If no database lockout were in place, the AR control value of $20,000 is
retrieved by both users of the system. One user is posting a payment to sub account 1 of $500 and the
other is posting a payment to sub account 2 for $800.
AR
Control
Time Site Instruction Sub Account 1 Sub Account 2 Account
[Link] A Read sub acct 1 1,000
[Link] B Read sub acct 2 3,000
[Link] A Update sub acct 1 500
[Link] B Update sub acct 2 2,200
[Link] A Read control acct 20,000
[Link] B Read control acct 20,000
[Link] A Update control acct 19,500
[Link] B Update control acct 19,200
The update process does not reflect the second to last instruction executed. The AR control account
should reflect payments received of $1,300, and have a value of $18,700, but only $800 of payments
are accounted for. Thus, a transaction is lost, and the control and subsidiary ledgers are out of balance.
Deadlock.
A deadlock occurs when multiple sites lock out each other. Take for example a mail order company in
which two customers are processing transactions simultaneously. Customer 1 wants to order 2 items—
Item A and Item B. Customer 2 is purchasing Item B and Item A.
Customer 1 informs the phone clerk that he or she wants Item A and the record for Item A is locked
until the order is complete. Meanwhile, Customer 2 orders Item B from another phone clerk who locks
it. Customer 1 then requests Item B, which is locked by Customer 2’s order. The phone clerk
apologizes for the delay and says the system is slow today. Customer 2 then requests Item A, which is
locked by Customer 1’s order. The phone clerk who is helping Customer 2 apologizes for the delay and
AUDITING OPERATING SYSTEMS & NETWORKS
ACTIVITY No. 6
says the system is slow today. Unfortunately, neither transaction can be completed, resulting in
deadlock. This condition will not be resolved unless some type of intervention occurs.
II. Distributed Databases
The ABC Company is a geographically distributed organization with several sites around the country.
Users at these sites need rapid access to data for transaction processing purposes. The sites are
autonomous; they do not share the same customers, products, or suppliers.
Required: Which distributed database method is best under these circumstances? Explain your
reasoning.
Response:
The best distributed approach is a partitioned database.
Reasoning:
• Users are scattered across the country and need quick access to data. A centralized model can
introduce long delays due to network traffic and database locks.
• The need for user data is specific to an identifiable key user. There is no need to replicate the entire
database.
• Users are unique, so changes to the database do not cause database concurrency issues.
III. Multiple Choice Questions
1. The database approach has several unique characteristics not found in traditional (flat-file) systems,
specifically file-oriented systems. Which one of the following statements does not apply to the
database model?
A. Database systems have data independence; that is, the data and the programs are maintained
separately, except during processing.
B. Database systems contain a data definition language that helps describe each schema and
subschema.
C. The database administrator is the part of the software package that instructs the
operating aspects of the program when data are retrieved.
D. A primary goal of database systems is to minimize data redundancy.
E. Database systems increase user interface with the system through increased accessibility and
flexibility.
2. One of the first steps in the creation of a relational database is to
A. integrate accounting and nonfinancial data.
B. plan for increased secondary storage capacity.
C. order data-mining software that will facilitate data retrieval.
D. create a data model of the key entities in the system.
E. construct the physical user view using SQL.
3. Which of the following is a characteristic of a relational database system?
A. All data within the system are shared by all users to facilitate integration.
B. Database processing follows explicit links that are contained within the records.
C. User views limit access to the database.
D. Transaction processing and data warehousing systems share a common database.
4. Partitioned databases are most effective when
A. users in the system need to share common data.
B. primary users of the data are clearly identifiable.
C. read-only access is needed at each site.
D. all of the above.
AUDITING OPERATING SYSTEMS & NETWORKS
ACTIVITY No. 6
5. The functions of a database administrator are
A. database planning, data input preparation, and database design.
B. data input preparation, database design, and database operation.
C. database design, database operation, and equipment operations.
D. database design, database implementation, and database planning.
E. database operations, database maintenance, and data input preparation.