Q61
Q61 What mechanism is commonly used to ensure the durability of transactions in database systems?
Log-based recovery
Two-phase locking
Timestamp ordering
Savepoints
Q62
Q62 Which SQL statement is used to start a transaction in most RDBMS?
BEGIN TRANSACTION
START
BEGIN
INITIATE TRANSACTION
Q63
Q63 In SQL, how can you force a transaction to roll back?
ROLLBACK TRANSACTION
UNDO TRANSACTION
RESET TRANSACTION
CANCEL TRANSACTION
Q64
Q64 How can the isolation level of a transaction be set in SQL Server to prevent dirty reads?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Q65
Q65 Which SQL command is used to save a point within a transaction that can be rolled back to, without affecting the preceding statements of the transaction?
SAVEPOINT
CHECKPOINT
COMMIT TO POINT
ROLLBACK TO SAVEPOINT
Q66
Q66 A transaction fails to commit and is rolled back.
What is a common reason for this behavior?
The transaction was manually rolled back by the user
A deadlock was detected
A data validation rule was violated
The database system crashed
Q67
Q67 When attempting to execute multiple transactions concurrently, you notice that some transactions are waiting indefinitely. What is the likely cause?
Network latency
Deadlocks
Inefficient query execution
Insufficient database indexing
Q68
Q68 After changing the isolation level of transactions, a significant decrease in performance is observed.
What could be the reason?
Increased lock contention due to a higher isolation level
Decreased cache utilization
Hardware malfunctions
Network issues
Q69
Q69 A transaction is experiencing repeated failures due to lock timeouts.
What strategy can help resolve this issue?
Decreasing the isolation level of the transaction
Increasing the lock timeout value
Splitting the transaction into smaller transactions
All of the above
Q70
Q70 Which concurrency control technique involves locking resources to prevent concurrent transactions from accessing them simultaneously?
Timestamp ordering
Lock-based protocols
Validation-based protocols
Snapshot isolation
Q71
Q71 In the context of concurrency control, what does the term "deadlock" refer to?
A lock that cannot be released
A transaction that cannot be rolled back
A situation where transactions wait indefinitely for each other
A failed concurrency control mechanism
Q72
Q72 What is the primary goal of the two-phase locking (2PL) protocol?
To ensure all transactions are executed in parallel
To allow transactions to access any data at any time
To prevent deadlocks
To ensure serializability of transactions
Q73
Q73 Which of the following scenarios is a potential drawback of using optimistic concurrency control?
Transactions are serialized automatically
Deadlocks are more frequent
Increased overhead due to rollback of transactions
Reduced throughput due to excessive locking
Q74
Q74 The isolation level that allows dirty reads, non-repeatable reads, and phantom reads is:
Read Uncommitted
Read Committed
Repeatable Read
Serializable
Q75
Q75 In database systems, the phenomenon where a transaction re-reads data it has previously read and finds that another transaction has modified it is called:
Dirty read
Non-repeatable read
Phantom read
Serialization anomaly
Q76
Q76 To set the transaction isolation level to Serializable in SQL Server, which command should be used?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ISOLATION LEVEL SERIALIZABLE
TRANSACTION SET ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION LEVEL AS SERIALIZABLE
Q77
Q77 In PostgreSQL, how can you acquire an exclusive lock on a table to prevent other transactions from reading or writing to it?
LOCK TABLE table_name IN EXCLUSIVE MODE
EXCLUSIVE LOCK ON table_name
SET LOCK table_name EXCLUSIVE
LOCK table_name EXCLUSIVE
Q78
Q78 Which command is used in MySQL to start a transaction with a consistent snapshot, providing repeatable reads for the duration of the transaction?
START TRANSACTION WITH CONSISTENT SNAPSHOT
BEGIN WORK WITH CONSISTENT SNAPSHOT
CREATE TRANSACTION WITH SNAPSHOT
BEGIN TRANSACTION CONSISTENT SNAPSHOT
Q79
Q79 How do you enable automatic detection and resolution of update conflicts in Oracle when using optimistic concurrency control?
ENABLE AUTOMATIC CONFLICT RESOLUTION
SET TRANSACTION RESOLVE CONFLICTS AUTOMATICALLY
DBMS_LOCK.RESOLVE
DBMS_CONFLICT.RESOLVE_AUTOMATICALLY
Q80
Q80 A transaction frequently fails due to lock timeouts.
What is a simple strategy to reduce the likelihood of this happening?
Decrease the transaction isolation level
Increase the lock timeout duration
Split the transaction into smaller parts
All of the above
Q81
Q81 After implementing row-level locking, the system experiences deadlocks.
Which approach can help minimize this issue?
Using table-level locks instead
Applying a consistent locking order
Reducing the transaction isolation level
Implementing lock escalation
Q82
Q82 You notice that a particular query within a transaction is causing lock contention.
What is a first step in addressing this issue?
Re-writing the query to access fewer rows
Increasing the database's hardware resources
Switching to table-level locks
Disabling locks for the transaction
Q83
Q83 What is the primary purpose of indexing in a database?
To increase transaction speeds
To decrease database storage requirements
To speed up the retrieval of records based on attribute values
To secure the database
Q84
Q84 Which type of database index is optimized for equality searches?
Clustered index
Non-clustered index
Full-text index
Bitmap index
Q85
Q85 What is a full-text search primarily used for?
Searching for numeric data
Searching for exact matches in textual data columns
Performing complex searches within textual data columns
Indexing foreign keys
Q86
Q86 In a relational database, what does a composite index refer to?
An index that combines multiple tables
An index built on a single column
An index that includes multiple columns from a single table
An index used exclusively for joins
Q87
Q87 Which of the following best describes the difference between a clustered and a non-clustered index?
A clustered index speeds up data insertion, whereas a non-clustered index speeds up data deletion
A clustered index affects the physical order of rows, whereas a non-clustered index does not
A clustered index can only be created on primary keys, whereas a non-clustered index can be created on any column
A clustered index is automatically created when a table is created, whereas a non-clustered index must be created manually
Q88
Q88 What is a B-tree index suitable for?
Only equality searches
Only range searches
Both equality and range searches
Neither equality nor range searches
Q89
Q89 How can you create a non-clustered index on the "email" column of the "Users" table in SQL?
CREATE NONCLUSTERED INDEX idx_email ON Users(email)
CREATE INDEX idx_email ON Users(email) NONCLUSTERED
INDEX CREATE ON Users(email) NONCLUSTERED
CREATE INDEX idx_email ON Users USING NONCLUSTERED(email)
Q90
Q90 Which SQL statement is used to create a full-text index in MySQL?
CREATE FULLTEXT INDEX ft_index ON Articles(content)
FULLTEXT INDEX CREATE ON Articles(content)
INDEX FULLTEXT CREATE Articles(content)
CREATE INDEX FULLTEXT ON Articles(content)
