Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
A high-concurrency application on Cloud Spanner uses the default SERIALIZABLE isolation level with pessimistic concurrency control. During peak traffic, 99th-percentile write latency spikes while CPU utilization remains below 40%.
Querying SPANNER_SYS.LOCK_STATS_TOP_10MINUTE and SPANNER_SYS.TXN_STATS_TOP_10MINUTE reveals:
lock_wait_seconds from ReaderShared and WriterShared lock conflicts across multiple columns of the Orders tablecommit_abort_count and commit_retry_count from wound-wait deadlock resolutionSELECT * without a primary key filter to locate rows before updating one columnWhich optimization should you implement to minimize lock contention and transaction aborts?
Adding processing units or nodes to a Cloud Spanner instance increases available CPU, memory, and split-serving capacity, allowing the database to handle higher overall throughput and distribute data splits across more servers.
ReaderShared and WriterShared locks.lock_wait_seconds or commit_abort_count caused by poorly scoped transactions.Latency spikes correlated with near-100% CPU utilization across the instance and evenly distributed key access patterns, indicating a genuine compute resource shortage rather than transactional locking conflicts.
Cloud Spanner applies locks at cell-level granularity (the intersection of a specific row and column) exclusively for read-write transactions. Read-only transactions observe a consistent snapshot of the commit history without acquiring any locks, so they never block concurrent writes.
ReaderShared locks: Moving the discovery query into a read-only transaction ensures the scan acquires zero locks, preventing interference with concurrent WriterShared requests.Orders table.SELECT * inside the read-write transaction) ensures unrelated columns remain unlocked and accessible to other transactions.ABORTED errorsWhen CPU utilization is low while lock wait times and aborts are high, the bottleneck is logical lock contention rather than compute capacity. Separating lock-free discovery from targeted primary-key updates directly addresses the root cause of ReaderShared versus WriterShared conflicts while preserving strict SERIALIZABLE guarantees on the write operation itself.
REPEATABLE READ isolation uses snapshot isolation, where standard reads see a consistent snapshot without acquiring locks. However, enabling pessimistic concurrency control with a SELECT ... FOR UPDATE clause forces the database to acquire Exclusive locks on every data cell and index gap within the scanned range until the transaction commits or rolls back.
ReaderShared locks with Exclusive locks prevents other transactions from even reading the locked cells, worsening blocking rather than minimizing it.SELECT * query lacks a primary key filter, FOR UPDATE locks all existing rows, every column, and the gaps between keys across the scanned range, blocking concurrent INSERT and UPDATE operations.REPEATABLE READ does not enforce full serializability and can introduce write skew anomalies for any reads outside the FOR UPDATE statement.Exclusive locks block both concurrent reads and concurrent writes on the locked cells.FOR UPDATE apply gap locking, stalling new row insertions inside the scanned key range.REPEATABLE READ with pessimistic locking is intended to mirror traditional row-locking semantics, not to fix unfiltered table scans.The application was migrated from a traditional relational database requiring REPEATABLE READ isolation and needed FOR UPDATE on a narrow, primary-key-filtered lookup to serialize specific row updates and prevent write skew.
Optimistic concurrency control executes reads and queries inside a read-write transaction without acquiring locks during execution, assuming conflicts are rare. Under SERIALIZABLE isolation, all data read during the transaction is validated at commit time to verify that no concurrent transaction modified those cells after the read timestamp.
SELECT * inside the read-write transaction means the validation phase checks every column of every scanned row at commit time.SELECT *) maximizes the surface area for commit conflicts.The workload experienced low read-write contention with mostly read-only or non-overlapping operations, and long-running reads needed to avoid blocking latency-sensitive writes without triggering frequent validation failures.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated