Question 1

In database concurrency control, which type of lock prevents other transactions from modifying an item but still allows them to read it?

Accepted Answer

Introduction / Context:This question tests your understanding of lock modes used by a Database Management System (DBMS) to coordinate concurrent access. Specifically, it asks which lock permits reads but prevents writes, a core idea behind isolation and concurrency. Given Data / Assumptions: Multiple transactions may access the same data item concurrently. We need a lock that blocks updates but allows reads. Standard lock nomenclature: shared (S) vs. exclusive (X). Concept / Approach:A shared (S) lock is designed for read-only access. Many transactions can hold S locks concurrently on the same item. An exclusive (X) lock is required for updates and is incompatible with other S or X locks, preventing both reads and writes by others while held. Step-by-Step Solution: Identify the requirement: allow read, disallow change.Map lock modes: S allows read; X allows change and blocks others.Therefore the correct mode is the shared (S) lock. Verification / Alternative check:Compatibility matrix: S with S = compatible (reads proceed). S with X = incompatible (updates blocked). This matches the requirement. Why Other Options Are Wrong: Implicit lock: refers to who sets the lock (DBMS automatically), not the lock's permission model. Explicit lock: set by the application; again, not a lock mode. Exclusive lock: blocks both reads (by others) and writes; too restrictive. Common Pitfalls:Confusing “implicit/explicit” (how a lock is obtained) with “shared/exclusive” (what a lock allows). The question is about behavior, not acquisition method. Final Answer:Shared lock

Question 2

Database Administration (DBA): what is the overall responsibility of the DBA in an organization using a DBMS?

Accepted Answer

Introduction / Context:The Database Administrator (DBA) is accountable for ensuring that organizational data is accurate, available, secure, and performant. This question asks you to identify the broadest, most accurate statement of a DBA’s role. Given Data / Assumptions: A DBA coordinates across development and operations. Responsibilities span design, implementation, security, backup/recovery, and performance. DBA work includes facilitating database use and managing life-cycle tasks. Concept / Approach:Option (a) captures enablement and support. Option (c) enumerates development, operation, and maintenance. Together, they summarize the complete scope. Merely creating tables (option b) is too narrow. Step-by-Step Solution: Compare each option with industry-standard DBA duties.Identify that (a) and (c) together form an accurate, comprehensive description.Select the combined choice indicating both are correct. Verification / Alternative check:Typical DBA job descriptions include planning, capacity, security, performance tuning, backups, recovery, and enabling development teams—consistent with (a) and (c). Why Other Options Are Wrong: Create and populate tables: a small subset of DBA tasks. Only (a) or only (c) alone omit complementary aspects; together they are accurate. Common Pitfalls:Equating DBAs with only operational tasks or only development support. In practice, DBAs bridge both worlds. Final Answer:Both the first and third answers above are correct.

Question 3

In DBMS locking, which lock mode prevents any other transaction from accessing an item (neither read nor write) while the lock is held?

Accepted Answer

Introduction / Context:To preserve consistency, DBMSs use lock modes to control concurrent access. This question asks for the lock that blocks all other access (reads and writes) while held. Given Data / Assumptions: Multiple transactions may attempt to read or modify the same item. We want a lock that denies all concurrent access by others. Concept / Approach:An exclusive (X) lock grants a transaction the sole right to read and modify the item and prevents other transactions from acquiring shared or exclusive locks on the same item until it is released. Step-by-Step Solution: Recall lock compatibility: X is incompatible with both S and X.Therefore, while an X lock is held, no other read or write can proceed.Select “Exclusive lock.” Verification / Alternative check:Lock matrix check: X with S = conflict, X with X = conflict; the item is fully isolated. Why Other Options Are Wrong: Implicit/Explicit: describe how a lock is acquired (automatic vs. requested), not its blocking behavior. Shared lock: allows other shared locks and thus allows reads by others. Common Pitfalls:Assuming “exclusive” only blocks writers; in standard compatibility models, it blocks both readers and writers. Final Answer:Exclusive lock

Question 4

Transaction isolation levels: which level allows dirty reads, nonrepeatable reads, and phantom reads?

Accepted Answer

Introduction / Context:Isolation levels regulate what anomalies are possible when transactions run concurrently. The question asks which level is the least strict, permitting all three classic anomalies. Given Data / Assumptions: Dirty read: reading uncommitted changes. Nonrepeatable read: same row returns different values within a transaction. Phantom read: a re-run of a range query returns a different set of rows. Concept / Approach:Read uncommitted is the lowest isolation level. It allows dirty reads and therefore also permits nonrepeatable and phantom reads. Higher levels restrict these anomalies progressively. Step-by-Step Solution: Map anomalies to levels: Read uncommitted allows all.Read committed blocks dirty reads but not the others.Repeatable read blocks dirty and nonrepeatable, but not phantoms.Serializable blocks all three. Verification / Alternative check:Standards and vendor docs concur on the anomaly allowances described above, with some engine-specific nuances. Why Other Options Are Wrong:Read committed, Repeatable read, and Serializable progressively restrict anomalies; none of them allow all three. Common Pitfalls:Confusing “read committed” with “repeatable read”; the latter also prevents nonrepeatable reads, not just dirty reads. Final Answer:Read uncommitted

Question 5

Cursors: which cursor type stores primary keys at open time and uses the keys to fetch the current row values later (so changes by others are visible)?

Accepted Answer

Introduction / Context:Different cursor types balance stability versus currency of data. This question targets the cursor whose membership is fixed but values can reflect concurrent updates. Given Data / Assumptions: The cursor saves key values for identified rows when opened. When fetching, the cursor uses those keys to read current values. If a row is deleted, it becomes inaccessible; new rows are not added. Concept / Approach:A keyset cursor materializes the set of primary keys at open time. Data lookups at fetch time use those keys, so updates by other transactions are visible, deletions are detected, and inserts are not seen because the keyset is fixed. Step-by-Step Solution: Eliminate “static”: takes a snapshot; values do not change.Eliminate “forward only”: one-way movement; does not specify keyset behavior.Eliminate “dynamic”: membership and values can change; not just keyset.Select “Keyset”: fixed membership via stored keys; current values fetched. Verification / Alternative check:Vendor docs (e.g., SQL Server) describe keyset behavior exactly as stated: fixed row identity with live data values. Why Other Options Are Wrong: Static: both membership and values are static (snapshot). Dynamic: both membership and values can change. Forward only: movement characteristic, not value-currency behavior. Common Pitfalls:Confusing “dynamic” (membership can grow/shrink) with “keyset” (membership fixed, values current). Final Answer:Keyset

Question 6

Isolation level comparison: which level disallows dirty reads and nonrepeatable reads, but still allows phantom reads?

Accepted Answer

Introduction / Context:Understanding which anomalies are blocked at each isolation level is fundamental for designing correct transactional behavior under concurrency. Given Data / Assumptions: Dirty read must be disallowed. Nonrepeatable read must be disallowed. Phantom reads may still occur. Concept / Approach:Repeatable read ensures that if a transaction reads a row twice, the values do not change between reads (no dirty or nonrepeatable reads). It does not, however, lock ranges in a way that always prevents new qualifying rows from appearing, so phantom reads can still occur (engine specifics aside). Step-by-Step Solution: Read uncommitted: allows all anomalies — not acceptable.Read committed: blocks dirty reads only — still allows nonrepeatable and phantom reads.Repeatable read: blocks dirty and nonrepeatable — permits phantoms.Serializable: blocks all three — too strong for the stated condition. Verification / Alternative check:Refer to ANSI SQL isolation definitions; behavior matches the description, noting DBMS nuances (e.g., Next-Key locks may reduce phantoms). Why Other Options Are Wrong:Read committed and read uncommitted are too permissive; serializable is stricter than required. Common Pitfalls:Thinking repeatable read also blocks phantoms universally; that typically requires serializable or range locking. Final Answer:Repeatable read

Question 7

ACID properties: a transaction where either all individual actions occur or none occur exemplifies which property?

Accepted Answer

