Descriptions of these changes are durably recorded on disk in a type of transaction log file known as an undo-redo log. There is an optional second log, called the redo log; this can be used to enhance system reliability by recording the transaction log in two places.
Before Images and After Images
The undo-redo (before-image file) contains a log of all recent database changes. As transactions change the database during processing, the database manager writes one or more log records describing each change to the before-image file before making the change. It contains data that can restore the database to a consistent state after a failure by repeating (redoing) previously made changes.
The redo log (after-image log) keeps a log of all database changes that have occurred after a full backup of the entire database.
When recovering from failure you would restore the database from the backup and then reapply all changes made since the last backup, this process is called roll-forward. It allows the database manager to read records from the after-image log and repeat all changes in the order originally made.
Check Points
These contain the identifiers of all transactions that are active at the time of the checkpoint. Checkpoints are markers on the log written after periodic incremental backup is performed (Toby, 1999).
A checkpoint has two purposes; establish data consistency and secondly enable faster database recovery. As all database changes up to the checkpoint have been recorded in the transaction log, it makes it unnecessary to apply redo log entries prior to the checkpoint (Eaglestone, 1991). Frequent checkpoints reduce the time required to recover in the event of a system failure.
Example of Checkpoint
4.0 Locking
Locks are placed on data items to ensure that only one client at a time can access each data item. When the situation arises where a client requests for access to a data item which is in use by another client the former client must wait until the later client finishes the transaction process, as this is the stage at which the server unlocks the data (Eaglestone, 1991).
Enforcing locks allows the server to grant access to the data items according to the time sequence of their request. This is known as the resource lock, this prevents a user from reading and/or writing to a piece of data that is in use. An Example of this is shown in appendices 1. There are two types of resource locks:
-
Implicit versus Explicit – implicit locks are issued automatically by the DBMS based on an activity whereas explicit locks are issued by users requesting exclusive rights to the data (Toby, 1999).
-
Exclusive versus shared – Exclusive locks prevent other users from reading or updating the data. A shared lock allows others to read but not update the data (Eaglestone, 1991).
4.1 Types of Locks
There is another type of locking known as the two phase locking; it is widely used for concurrency control.
There are two distinct phases the first phase being the growing phase, whereby the transaction continues to request additional locks. The second phase is called the shrinking phase, whereby the transaction begins to release the locks. During these two phases all changes made by the transaction is invisible to other transactions.
Deadlocking
A deadlock can be a possible side effect from the locking scheme. It occurs when a transaction begins to lock resources that another transaction has not yet released. In this situation all transactions involved will wait indefinitely for a lock to be released, unless some outside agent takes action.
There are three strategies to avoid deadlocks: -
- Wait until all resources are available, then lock them all before beginning
- Establishing and using clear locking orders/ sequence
- Once the deadlock is detected the DBMS will rollback one transaction.
An example of a deadlock can be seen in the appendices in test 15.
Lock conversion can be used to change the state of a lock that is already in use to a more restrictive state. This has to be done through lock conversion as a transaction can hold only one lock on a data resource at a time.
The amount of space available for locks is limited by Maxlocks, as availability of space is not infinite. Lock escalation prevents specific database agents from exceeding the lock space limitations. This is performed automatically when there are too many locks that have been acquired.
Lock timeouts are used to control the amount of time any transaction will wait to obtain a requested lock. The time specified for the lock finishes then the waiting application receives an error and the transaction requesting the lock is rolled back.
5.0 Time stamping
Time stamping is a concurrency control technique; it is used to manage the state of data within a distributed database system. This is done by tagging the data in the database with timestamps as this indicates when the data was entered. This tagging process can be useful as it can permit a user to query the database over a historical period; it also allows the user to see a recent version of the database without having this transaction interfere with ongoing updates.
All updates made by a transaction to a database are stamped with the same time. This timestamp is stored is stored as an attribute of the data. The order of the timestamps must be in the correct serialization of the transactions. (Eaglestone, 1991).
A unique feature of this concurrency control technique is that it avoids the deadlock problem, because it makes a transaction wait to read or write when the transaction having an earlier timestamp is operating on that particular data item on which the former transaction waits.
5.1 Time stamping and Locking
Time stamping is a similar method to basic locking. Locking uses a lock to indicate which transaction is eligible to access the data, whereas timestamp uses the minimum timestamp to decide which transaction should gain access to data.
Competing requests for the same data that are not available result in one or the other of the competing transactions being aborted. This is usually considered to be less robust and less effective than locking (Oxborrow, 1989).
Having timestamps with data allows users to query a database using some particular time. This type of query when supported can provide a transaction with a consistent view of the database as it was originally at the requested time.
The basic time stamping mechanism can be used to see how timestamps can be propagated, it is known as the two phase commit protocol, it involves the following:
Firstly there is the prepare message of the protocol this is where a transaction notifies all parties (known as cohorts) that the transaction is terminated. The cohort then makes sure that the before state of the transaction and the after state are durably stored.
The cohort then votes to commit or to abort, the coordinator will then commit the transaction if all cohorts have voted to commit, if the coordinator times out waiting for the cohorts vote or any of the cohorts vote is aborted then the coordinator aborts the transaction. Cohorts then receive the transaction disposition message, which notifies whether the transaction has been aborted or committed and terminates the transaction.
6.0 Concurrency
In a multi-user database the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful consistent results. Therefore control of data concurrency and data consistency is vital in a multi-user database.
Concurrency control is the activity if coordinating the actions of processes that operate in parallel, access shared data and therefore potentially interferes with each other (Bowes, 1993). The main component of this model is the transaction. Without concurrency control one client request could be made at a time. If the server processes only one request at a time the process could not do anything while waiting for an event. Also clients would frequently time out due to the length of time waiting for the server to accept a new connection.
One of the most important concepts in modern systems is undoubtedly multi-programming. By having several transactions executing at the same time, the processor maybe shared among them (Eaglestone, 1991). This scheme improves the overall efficiency of the computer system by getting more work done in less time.
The idea of multi programming is very simple. A transaction is executed until it must wait, typically for the completion of some input or output request. In a programmed computer system the processor would just sit idle. All of this waiting time is wasted; no useful work is accomplished. With multiprogramming, we try to use this time productively. Several transactions are available for execution at one time. When a transaction has to wait, the system takes the processor away from that transaction and gives it to another transaction. The benefits of multiprogramming are increased processor utilisation and higher total transaction through input, that is, the amount of work that is accomplished in a given time interval.
6.1 Consistency
This allows each user to see a consistent view of the data including visible changes made by the users own transactions and transactions of other users.
Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency), an example of this can be seen in the appendices in tests 20 and 26. Oracle can also provide read consistency to all of the queries in a transaction (transaction-level read consistency). Oracle uses information maintained in its rollback segments to provide these consistent views (Toby, 1999). The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.
Transaction- level read consistency.
Oracle offers the option of enforcing transaction-level read consistency. When a transaction executes in serialisation mode, all data assesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serial able transaction do see changes made by the transaction itself. This read produces repeatable reads and does not expose a query to phantoms.
7.0 Indexes
Index provides direct access to individual records but also provides sequential access to sets of records. An objective of indexing is to hold as much of the index as possible in main memory to provide fast access to the index with only one disc access to obtain the required record (Oxborrow,1989).
There are various types of index; the basic types are partial indexes and full indexes, both of which may be single-level or multi-level.
Single-level Index
A partial index provides an index to individual pages (or buckets) in the file, each of which contains a group of records. A Particular record is found by first searching the index to locate the relevant page (or bucket) and then searching serially through the bucket for the actual record required. This is the basis of the indexed sequential access mechanism.
Multi-level Index
A full index provides an index entry for each record. Except for the smallest of files, a full index will be multi-level, re-presenting an inverted tree structure. The index maintained in sequential order also to addition to and deletions from the file are generally faster. An important point regarding this type of index is its ability to support records, which are variable in length.
B-tree is a type of multi-level index. They are largely used because they are self maintaining. They tend expand and contract automatically according to the number of data records in a file, this avoids deterioration in access speeds that occurs when a file is updated (Bowes, 1993). A B-tree keeps itself balanced by taking approximately the same time to access any data records. It does this by ensuring that each component index has no les than some fixed minimum and no more than a fixed maximum number of entries.
8.0 Conclusion
In a Multi-user environment there are various issues that arise which will affect the users of the database. Our findings have identified six issues that are interrelated with one another which can affect the users and the use of the database.
Concurrency control is necessary to preserve integrity of the database from threats posed by multiple users accessing, and potentially making conflicting changes to, the same unit of data at the same time. Locking is the method commonly used to protect against this problem.
Database backup and recovery procedures are designed to get the database back in operation without the loss or compromise of data in the face of any potential disaster. Three major elements are normally used to provide recovery services:
Backup copies are made on a regular basis and stored in a secure manner.
Transaction log is maintained. It contains before and after images of all units of data affected by transactions and other status information.
Checkpoints are generated by the system periodically and entered into the transactions log.
There are various mechanisms that can help protect and control data by resolving data ownership and user issues.
9.0 References
Books
BOWES, D. (1993) From Data to Databases. 2nd Ed., London: Chapman & Hall.
BRAITHWAITE, S. (1991) Relational Theory: Concepts and Applications. London: Academic Press Ltd.
CONNOLLY, T & BEGG, C. (1999) Database Systems: A Practical approach to design, implementation and management. 2nd Ed., Essex: Addison Wesley Longman Ltd.
DATE, J. (2001) The Database Relational Model: A retrospective review an analysis. USA: Addison Wesley Longman.
EAGLESTONE, B. (1991) Relational Database. Cheltenham: Stanley Thornes.
GRAY, J. & REUTERS, A. (1993) Transaction Processing: Concepts and techniques. USA: Morgan Kaufmann Publishers Inc.
OXBORROW, E. (1989) Databases and Database Systems: Concepts and Issues. 2nd ed., Sweden: Chartwell – Bratt Ltd.
RAMEZ, E & NAVATHE, S B. (1994) Fundamentals of database systems. 2nd Ed., Canada: The Benjamin/ Cummings Publishing Company Inc.
TOBY, T. (1999) Database Modelling and Design. 3rd ed., USA: Morgan Kaufmann Publishers Inc.
10.0 Bibliography
BOWES, D. (1993) From Data to Databases. 2nd Ed., London: Chapman & Hall.
BRAITHWAITE, S. (1991) Relational Theory: Concepts and Applications. London: Academic Press Ltd.
CONNOLLY, T & BEGG, C. (1999) Database Systems: A Practical approach to design, implementation and management. 2nd Ed., Essex: Addison Wesley Longman Ltd.
DATE, J. (2001) The Database Relational Model: A retrospective review an analysis. USA: Addison Wesley Longman.
EAGLESTONE, B. (1991) Relational Database. Cheltenham: Stanley Thornes.
GRAY, J. & REUTERS, A. (1993) Transaction Processing: Concepts and techniques. USA: Morgan Kaufmann Publishers Inc.
OXBORROW, E. (1989) Databases and Database Systems: Concepts and Issues. 2nd ed., Sweden: Chartwell – Bratt Ltd.
RAMEZ, E & NAVATHE, S B. (1994) Fundamentals of database systems. 2nd Ed., Canada: The Benjamin/ Cummings Publishing Company Inc.
TOBY, T. (1999) Database Modelling and Design. 3rd ed., USA: Morgan Kaufmann Publishers Inc.
Appendices
Grant Access to User 2 Page 19
User accessing granted tables Page 20
Test 1 Page 21
Test 2 Page 21
Teat 3 Page 22
Test 4 Page 22
Test 5 Page 23
Test 6 Page 23
Test 7 Page 24
Test 8 Page 24
Test 9 Page 25
Test 10 Page 26
Test 11 Page 26
Test 12 Page 27
Test 13 Page 27
Test 14 Page 28
Test 15 Page 28
Test 16 Page 28
Test 17 Page 29
Test 18 Page 30
Test 19 Page 30
Test 20 Page 31
Test 21 Page 32
Test 22 Page 32
Test 23 Page 33
Test 24 Page 33 - 34
Test 25 Page 35
Test 26 Page 36
Grant Access to User 2
SQL> GRANT ALL PRIVILEGES ON SCREEN TO OPS$B0161769;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON CINEMA TO OPS$B0161769;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON FILM TO OPS$B0161769;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON SHOWING TO OPS$B0161769;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON MANAGER TO OPS$B0161769;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON STAFF TO OPS$B0161769;
Grant succeeded.
User accessing granted tables
- SQL> SELECT * FROM OPS$B0157700.STAFF;
- SQL> SELECT * FROM OPS$B0157700.SCREEN;
- SQL>SELECT * FROM OP$B0157700.SHOWING;
- SQL>SELECT * FROM OP$B0157700. FILM;
- SQL>SELECT * FROM OP$B0157700. MANAGER;
- SQL>SELECT * FROM OP$B0157700. CINEMA;
WHEN USER 2 TYPED IN THE ABOVE STATEMENTS THE CONTENTS OF THE TABLE THEY REQUIRED WERE DISPLAYED.
Query 1 – INSERTING DATA CONCURRENTLY WITH OUT AUTO COMMIT ON
SQL> SPOOL TESTING
SQL> SET AUTOCOMMIT ON
Test 1
USER 1’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.FILM
2 VALUES(823, 'THE HERO', '25-APR-03', '178');
1 row created.
Commit complete.
USER 2’S RESULT
SQL> INSERT INTO FILM
2 VALUES(823, 'THE HERO', '25-APR-03', '178');
INSERT INTO FILM
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKFILM) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 2
USER 1’S INPUT & RESULT
SQL> INSERT INTO SHOWING
2 VALUES(111, 823, '12-OCT-2000', 298);
1 row created.
Commit complete.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.SHOWING
2 VALUES(111, 823, '12-OCT-2000', 298);
INSERT INTO OPS$B0157700.SHOWING
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKSHOWING) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 3
USER 1’S INPUT & RESULT
SQL> INSERT INTO SCREEN
2 VALUES(26, 110,300);
1 row created.
Commit complete.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.SCREEN
2 VALUES(26, 110,300);
INSERT INTO OPS$B0157700.SCREEN
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKSCREEN) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 4
USER 1’S INPUT & RESULT
SQL> INSERT INTO STAFF
2 VALUES(114579, 'MATTHEWS', '18-JAN-71', 'JR981258A', '58 HILLARY STREET', 'WALSALL', 140, '5.0
0', 'ADMINISTRATOR', 125);
1 row created.
Commit complete.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.STAFF
2 VALUES(114579, 'MATTHEWS', '18-JAN-71', 'JR981258A', '58 HILLARY STREET', 'WALSALL', 140, '5.00', 'ADMINISTRATOR', 125);
INSERT INTO OPS$B0157700.STAFF
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKEMP) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 5
USER 1’S INPUT & RESULT
SQL> INSERT INTO MANAGER
2 VALUES(15648, 'AJIMAL', '27-OCT-74', 'JR596421A', '52 BANTOCK AVE', 'WOLVERHAMPTON');
1 row created.
Commit complete.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.MANAGER
2 VALUES(15648, 'AJIMAL', '27-OCT-74', 'JR596421A', '52 BANTOCK AVE', 'WOLVERHAMPTON');
INSERT INTO OPS$B0157700.MANAGER
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKMANAGER) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 6
USER 1’S INPUT & RESULT
SQL> INSERT INTO CINEMA
2 VALUES(133, 'ROXY', 'WOLVPTON', 15648, '13-APR-2003',50);
1 row created.
Commit complete.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.CINEMA
2 VALUES(133, 'ROXY', 'WOLVPTON', 15648, '13-APR-2003',50);
INSERT INTO OPS$B0157700.CINEMA
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKCINE) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Query 2 – UPDATE SAME COLUMNS OF A TABLE AT THE SAME TIME
SQL> SET AUTOCOMMIT ON
Test 7
USER 1’S INPUT & RESULT
SQL> UPDATE FILM
2 SET LENGTH = '180'
3 WHERE FILM_NO = '823';
1 row updated.
Commit complete.
USER 2’S INPUT & RESULT
SQL> UPDATE OPS$B0157700.FILM
2 SET LENGTH = '180'
3 WHERE FILM_NO= '823';
1 row updated.
Commit complete.
(BOTH USER 1 AND USER 2 WERE ABLE TO UPDATE A COLUMN CONCURRENTLY)
Test 8
USER 1’S INPUT & RESULT
SQL> UPDATE STAFF
2 SET HOURLY_RATE = '5.25'
3 WHERE EMPNO = '114579';
1 row updated.
Commit complete.
USER 2’S INPUT & RESULT
SQL> UPDATE OPS$B0157700.STAFF
2 SET HOURLY_RATE = '5.25'
3 WHERE EMPNO = '114579';
1 row updated.
Commit complete.
(BOTH USER 1 AND USER 2 WERE ABLE TO UPDATE A COLUMN CONCURRENTLY)
Query 3 – QUERY THE SAME TABLE AT THE SAME TIME
SQL> SET AUTOCOMMIT ON
Test 9
USER 1’S INPUT & RESULT
SQL> SELECT CNAME, CINEMA_NO, CITY
2 FROM CINEMA
3 WHERE CNAME = 'ROXY';
CNAME CIN CITY
-------- --- ----------
ROXY 133 WOLVPTON
ROXY 104 WYLAM
ROXY 115 HACKTHOR
ROXY 120 RUGBY
ROXY 122 LINSTEAD
ROXY 125 SANDHEAD
6 rows selected.
USER 2’S INPUT & RESULT
SQL> SELECT CNAME,CINEMA_NO, CITY
2 FROM OPS$B0157700.CINEMA
3 WHERE CNAME = 'ROXY';
CNAME CIN CITY
-------- --- ----------
ROXY 133 WOLVPTON
ROXY 104 WYLAM
ROXY 115 HACKTHOR
ROXY 120 RUGBY
ROXY 122 LINSTEAD
ROXY 125 SANDHEAD
6 rows selected.
(BOTH USER 1 AND USER 2 WERE ABLE TO QUERY THE SAME TABLE CONCURRENTLY)
Test 10
USER 1’S INPUT & RESULT
SQL> SELECT MNAME, CNAME FROM MANAGER, CINEMA
2 WHERE MANAGER.MGRNO = CINEMA.MGRNO
3 AND MANAGER.MGRNO = '15787';
MNAME CNAME
---------- --------
WARD WARNER
USER 2’S INPUT & RESULT
SELECT MNAME, CNAME FROM OPS$B0157700.MANAGER, OPS$B0157700.CINEMA
WHERE OPS$B0157700.MANAGER.MGRNO = OPS$B0157700.CINEMA.MGRNO
AND OPS$B0157700.MANAGER.MGRNO = ‘15787’;
MNAME CNAME
---------- --------
WARD WARNER
(BOTH USER 1 AND USER 2 WERE ABLE TO QUERY THE SAME TABLE CONCURRENTLY)
Test 11
USER 1’S INPUT & RESULT
SQL> SELECT ENAME, ADDRESSL1, ADDRESSL2
2 FROM STAFF
3 WHERE EMPNO IN (SELECT SUPERNO FROM STAFF WHERE EMPNO = '89');
ENAME ADDRESSL1 ADDRESSL2
---------- ------------------------- -------------------------
WALL 45 KILWS RD BILSWORTH
USER 2’S INPUT & RESULT
SQL> SELECT ENAME, ADDRESSL1, ADDRESSL2
2 FROM OPS$B0157700.STAFF
3 WHERE EMPNO IN (SELECT SUPERNO FROM OPS$B0157700.STAFF WHERE EMPNO = '89');
ENAME ADDRESSL1 ADDRESSL2
---------- ------------------------- -------------------------
WALL 45 KILWS RD BILSWORTH
(BOTH USER 1 AND USER 2 WERE ABLE TO QUERY THE SAME TABLE CONCURRENTLY)
Query 1 – INSERTING DATA CONCURRENTLY WITH AUTO COMMIT 0FF
SQL> SPOOL TESTING1
SQL> SET AUTOCOMMIT OFF
Test 12
USER 1’S INPUT & RESULT
SQL> INSERT INTO FILM
2 VALUES(853, 'S CLUB', '15-APR-03', '92');
1 row created.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.FILM
2 VALUES(823, 'S CLUB', '15-APR-03', '92');
INSERT INTO OPS$B0157700.FILM
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$B0157700.PKFILM) violated
( USER 2 WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 13
USER 1’S INPUT & RESULT
SQL> INSERT INTO MANAGER
2 VALUES(10548, 'MATTHEWS', '13-OCT-73', 'JR186422A', '7 HOPE ST', 'WISBECH');
1 row created.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.MANAGER
VALUES(10548, ‘MATTHEWS’, ’13-OCT-73’, ‘JR186422A’, ’7 HOPE ST’, ‘WISBECH’);
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 14
USER 1’S INPUT & RESULT
SQL> INSERT INTO CINEMA
2 VALUES(134, 'ROXY', 'WOLVPTON', 10548, '13-APR-2003',50);
1 row created.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.CINEMA
VALUES(134, ‘ROXY’, ‘WOLVPTON’, 10548, ’13-APR-2003’,50);
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 15
USER 1’S INPUT & RESULT
SQL> INSERT INTO SHOWING
2 VALUES(134, 853, '25-APR-2003', 450);
1 row created.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.SHOWING
2 VALUES(134, 823, '25-APR-2003', 450);
INSERT INTO OPS$B0157700.SHOWING
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 16
USER 1’S INPUT & RESULT
SQL> INSERT INTO SCREEN
2 VALUES(50, 134,525);
1 row created.
USER 2’S INPUT & RESULT
SQL> INSERT INTO OPS$B0157700.SCREEN
VALUES(50, 134,525);
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Test 17
USER 1’S INPUT & RESULT
SQL> INSERT INTO STAFF
2 VALUES(104379, 'FRED', '19-JAN-71', 'JR971248A', '56 HILL ROAD', 'LICHFIELD', 197, '4.40', 'COUNTER', 134);
1 row created.
USER 2’S INPUT & RESULT
INSERT INTO OPS$B0157700.STAFF
VALUES(104379, ‘FRED’,’19-JAN-71’, ‘JR971248A’, ’56 HILL ROAD’, ‘LICHFIELD’, 197, ‘4.40’, ‘COUNTER’, 134);
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO INSERT THE DATA CONCURRENTLY TO USER 1)
Query 2 – UPDATE SAME COLUMNS OF A TABLE AT THE SAME TIME – AUTO COMMIT OFF
SQL> SET AUTO COMMIT OFF
Test 18
USER 1’S INPUT & RESULT
SQL> UPDATE FILM
2 SET LENGTH = '90'
3 WHERE FILM_NO = '784';
1 row updated.
USER 2’S INPUT & RESULT
SQL> UPDATE OPS$B0157700.FILM
SET LENGTH = ‘90’
WHERE FILM_NO= ‘784’;
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO UPDATE THE DATA CONCURRENTLY TO USER 1)
Test 19
USER 1’S INPUT & RESULT
SQL> UPDATE STAFF
2 SET CINEMA_NO = '117'
3 WHERE EMPNO = '189794';
1 row updated.
USER 2’S INPUT & RESULT
SQL> UPDATE OPS$B0157700.STAFF
SET CINEMA_NO = ‘117’
WHERE EMPNO = ‘189794’;
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO UPDATE THE DATA CONCURRENTLY TO USER 1)
Query 3 – QUERY THE SAME TABLE AT THE SAME TIME – AUTO COMMIT OFF
SQL> SET AUTO COMMIT OFF
Test 20
USER 1’S INPUT & RESULT
SQL> SELECT CNAME, CINEMA_NO, CITY
2 FROM CINEMA
3 WHERE CNAME = 'ROXY';
CNAME CIN CITY
-------- --- ----------
ROXY 134 WOLVPTON
ROXY 133 WOLVPTON
ROXY 104 WYLAM
ROXY 115 HACKTHOR
ROXY 120 RUGBY
ROXY 122 LINSTEAD
ROXY 125 SANDHEAD
7 rows selected.
USER 2’S INPUT & RESULT
SQL> SELECT CNAME,CINEMA_NO, CITY
2 FROM OPS$B0157700.CINEMA
3 WHERE CNAME = 'ROXY';
CNAME CIN CITY
-------- --- ----------
ROXY 133 WOLVPTON
ROXY 104 WYLAM
ROXY 115 HACKTHOR
ROXY 120 RUGBY
ROXY 122 LINSTEAD
ROXY 125 SANDHEAD
6 rows selected.
(USER 1 PREVIOUSLY DELETED A ROW FROM THIS TABLE, AS USER 1 HAS NOT YET TERMINATED THEIR SESSION THE CHAGES HAVE NOT YET BEEN UPDATED AND THEREFORE USER 2 IS RECEIVING INCORRECT DATA)
Test 21
USER 1’S INPUT & RESULT
SQL> SELECT MNAME, CNAME FROM MANAGER, CINEMA
2 WHERE MANAGER.MGRNO = CINEMA.MGRNO
3 AND MANAGER.MGRNO = '15787';
MNAME CNAME
---------- --------
WARD WARNER
USER 2’S INPUT & RESULT
SQL> SELECT MNAME, CNAME FROM OPS$B0157700.MANAGER,
OPS$B0157700.CINEMA
2 WHERE OPS$B0157700.MANAGER.MGRNO = OPS$B0157700.CINEMA.MGRNO
3 AND OPS$B0157700.MANAGER.MGRNO = '15787';
MNAME CNAME
---------- --------
WARD WARNER
(USER 1 AND USER 2 WERE ABLE TO RUN THEIR QUERIES SUCCESSFULLY AND CONCURRENTLY)
Test 22
USER 1’S INPUT & RESULT
SQL> SELECT ENAME, ADDRESSL1, ADDRESSL2
2 FROM STAFF
3 WHERE EMPNO IN (SELECT SUPERNO FROM STAFF WHERE EMPNO = '89');
ENAME ADDRESSL1 ADDRESSL2
---------- ------------------------- -------------------------
WALL 45 KILWS RD BILSWORTH
USER 2’S INPUT & RESULT
SQL> SELECT ENAME, ADDRESSL1, ADDRESSL2
2 FROM OPS$B0157700.STAFF
3 WHERE EMPNO IN (SELECT SUPERNO FROM OPS$B0157700.STAFF WHERE EMPNO = '89');
ENAME ADDRESSL1 ADDRESSL2
---------- ------------------------- -------------------------
WALL 45 KILWS RD BILSWORTH
(USER 1 AND USER 2 WERE ABLE TO RUN THEIR QUERIES SUCCESSFULLY AND CONCURRENTLY)
OTHER EXPERIMENTS – AUTO COMMIT OFF
SQL> SET AUTOCOMMIT OFF
Test 23
USER 1’S INPUT & RESULT
SQL> DELETE FROM SCREEN
2 WHERE SCREEN_NO = 16;
1 row deleted.
USER 2’S INPUT & RESULT
SQL> DELETE FROM OPS$B0157700.SCREEN
WHERE SCREEN_NO = 16;
BLANK SCREEN
( USER 2 SYSTEM CRASHED WAS UNABLE TO UPDATE THE DATA CONCURRENTLY TO USER 1)
Test 24
USER 1’S INPUT & RESULT
SQL> SELECT * FROM SCREEN;
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
50 134 525
26 110 300
18 101 225
9 102 200
1 103 250
12 104 225
11 105 200
2 106 230
19 107 235
5 108 200
20 109 300
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
13 110 250
8 112 275
3 113 325
24 114 350
30 115 450
23 116 400
14 117 390
21 118 400
27 120 500
22 121 475
4 122 350
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
17 123 350
6 124 375
10 125 400
25 128 375
15 130 400
29 126 375
31 127 400
29 rows selected.
USER 2’S INPUT & RESULT
SQL> SELECT * FROM OPS$B0157700.SCREE
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
26 110 300
18 101 225
9 102 200
1 103 250
12 104 225
11 105 200
2 106 230
19 107 235
5 108 200
20 109 300
13 110 250
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
16 111 300
8 112 275
3 113 325
24 114 350
30 115 450
23 116 400
14 117 390
21 118 400
27 120 500
22 121 475
4 122 350
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
17 123 350
6 124 375
10 125 400
25 128 375
15 130 400
29 126 375
31 127 400
29 rows selected.
(USER 1 PREVIOUSLY DELETED A ROW FROM THIS TABLE, AS USER 1 HAS NOT YET TERMINATED THEIR SESSION THE CHAGES HAVE NOT YET BEEN UPDATED AND THEREFORE USER 2 IS RECEIVING INCORRECT DATA)
OTHER EXPERIMENTS – AUTO COMMIT ON
SQL> SET AUTO COMMIT ON
Test 25
USER 1’S INPUT & RESULT
SQL> DELETE FROM SCREEN
2 WHERE SCREEN_NO = 23;
1 row deleted.
Commit complete.
USER 2’S INPUT & RESULT
SQL> DELETE FROM OPS$B0157700.SCREEN
2 WHERE SCREEN_NO = 23;
0 rows deleted.
Commit complete.
Test 26
USER 1’S INPUT & RESULT
SQL> SELECT * FROM SCREEN;
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
50 134 525
26 110 300
18 101 225
9 102 200
1 103 250
12 104 225
11 105 200
2 106 230
19 107 235
5 108 200
20 109 300
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
13 110 250
8 112 275
3 113 325
24 114 350
30 115 450
14 117 390
21 118 400
27 120 500
22 121 475
4 122 350
6 124 375
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
10 125 400
25 128 375
15 130 400
29 126 375
31 127 400
27 rows selected.
USER 2’S INPUT & RESULT
SQL> SELECT * FROM OPS$B0157700.SCREEN;
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
50 134 525
26 110 300
18 101 225
9 102 200
1 103 250
12 104 225
11 105 200
2 106 230
19 107 235
5 108 200
20 109 300
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
13 110 250
8 112 275
3 113 325
24 114 350
30 115 450
14 117 390
21 118 400
27 120 500
22 121 475
4 122 350
6 124 375
SCREEN_NO CIN NO_OF_SEATS
---------- --- -----------
10 125 400
25 128 375
15 130 400
29 126 375
31 127 400
27 rows selected.
