Database Management System Interview Questions and Answers For Freshers and Experience Part-5
41. Explain the difference between an exclusive lock and a shared lock.
An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other users to read the locked resource, but they cannot update it.
42. Explain the difference between optimistic locking and pessimistic locking.
Optimistic locking assumes no transaction conflict will occur and deals with the consequences if it does. Pessimistic locking assumes that conflict will occur and so prevents it ahead of time with locks. In general, optimistic locking is preferred for the Internet and for many intranet applications.
43. What is deadlock? How can it be avoided? How can it be resolved once it occurs?
Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds. Deadlock can be prevented by requiring transactions to acquire all locks at the same time; once it occurs, the only way to cure it is to abort one of the transactions and back out of partially completed work.
44. What are the major functions of the database administrator?
Managing database structure, controlling concurrent processing, managing processing rights and responsibilities, developing database security, providing for database recovery, managing the DBMS and maintaining the data repository.
45. Explain what we mean by an ACID transaction.
An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent. Consistency can mean either statement level or transaction level consistency. With transaction level consistency, a transaction may not see its own changes. There are four transaction isolation levels: read committed, read uncommitted, repeatable read and serialization. Atomic means it is performed as a unit.
46. What are the ways in which an Oracle database can be created?
There are three (3) ways to create an Oracle database. You can create a database using the Database Configuration Assistant, using the Oracle-supplied database creation procedures or using the SQL CREATE DATABASE command.
47. What are sequences, and what are the possible problems when using them to create surrogate keys?
A sequence is an object that generates a sequential series of unique numbers. Sequences are most often used to provide values for surrogate keys. However, there are three problems with using sequences. First, a developer can use a defined sequence for any purpose; and if a sequence is used for purposes other than the surrogate key, some values will be missing. A second problem is that there is nothing in the schema that prevents someone from issuing an INSERT statement that adds data to the table but that does not use the sequence. Finally, it is possible to use the wrong sequence when putting data into a table.
48. Under what conditions should indexes be used?
Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values. A good candidate for an index is a column that is frequently used with equal conditions in WHERE clauses.
49. Explain the three levels of transaction isolation supported by Oracle.
Oracle supports read committed, serializable, and read-only transaction isolation levels. Because of the way Oracle System Change Number (SCN) values are processed, Oracle never reads dirty data. Serializable isolation is possible, but the application program must be written to process the “Cannot serialize” exception. Applications can place locks explicitly using SELECT FOR UPDATE commands but this is not recommended.
50. What are the types of files used in Oracle recovery?
Datafiles, control files and two types of ReDo log files: OnLine Redo and Offline ReDo (which is also known as Archive ReDo).