Introduction / Context:ACID is the cornerstone of reliable transaction processing. This question focuses on identifying the property that enforces the “all-or-nothing” execution semantics. Given Data / Assumptions: A transaction comprises multiple database actions. On success, all effects persist; on failure, none persist. Concept / Approach:Atomicity guarantees that a transaction is indivisible: either it fully commits or it fully rolls back. The other ACID properties are consistency (state validity), isolation (concurrency safety), and durability (survival of committed data). Step-by-Step Solution: Match “all or none” to the ACID property definitions.Atomicity is the only property that enforces all-or-nothing behavior.Select “atomic.” Verification / Alternative check:Consider a money transfer (debit and credit). Atomicity ensures both operations happen together or neither does. Why Other Options Are Wrong: Consistent: ensures integrity constraints hold before/after the transaction. Isolated: controls interference among concurrent transactions. Durable: ensures committed results survive failures. Common Pitfalls:Confusing durability (post-commit persistence) with atomicity (commit as a single unit). Final Answer:atomic

Question 8

Isolation levels: at which level are dirty reads, nonrepeatable reads, and phantom reads all prevented?

Accepted Answer

Introduction / Context:Designing for correctness often means choosing an isolation level that disallows concurrency anomalies. This question asks which level blocks all three classic anomalies. Given Data / Assumptions: We want to disallow dirty reads, nonrepeatable reads, and phantom reads. Standard ANSI SQL isolation definitions are assumed. Concept / Approach:Serializable is the strictest isolation level, ensuring the outcome is equivalent to some serial (one-at-a-time) execution order. As a result, dirty reads, nonrepeatable reads, and phantom reads are all prevented by ensuring appropriate locking or versioning semantics. Step-by-Step Solution: Read uncommitted: allows all anomalies.Read committed: prevents dirty reads only.Repeatable read: also prevents nonrepeatable reads but not phantoms.Serializable: prevents all three — select this. Verification / Alternative check:Check DBMS docs: Serializable isolation uses range locks or predicate locking (or MVCC rules) to eliminate phantoms and other anomalies. Why Other Options Are Wrong:They permit at least one of the anomalies listed and thus do not satisfy the requirement. Common Pitfalls:Assuming repeatable read blocks phantoms in all engines; typically only serializable does. Final Answer:Serializable

Question 9

Locking terminology: what do we call the “size” or scope of a lock (for example, row-level vs. page-level vs. table-level)?

Accepted Answer

Introduction / Context:Locking can occur at different scopes, from fine-grained (row) to coarse-grained (table). Understanding the term for this scope helps reason about performance and contention. Given Data / Assumptions: Locks may apply to rows, pages/blocks, partitions, or entire tables. The question asks for the standard term for this scope/size. Concept / Approach:Lock granularity refers to the amount of data covered by a lock. Finer granularity increases concurrency but may raise overhead; coarser granularity lowers overhead but can increase contention. Step-by-Step Solution: Identify the concept of scope/size of locked data.Associate this with the term “granularity.”Choose “lock granularity.” Verification / Alternative check:DBMS tuning guides consistently use “granularity” to describe row/page/table lock scope. Why Other Options Are Wrong: Implicit/exclusive/shared: describe acquisition method or mode, not size. Common Pitfalls:Assuming coarser locks always perform better; they reduce overhead but may dramatically lower concurrency. Final Answer:lock granularity

Question 10

Lock acquisition: what is the term for locks that are automatically placed by the DBMS (as opposed to locks explicitly requested by an application)?

Accepted Answer

Introduction / Context:DBMSs can place locks automatically to enforce isolation, or applications can request locks explicitly. The question asks for the name of the system-placed variety. Given Data / Assumptions: Two acquisition styles exist: automatic (by the DBMS) and manual (by the application). Lock mode (shared/exclusive) is separate from acquisition style. Concept / Approach:Implicit locks are automatically acquired and released by the DBMS as part of query and transaction processing. Explicit locks are requested directly by application code using lock statements or hints. Step-by-Step Solution: Identify the actor acquiring the lock: the system.Map system-acquired to “implicit.”Select “implicit locks.” Verification / Alternative check:DBMS documentation distinguishes implicit locking (default engine behavior) from explicit locking APIs or hints. Why Other Options Are Wrong: Explicit locks: requested by applications, not automatic. Exclusive/shared: lock modes, not acquisition style. Common Pitfalls:Conflating lock mode (S/X) with how the lock is obtained (implicit/explicit). They are orthogonal concepts. Final Answer:implicit locks

Question 11

Database cursors and concurrency visibility Which cursor type shows changes of any kind and from any source as they occur (i.e., the application always sees the latest committed state)?

Accepted Answer

Introduction / Context:In relational databases, a cursor defines how an application scans a result set and what changes it can “see” while it is open. Understanding the visibility guarantees of each cursor type is essential for writing correct, concurrent applications that avoid stale views of data or surprising anomalies. Given Data / Assumptions: We are comparing common server-side cursor models: forward-only, static, keyset, and dynamic. Other sessions may insert, update, or delete rows while a cursor is open. We care about whether the cursor reflects those committed changes. Concept / Approach: A dynamic cursor is defined so that it reflects the current, committed state of the underlying tables. By contrast, a static cursor is a snapshot taken when the cursor opens, a keyset cursor has a fixed key list with values refreshed for those keys, and a forward-only cursor is a simple streaming cursor with limited navigation and implementation-dependent visibility. The question asks which cursor shows any type of committed change from any source while it is open—this is precisely the property of a dynamic cursor. Step-by-Step Solution: 1) Identify cursor types and their visibility properties.2) Static cursor: data does not change from the application's perspective after open.3) Keyset cursor: set of keys (rows) is fixed, but values for those keys can change; inserts that introduce new keys are not visible.4) Forward-only cursor: minimal navigation; visibility varies but typically not fully dynamic.5) Dynamic cursor: reflects inserts, updates, and deletes from any session as they become committed.6) Therefore, the correct choice is the dynamic cursor. Verification / Alternative check: Vendor documentation (e.g., SQL Server's cursor types) describes dynamic cursors as showing all committed changes, confirming our reasoning independent of implementation details. Why Other Options Are Wrong: Forward-only cursor: navigation property, not a visibility guarantee; typically not fully dynamic. Static cursor: materialized snapshot, so no visibility to new inserts or deletes. Keyset cursor: does not see rows outside the fixed key list; partial visibility only. Read-only snapshot cursor: a distractor that implies static behavior, not dynamic. Common Pitfalls: Confusing navigation (forward-only) with concurrency visibility. Assuming keyset cursors see new rows; they do not—only existing keys refresh. Believing static cursors update themselves; they are intentionally stable. Final Answer: Dynamic cursor.

Question 12

Transaction anomalies and isolation What is it called when a transaction reads data that was modified by another transaction but not yet committed (i.e., uncommitted changes)?

Accepted Answer

Introduction / Context:Transaction isolation defines which phenomena can occur when multiple transactions execute concurrently. Recognizing anomalies helps you choose the right isolation level and locking strategy for correctness and performance. Given Data / Assumptions: A transaction reads a row that another transaction has changed but not committed. We want the standard name for this anomaly in database theory and practice. Concept / Approach: A read of uncommitted data is classically termed a dirty read. If the writing transaction later rolls back, the reader will have observed a value that never becomes durable, violating consistency expectations and leading to incorrect decisions by the reading transaction. Step-by-Step Solution: 1) Define the scenario: T1 updates a row; T2 reads the updated value before T1 commits.2) If T1 rolls back, T2 has read a value that never existed in a committed state.3) This is the textbook definition of a dirty read.4) Prevent by using at least READ COMMITTED isolation or explicit locks. Verification / Alternative check: Standard texts and DBMS documentation list dirty reads as allowed at READ UNCOMMITTED and disallowed at READ COMMITTED and above, confirming the term and conditions. Why Other Options Are Wrong: Nonrepeatable read: re-reading the same row yields a different committed value later. Phantom read: re-executing a range query returns new rows that were inserted by others. Consistent read: a term used by some engines (e.g., snapshot reads) to mean a stable view, not an anomaly. Lost update: concurrent updates overwrite each other; different anomaly. Common Pitfalls: Equating dirty reads with nonrepeatable reads; the former involves uncommitted data. Assuming all engines even allow dirty reads; many default to READ COMMITTED. Final Answer: Dirty read.

Question 13

Phantoms in range queries What anomaly occurs when a transaction re-executes a predicate (range) query and finds additional rows inserted by other transactions since its prior read?

Accepted Answer

Introduction / Context:Beyond single-row changes, range queries can be affected by concurrent inserts or deletes, producing anomalies known as phantoms. Correct handling of phantoms is central to serializable isolation and predicate locking theory. Given Data / Assumptions: We execute the same search condition twice, e.g., WHERE amount > 1000. The second execution returns additional rows that were not present previously. Other transactions may have inserted rows satisfying the predicate. Concept / Approach: When new rows appear that match the prior predicate, we call this a phantom read. It differs from nonrepeatable reads (which affect the same previously read row) because the issue is the changing membership of the result set, not just changes to a particular row's values. Step-by-Step Solution: 1) First SELECT with a predicate returns N rows.2) Another transaction inserts rows that satisfy the predicate and commits.3) The second SELECT returns N + k rows; the k new rows are the phantoms.4) Prevent at SERIALIZABLE isolation or via predicate locks/range locks. Verification / Alternative check: Standard isolation definitions (ANSI/ISO, academia, and vendor docs) illustrate phantoms as changes in the set of rows qualifying for a predicate, confirming the term and mitigation via serializable isolation or appropriate locking/indexing strategies. Why Other Options Are Wrong: Nonrepeatable read: the same row changes value, not new rows appearing. Dirty read: reading uncommitted data, not committed inserts after the fact. Consistent read: a stable snapshot, not an anomaly. Lost update: clobbering concurrent updates, unrelated to range membership. Common Pitfalls: Using row locks only; they do not protect against new qualifying rows. Confusing repeatable read with phantom protection; some engines still allow phantoms at REPEATABLE READ. Final Answer: Phantom read.

Question 14

Choosing the right isolation level At which isolation level are dirty reads disallowed, while nonrepeatable reads and phantom reads can still occur?

Accepted Answer

Introduction / Context:Isolation levels trade off concurrency and correctness. READ COMMITTED is a widely used default that prevents reading uncommitted data yet still allows some anomalies that stronger levels prevent. Given Data / Assumptions: Goal: identify the level that blocks dirty reads only. We recognize standard levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and vendor-specific SNAPSHOT. Concept / Approach: READ COMMITTED guarantees that all data seen has been committed. However, between two reads, other transactions may commit updates or insert new qualifying rows, allowing nonrepeatable reads and phantom reads. Stronger levels add protections but at higher contention cost. Step-by-Step Solution: 1) READ UNCOMMITTED allows dirty reads → not the answer.2) READ COMMITTED forbids dirty reads but allows nonrepeatable and phantom reads → this matches the description.3) REPEATABLE READ prevents nonrepeatable reads on previously read rows, though phantoms may persist depending on the engine.4) SERIALIZABLE prevents both nonrepeatable reads and phantoms by ensuring an execution equivalent to some serial order.5) SNAPSHOT (where available) prevents many anomalies with versioning but is not the canonical answer to this description. Verification / Alternative check: ANSI/ISO isolation descriptions and major DBMS documentation align: READ COMMITTED disallows dirty reads while permitting the others under concurrency. Why Other Options Are Wrong: Read uncommitted: still allows dirty reads. Repeatable read: typically prevents nonrepeatable reads. Serializable: prevents both nonrepeatable and phantom reads. Snapshot isolation: vendor-specific semantics; not the textbook match. Common Pitfalls: Assuming READ COMMITTED prevents all anomalies; it does not. Conflating engine-specific REPEATABLE READ with serializable semantics. Final Answer: Read committed.

Question 15

Optimistic vs. pessimistic locking What is the primary advantage of optimistic locking in high-concurrency applications?

Accepted Answer

Introduction / Context:Optimistic locking is a concurrency control strategy that assumes conflicts are rare. Instead of locking rows up-front, transactions proceed and validate at commit time, improving throughput when contention is low. Given Data / Assumptions: Many reads and relatively fewer conflicting writes. Application can retry on conflict (e.g., version column mismatch). We compare optimistic vs. pessimistic approaches. Concept / Approach: Optimistic locking avoids holding locks during long business logic phases. It typically uses a version/timestamp check at update time. If another transaction has changed the row, the update fails and must be retried. The benefit is reduced blocking and better scalability under low-conflict patterns. Step-by-Step Solution: 1) Read the data and carry a version value (e.g., row_version = 12).2) Perform processing without locking the row.3) On update, check that the current row_version is still 12.4) If unchanged, update and increment the version; if changed, detect conflict and retry.5) Locks (or write latches) are acquired only at the final write phase, not throughout. Verification / Alternative check: Empirical performance studies show optimistic techniques excel when conflicts are rare because they minimize blocking and deadlocks, though they may degrade with frequent conflicts due to retries. Why Other Options Are Wrong: Obtaining the lock before processing (pessimistic) increases blocking time. “No lock ever required” is false; writes ultimately require coordination. High-conflict workloads favor pessimistic locking to avoid repeated retries. Optimistic locking does not guarantee serializability by itself; it enforces write integrity via version checks. Common Pitfalls: Using optimistic locking where conflicts are frequent, causing thrashing. Forgetting to include a version column check, leading to lost updates. Final Answer: The lock is obtained only when the transaction is ready to write or commit changes.

Question 16

ACID properties of transactions Which ACID property guarantees that once a transaction commits, its changes are permanent and will survive failures?

Accepted Answer

Introduction / Context:ACID—Atomicity, Consistency, Isolation, Durability—defines core guarantees for reliable transactions. Each property addresses a distinct concern, and durability handles permanence after commit. Given Data / Assumptions: We are asked which property ensures permanence of committed changes. Standard transactional storage uses write-ahead logs, checkpoints, and crash recovery. Concept / Approach: Durability means that once the DBMS acknowledges COMMIT, the system guarantees the committed state will persist, even after crashes. Logging and recovery protocols ensure the database can be restored to the last committed state. Step-by-Step Solution: 1) Atomicity: all-or-nothing execution of a transaction.2) Consistency: each commit moves the database from one valid state to another, respecting constraints.3) Isolation: concurrent transactions appear to run alone.4) Durability: after commit, changes persist across failures → the answer. Verification / Alternative check: Core database literature and vendor docs describe durability as guaranteed via WAL, fsync/flush semantics, and recovery mechanisms that reapply committed updates after a crash. Why Other Options Are Wrong: Atomic: scope is commit vs. rollback, not persistence across crashes. Consistent: logical correctness, not physical permanence. Isolated: concurrency behavior, not storage survivability. Deterministic: not an ACID property. Common Pitfalls: Equating durability with backup; backups are complementary, not the same guarantee. Assuming durability without proper hardware flushes or reliable storage. Final Answer: Durable.

Question 17

Static snapshot cursors Which cursor type shows the data exactly as it was when the cursor opened, regardless of subsequent inserts, updates, or deletes by other sessions?

Accepted Answer

Introduction / Context:Cursors differ in whether they present a live view of underlying tables or a stable snapshot. Static cursors are designed for repeatable, predictable reads without mid-scan surprises from concurrent activity. Given Data / Assumptions: The cursor should not reflect concurrent commits after it opens. We compare static, keyset, dynamic, and navigation-focused types. Concept / Approach: A static cursor materializes the result set when opened. Because the data is copied or fixed at open time, later changes are not visible. This behavior simplifies application logic that expects identical results when re-reading within the same cursor session. Step-by-Step Solution: 1) Identify snapshot behavior as the key requirement.2) Static cursor matches snapshot semantics.3) Keyset fixes membership but refreshes values for those keys—still not a full snapshot.4) Dynamic reflects all concurrent changes and therefore is not static.5) Forward-only speaks to direction, not visibility. Verification / Alternative check: DBMS manuals describe static cursors as insensitive to changes after open, confirming snapshot semantics and predictable iteration behavior. Why Other Options Are Wrong: Forward-only: direction only. Keyset: fixed membership, but values can change. Dynamic: fully reflects new commits—opposite of snapshot. Scrollable dynamic: still dynamic in visibility. Common Pitfalls: Assuming keyset is identical to static; keyset can still show updates for existing keys. Using dynamic when a stable audit-like iteration is required. Final Answer: Static cursor.

Question 18

Forward-only navigation Which cursor type allows the application to move only in the forward direction through the recordset (no backward scrolling)?

Accepted Answer

Introduction / Context:Cursors define both visibility and navigation. Some applications need the simplest, most efficient traversal: move to the next row only, without random access or backward movement. Given Data / Assumptions: Requirement: iterate forward sequentially through a result set. No need to scroll backward or jump to arbitrary positions. Concept / Approach: A forward-only cursor provides minimal navigation capability—FETCH NEXT repeatedly. Because it avoids maintaining bidirectional state, it can be lighter-weight and faster, especially for streaming large results. Step-by-Step Solution: 1) Map the requirement (only move forward) to cursor types.2) Forward-only cursor exactly matches this constraint.3) Static, keyset, and dynamic speak to visibility; they may support scrolling, so they are not defined by forward-only navigation.4) Bidirectional cursor is the opposite of the requirement. Verification / Alternative check: Database documentation consistently defines forward-only as a navigation limitation: you can only fetch the next row each time. This fits ETL and stream processing scenarios well. Why Other Options Are Wrong: Static cursor: snapshot property, not inherently forward-only. Keyset cursor: membership/visibility semantics, not navigation-only. Dynamic cursor: live visibility, not a navigation constraint. Bidirectional cursor: allows backward movement, contrary to the requirement. Common Pitfalls: Assuming forward-only implies static visibility; engines may still vary on visibility. Choosing bidirectional cursors unnecessarily, increasing overhead. Final Answer: Forward-only cursor.

Question 19

Locking semantics and control Locks that are explicitly requested by an application or a specific SQL statement (rather than acquired implicitly by the engine) are called:

Accepted Answer

Introduction / Context:Database engines automatically take many locks implicitly to enforce isolation. Sometimes, applications or DBAs request locks directly to control access patterns or prevent specific anomalies. These are known by a standard term. Given Data / Assumptions: We distinguish between the engine's automatic behavior and application-invoked locking. Examples include explicit table locks or SELECT ... FOR UPDATE. Concept / Approach: When an application or statement requests a lock via syntax or procedure, the lock is explicit. By contrast, implicit locks are acquired automatically by the optimizer/executor to satisfy isolation guarantees without programmer intervention. Step-by-Step Solution: 1) Identify whether the lock is taken automatically or by explicit command.2) If taken by command (e.g., LOCK TABLE, SELECT FOR UPDATE), it is an explicit lock.3) Exclusive/shared describe lock modes, not how the lock was requested.4) Therefore, the correct term is explicit lock. Verification / Alternative check: DBMS references categorize locks by acquisition method (implicit vs. explicit) and by mode (shared, exclusive, update). The question asks the method, not the mode, matching “explicit.” Why Other Options Are Wrong: Implicit locks: acquired automatically by the engine, not by command. Exclusive locks: a mode that prevents others from writing, orthogonal to explicitness. Shared locks: another mode, allowing concurrent readers. Update locks: a specialized mode for conversion patterns, not acquisition method. Common Pitfalls: Confusing “how” a lock is taken (explicit/implicit) with “what” the lock does (mode). Using broad exclusive locks instead of precise row/page locks, causing contention. Final Answer: Explicit locks.

Question 20

Nonrepeatable reads defined What anomaly occurs when a transaction re-reads rows it previously read and finds that some have been modified or deleted by another committed transaction?

Accepted Answer

Introduction / Context:Nonrepeatable reads reflect instability of previously observed rows under concurrent updates. They are distinct from phantoms (new rows) and from dirty reads (uncommitted data). Understanding the distinctions guides proper isolation selection. Given Data / Assumptions: We read a particular row or set of identified rows. Later, re-reading the same rows returns different committed values or shows that rows were deleted. Another transaction committed the changes in between. Concept / Approach: At READ COMMITTED, each statement sees a fresh committed view, so repeated reads of the same row can differ. Preventing this requires REPEATABLE READ (row-level stability) or SERIALIZABLE (full schedule equivalence), depending on the engine's semantics. Step-by-Step Solution: 1) Identify that the same row is read twice.2) Detect a change or deletion between reads due to another committed transaction.3) This is the definition of a nonrepeatable read.4) Mitigate with stronger isolation (REPEATABLE READ or SERIALIZABLE) or appropriate locking (e.g., SELECT ... FOR UPDATE). Verification / Alternative check: Academic and vendor references classify nonrepeatable reads as changes to previously read rows, in contrast with phantoms that concern new qualifying rows for a predicate. Why Other Options Are Wrong: Phantom read: concerns new rows appearing in a range, not changes to an already-read row. Dirty read: involves uncommitted data, not committed changes. Consistent read: stable view, typically the opposite of this anomaly. Write skew: a snapshot anomaly involving concurrent writes to disjoint rows under SI. Common Pitfalls: Using READ COMMITTED while expecting identical re-reads of the same row. Confusing predicate instability (phantoms) with row instability (nonrepeatable read). Final Answer: Nonrepeatable read.

Managing Multiuser Databases Questions

Questions

Practice smarter