Data Modification, Transactions, and Concurrency

Introduction

SQL is not only a language for retrieving information. In operational systems, it is also used to create, alter, and remove data while preserving business rules and protecting the database from simultaneous, conflicting activity. These responsibilities are especially important in business analytics because analytical results are only as reliable as the data-management processes that produce the underlying data.

A poorly designed data-modification operation can create duplicate customers, incorrect balances, orphan records, or irrecoverable losses. A poorly controlled transaction can allow users to observe incomplete work or overwrite one another’s changes. This chapter explains how SQL modification statements work, how transactions provide reliable units of work, and how database management systems control concurrent activity through isolation, locks, logging, and recovery.

SQL syntax varies among PostgreSQL, MySQL, SQL Server, Oracle, and other systems. The examples use broadly familiar SQL, but production code should always be checked against the documentation for the selected DBMS.

Learning Objectives

By the end of this chapter, you should be able to:

• use INSERT, UPDATE, DELETE, and MERGE safely;
• distinguish statement-level and transaction-level integrity;
• explain the ACID properties of transactions;
• describe isolation levels, anomalies, and locking mechanisms;
• identify causes of deadlocks and apply prevention and recovery strategies;
• explain write-ahead logging, checkpoints, commit, rollback, and crash recovery;
• design modification procedures that support consistency, auditability, and security;
• evaluate a data-modification process in a realistic business scenario.

1. Data Modification as Controlled State Change

A relational database represents an organizational state: customers, products, orders, payments, employees, or other entities at a particular time. A modification changes that state. The central professional question is not merely whether a statement executes successfully, but whether the resulting state is valid, complete, authorized, and recoverable.

Four fundamental data-modification statements are:

A modification should satisfy several controls:

1. Target control: the intended table and rows are identified.
2. Domain control: values have acceptable types, ranges, and formats.
3. Entity control: primary-key values uniquely identify records.
4. Referential control: foreign-key relationships remain valid.
5. Business-rule control: organizational rules are preserved.
6. Authorization control: the actor is permitted to make the change.
7. Recovery control: the change can be committed or undone as a unit.

1.1 INSERT

The safest general form names both the columns and the values:

INSERT INTO Customer (customer_id, customer_name, email)
VALUES (1042, 'Mina Patel', '[email protected]');

Naming columns prevents an operation from silently depending on the table’s physical column order. If a column has a default value, it may be omitted deliberately:

INSERT INTO Customer (customer_id, customer_name)
VALUES (1043, 'Jon Bell');

A set of rows can be inserted using a query:

INSERT INTO CustomerArchive (customer_id, customer_name, archived_at)
SELECT customer_id, customer_name, CURRENT_TIMESTAMP
FROM Customer
WHERE status = 'INACTIVE';

The query should be tested independently before it is used as the source of an insert. Constraints may reject the operation if keys, types, or required values are invalid. Depending on the DBMS, identity columns, sequences, or generated keys may be used instead of manually supplied identifiers.

1.2 UPDATE

An UPDATE changes values in rows selected by a predicate:

UPDATE Product
SET unit_price = unit_price * 1.05
WHERE category_id = 7;

The WHERE clause is a critical safety boundary. Without it, every row may be changed:

-- Dangerous unless every product should be discontinued
UPDATE Product
SET status = 'DISCONTINUED';

A safer workflow is to execute the predicate as a SELECT first:

SELECT product_id, unit_price
FROM Product
WHERE category_id = 7;

Then confirm the expected row count and values. In high-risk procedures, an additional condition such as a version number or last-modified timestamp can implement optimistic concurrency control:

UPDATE Account
SET balance = balance + 100,
    version_no = version_no + 1
WHERE account_id = 12
  AND version_no = 4;

If zero rows are affected, another process may have changed the record; the application should detect this rather than assuming success.

1.3 DELETE

A DELETE removes rows, not merely their visible values:

DELETE FROM SessionLog
WHERE expires_at < CURRENT_TIMESTAMP;

Before a consequential deletion, inspect the candidate set:

SELECT COUNT(*)
FROM SessionLog
WHERE expires_at < CURRENT_TIMESTAMP;

Foreign keys determine what happens to dependent rows. With ON DELETE RESTRICT or equivalent behavior, deletion of a parent may be rejected. With ON DELETE CASCADE, related rows may be removed automatically. Cascades are useful when the dependent rows have no independent meaning, but dangerous when a user does not realize the breadth of the operation.

For regulatory, analytical, or audit reasons, organizations often use a logical deletion instead:

UPDATE Customer
SET is_deleted = TRUE,
    deleted_at = CURRENT_TIMESTAMP
WHERE customer_id = 1042;

Logical deletion preserves history but requires every relevant query and security rule to exclude logically deleted rows when appropriate.

1.4 MERGE and Upsert Patterns

A synchronization process commonly receives a source dataset containing new and changed records. Conceptually, a merge compares source and target rows using a matching condition. Matching rows may be updated; nonmatching source rows may be inserted. Some products also support deletion of target rows absent from the source.

A conceptual example is:

MERGE INTO Customer AS t
USING CustomerImport AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET customer_name = s.customer_name,
             email = s.email
WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, email)
  VALUES (s.customer_id, s.customer_name, s.email);

MERGE syntax and behavior differ significantly among DBMS products. A robust merge requires:

• a source key that is unique, or a defined rule for duplicates;
• a precisely specified match condition;
• validation of source data before modification;
• explicit handling of unmatched target rows if synchronization requires deletion;
• careful consideration of concurrent changes;
• logging of inserted, updated, and deleted counts.

If the source contains two rows matching one target and the DBMS cannot determine a single update, the operation may fail or behave differently across products. Never assume that a merge is automatically safer than separate statements.

2. Integrity Constraints and Defensive Modification

Constraints are declarative consistency controls. Common constraints include:

• PRIMARY KEY: uniquely identifies each row and normally disallows null values;
• FOREIGN KEY: requires a value to correspond to a key in another table, subject to nullability and action rules;
• UNIQUE: prevents duplicate values or combinations of values;
• NOT NULL: requires a value;
• CHECK: restricts values according to a Boolean condition;
• DEFAULT: supplies a value when one is omitted.

For example:

CREATE TABLE OrderLine (
    order_id     INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES CustomerOrder(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

A constraint is stronger than an application convention because every authorized database client is subject to it. However, constraints cannot express every business rule. For example, a rule stating that a customer’s total unpaid balance must not exceed an approved credit limit may require a transaction, a trigger, a stored procedure, or application logic coordinated with suitable locking.

Input values should be passed through parameterized statements rather than concatenated into SQL strings. Parameterization reduces SQL-injection risk, clarifies type handling, and separates code from data. Modification privileges should follow least privilege: a reporting account should generally not possess DELETE or unrestricted UPDATE rights.

3. Transactions and the ACID Model

A transaction is a logical unit of work containing one or more database operations. It begins explicitly or implicitly, performs reads and writes, and ends with COMMIT or ROLLBACK.

BEGIN;

UPDATE Inventory
SET quantity_on_hand = quantity_on_hand - 2
WHERE product_id = 88
  AND quantity_on_hand >= 2;

INSERT INTO ShipmentLine (shipment_id, product_id, quantity)
VALUES (501, 88, 2);

COMMIT;

If a required step fails, the entire unit should be undone:

ROLLBACK;

The four ACID properties describe the desired behavior of reliable transactions.

Atomicity

Atomicity means that the transaction is treated as one indivisible unit from the database’s perspective: either all required effects become durable, or none do. It prevents an order from being recorded without its payment, or a payment from being recorded without the corresponding order, when those actions belong to one transaction.

Consistency

Consistency means that a committed transaction moves the database from one valid state to another valid state, preserving declared constraints and relevant business rules. Consistency is not a substitute for correct rule design. A database can consistently store an incorrect business rule if the rule was never encoded or enforced.

Isolation

Isolation concerns the extent to which concurrent transactions are prevented from interfering with one another. Stronger isolation usually reduces anomalies but can increase waiting, locking, aborts, or resource consumption. Isolation is discussed in detail below.

Durability

Durability means that after successful commit, the committed effects survive an ordinary system failure. Database systems commonly achieve this through transaction logs, stable storage, checkpoints, replication, or combinations of these mechanisms. Durability guarantees depend on configuration; a system that acknowledges commits before safely persisting required information may provide weaker protection.

A useful distinction is between transaction success and business success. A committed transaction proves that the DBMS accepted and protected the changes. It does not prove that the price was correct, the customer was authorized, or the source data was accurate.

4. Transaction Boundaries and Error Handling

A transaction should be neither unnecessarily broad nor so narrow that related operations can be separated. A transfer between accounts illustrates the boundary:

BEGIN;

UPDATE Account
SET balance = balance - 250
WHERE account_id = 10
  AND balance >= 250;

-- Application verifies that exactly one row was affected.

UPDATE Account
SET balance = balance + 250
WHERE account_id = 20;

-- Application verifies both updates.
COMMIT;

If the first update affects zero rows, the application should roll back rather than crediting the destination without debiting the source. In production, error-handling syntax varies by DBMS, but the principle is consistent: detect errors, roll back the incomplete unit, record diagnostic information, and return a clear outcome to the caller.

Autocommit mode commits each successful statement as its own transaction. It is convenient for isolated changes but unsuitable when several statements must succeed together. Developers should know whether their client library enables autocommit and should set transaction boundaries deliberately.

Savepoints allow partial rollback within a transaction:

SAVEPOINT before_optional_step;
-- perform optional operation
ROLLBACK TO SAVEPOINT before_optional_step;

Savepoints do not make independently committed work reversible and do not necessarily release all locks. Their use should therefore be guided by the DBMS and the application’s failure model.

5. Concurrency and Isolation Anomalies

Concurrency allows many users or processes to use a database efficiently. Without control, however, interleaving operations can produce incorrect results. Consider two transactions that read the same inventory quantity and then both sell the last unit. The final stock may become negative or the system may accept more sales than inventory permits.

Important anomalies include:

• Dirty read: a transaction reads data written by another transaction that has not committed. If the writer rolls back, the reader used a value that never officially existed.
• Nonrepeatable read: a transaction reads a row twice and obtains different committed values because another transaction updated it between the reads.
• Phantom read: repeating a predicate query returns a different set of rows because another transaction inserted, deleted, or altered rows that satisfy the predicate.
• Lost update: one transaction overwrites a change made by another transaction because each read an earlier version and writes without detecting the conflict.
• Write skew: two transactions read overlapping facts, each makes a locally valid change, and together they violate a multi-row rule.

Isolation Levels

Isolation levels define permitted visibility and conflict behavior. The SQL standard describes levels in terms of anomalies, but implementations differ, especially when using multiversion concurrency control (MVCC).

“Serializable” does not necessarily mean that the DBMS literally runs one transaction at a time. Locking or validation techniques may permit concurrency while ensuring that the committed outcome is equivalent to a serial order. The exact guarantee must be checked in the product documentation.

The correct isolation level depends on the invariant being protected. A dashboard may tolerate a changing view during a long report, whereas a transfer or inventory reservation generally requires stronger coordination. Raising isolation globally can harm throughput; targeted locking, atomic conditional updates, or schema constraints may solve a narrower problem more efficiently.

6. Locking and Multiversion Control

A lock is a control placed on a database object or version to coordinate access. A simplified lock model uses:

• Shared (S) locks: commonly allow multiple readers but block conflicting writers;
• Exclusive (X) locks: allow one transaction to modify a resource while blocking incompatible readers or writers.

A compatibility table is useful conceptually:

Actual systems may use intention locks, update locks, key-range locks, predicate locks, table locks, page locks, and row locks. Lock granularity matters. Fine-grained locks increase concurrency but consume more memory and management overhead; coarse-grained locks are simpler but can block unrelated work. Lock escalation may convert many row locks into a table lock.

Many modern DBMSs use MVCC. Instead of making readers wait for every writer, the system maintains row versions so that a reader can see a transactionally consistent snapshot. MVCC reduces read-write blocking but does not eliminate write conflicts, storage overhead, vacuum or version-cleanup work, or the need to choose an appropriate isolation level.

A practical concurrency pattern is an atomic conditional modification:

UPDATE Inventory
SET quantity_on_hand = quantity_on_hand - :requested_quantity
WHERE product_id = :product_id
  AND quantity_on_hand >= :requested_quantity;

The application checks the affected-row count. One affected row means the reservation succeeded; zero means insufficient stock or a missing product. This is safer than separately reading the quantity and later updating it without protection.

7. Deadlocks and Liveness

A deadlock occurs when transactions wait in a cycle. For example, transaction T1 holds a lock on row A and requests row B, while T2 holds row B and requests row A. Neither can proceed.

Database systems generally detect deadlocks and abort one transaction, allowing the other to continue. The aborted application must usually retry the complete transaction, not merely the failed statement, because earlier work was rolled back.

Prevention and reduction strategies include:

1. acquire resources in a consistent order, such as ascending account ID;
2. keep transactions short and avoid user interaction inside them;
3. access only the rows required;
4. use appropriate indexes so searches do not lock excessive ranges;
5. avoid unnecessary lock-strength escalation;
6. set sensible lock-wait timeouts;
7. implement bounded retry with backoff for transient deadlock errors.

Retries require idempotency. If a request may be submitted twice, use a unique request identifier or an idempotency key so that a successful first attempt is not duplicated by a later retry.

8. Recovery, Logging, and Failure

A DBMS must handle failures such as transaction errors, process crashes, power loss, hardware faults, and media loss. Recovery mechanisms differ, but several ideas are foundational.

Write-ahead logging (WAL) requires relevant log records to reach stable storage before the corresponding changed data pages are treated as safely persistent. The log records what changed and provides information needed to redo committed work and undo incomplete work.

A checkpoint records a recovery position and may flush selected dirty pages. It reduces the amount of log that must be examined after a crash, although it does not replace backups.

After a crash, a simplified recovery process is:

1. identify transactions that committed before failure;
2. redo committed changes not yet reflected in data files;
3. undo changes from transactions that were active or uncommitted at failure;
4. make the recovered state available according to the DBMS protocol.

A backup strategy addresses media failure, accidental deletion, corruption, and longer-term recovery. Full backups, incremental backups, transaction-log backups, replicas, and point-in-time recovery serve different purposes. Organizations should define recovery point objective (RPO), the maximum acceptable data loss measured in time, and recovery time objective (RTO), the target time to restore service. These are operational objectives, not SQL properties, and they must be tested through restoration exercises.

9. Consistency Controls in Analytical Environments

Business analytics commonly combines operational databases, staging tables, data warehouses, and data lakes. Modification safety remains important throughout the pipeline.

A reliable load may follow this pattern:

1. land source data in a controlled staging area;
2. validate schema, keys, ranges, nullability, and duplicate counts;
3. reject or quarantine invalid records;
4. transform data using documented rules;
5. merge valid records into the target within a controlled transaction where practical;
6. record batch ID, source timestamp, row counts, and error counts;
7. reconcile source and target totals;
8. publish data only after validation succeeds.

A single transaction may be inappropriate for a very large load because of log growth, lock duration, and recovery time. In that case, use carefully designed batches with restartability and reconciliation. Each batch should have a deterministic identity and an audit record so that a failed load can resume without duplicating prior work.

Slowly changing dimensions, late-arriving facts, corrections, and duplicate source events require explicit policies. For example, overwriting a customer attribute may preserve only the current value, whereas a versioned dimension preserves historical states. The correct choice depends on analytical requirements and governance obligations.

10. Detailed Applied Case: Retail Inventory Reservation

The following is an illustrative case.

Background

A retailer receives online orders while stores and warehouse systems update inventory. The Inventory table contains one row per product and location. Customers occasionally purchase the same final unit simultaneously, producing overselling and later cancellations.

Problem

The original application performed two steps:

SELECT quantity_on_hand
FROM Inventory
WHERE product_id = 88 AND location_id = 3;

If the result was at least the requested quantity, it later issued an update. Between the read and update, another transaction could reserve the same units. The process also wrote the order before confirming the inventory update.

Analysis

The design had a time-of-check/time-of-use race. It also had an unsafe transaction boundary: order creation and inventory reservation were not atomic. A simple higher isolation level might reduce some anomalies, but the business invariant should be enforced directly by an atomic conditional update and a transaction.

Approach and Solution

A revised process uses a request identifier and a transaction:

BEGIN;

-- First, reject a previously completed request ID.
-- Then attempt the reservation atomically.
UPDATE Inventory
SET quantity_on_hand = quantity_on_hand - :qty,
    version_no = version_no + 1
WHERE product_id = :product_id
  AND location_id = :location_id
  AND quantity_on_hand >= :qty;

-- Application verifies that exactly one row was affected.

INSERT INTO OrderHeader (order_id, customer_id, request_id, status)
VALUES (:order_id, :customer_id, :request_id, 'RESERVED');

INSERT INTO OrderLine (order_id, product_id, quantity)
VALUES (:order_id, :product_id, :qty);

COMMIT;

A unique constraint on request_id prevents the same client request from creating two orders. The application rolls back when the update affects zero rows or when any insert fails. The database also enforces positive quantities and valid product references.

Outcome

Concurrent reservations compete safely for the row. Only transactions that satisfy the quantity predicate can reduce stock. If two transactions request the final unit, one succeeds and the other receives a failure outcome. The transaction does not guarantee that payment, shipping, or customer intent is perfect; it guarantees that the defined reservation and order records are coordinated.

Learning Points

• Express critical invariants in the modification predicate.
• Use one transaction for logically inseparable changes.
• Check affected-row counts rather than assuming success.
• Use unique request identifiers to make retries safe.
• Test behavior under concurrency, not only in single-user trials.

11. Governance, Ethics, and Professional Practice

Data modification is an exercise of organizational power. Unauthorized updates can expose customers, alter financial records, or violate retention requirements. Role-based access, separation of duties, approval workflows, immutable audit logs, and periodic privilege reviews are therefore governance controls, not administrative luxuries.

Deletion must be evaluated against privacy law, litigation holds, financial retention rules, and legitimate analytical needs. A request to remove personal information may require deletion or anonymization, but technical deletion must be reconciled with legally required records and backup retention. Data minimization reduces the consequences of an error.

Audit records should identify the actor or service, time, affected entity, operation, reason or request ID, and before-and-after values where appropriate. Audit data itself requires access control because it may contain sensitive information.

Artificial intelligence does not remove these responsibilities. AI-generated SQL should be reviewed for missing predicates, unsafe joins, inappropriate merges, privilege escalation, and dialect-specific behavior. Automated agents should operate with restricted identities, transaction limits, approval gates for destructive actions, and extensive logging.

12. Best-Practice Checklist

Before executing a modification:

• verify the target database, schema, and environment;
• test the selection predicate with SELECT;
• use explicit column lists and parameters;
• check constraints, keys, and affected-row expectations;
• define transaction boundaries and error behavior;
• consider concurrent updates and required isolation;
• ensure backups and audit controls are adequate for the risk;
• use a rollback or restoration plan for destructive changes;
• test deadlock and retry behavior;
• reconcile results after batch or merge operations.

For production systems, code review and automated tests should include empty inputs, duplicates, nulls, boundary quantities, concurrent requests, partial failures, retries, and recovery after interruption.

Chapter Summary

INSERT, UPDATE, DELETE, and MERGE change database state and must be controlled through precise predicates, explicit column lists, constraints, authorization, and verification. Transactions group related operations into logical units and seek the ACID properties of atomicity, consistency, isolation, and durability. Isolation levels determine how concurrent transactions may observe and affect one another; dirty reads, nonrepeatable reads, phantom reads, lost updates, and write skew are important anomalies. Locks coordinate access, while MVCC provides version-based concurrency in many systems. Deadlocks arise from cyclic waiting and require consistent access ordering, short transactions, detection, and safe retry. Write-ahead logging, checkpoints, recovery procedures, backups, and tested RPO/RTO objectives protect committed work and support restoration. In analytical environments, staging, validation, batch design, reconciliation, and auditability are essential. Ethical and professional practice requires least privilege, privacy-aware retention, traceability, and careful review of automated or AI-generated SQL.

Key Terms

Transaction: A logical unit of database work that commits or rolls back as a whole.

Atomicity: The property that a transaction’s required effects all occur or none occur.

Consistency: Preservation of database constraints and business rules after a committed transaction.

Isolation: The degree to which concurrent transactions are protected from one another’s intermediate or conflicting effects.

Durability: Persistence of committed effects despite ordinary system failure.

Commit: The operation that makes a transaction’s changes permanent according to the DBMS’s durability guarantees.

Rollback: The operation that undoes uncommitted transaction changes.

Constraint: A declarative rule restricting permissible database states.

Primary key: A column or column set that uniquely identifies rows.

Foreign key: A column or column set referencing a key in another table.

MERGE: A conditional operation that can update matched rows and insert unmatched source rows.

Dirty read: Reading data written by an uncommitted transaction.

Nonrepeatable read: Obtaining different values for the same row during one transaction.

Phantom read: Observing a changed set of rows for a repeated predicate query.

Lost update: One concurrent change overwriting another without detecting the conflict.

Lock: A concurrency-control mechanism that restricts incompatible access.

MVCC: Multiversion concurrency control, which uses committed row versions to support concurrent reads and writes.

Deadlock: A cycle in which transactions wait for resources held by one another.

Write-ahead logging: A recovery method requiring log information to be persisted before dependent data pages are considered safely written.

Idempotency: The property that repeating a request produces no additional unintended effect.

Review Questions

Short Answer Questions

1. Why is omitting the WHERE clause in an UPDATE or DELETE potentially dangerous?
2. Define atomicity and give one business example.
3. What is the difference between a dirty read and a nonrepeatable read?
4. Why should an application check the number of rows affected by a conditional UPDATE?
5. What is the purpose of a database checkpoint?

Long Answer Questions

1. Explain the ACID properties and discuss how each contributes to reliable order processing.
2. Compare Read Committed, Repeatable Read, and Serializable isolation, including their principal trade-offs.
3. Explain how primary keys, foreign keys, CHECK constraints, and unique constraints work together to protect consistency.
4. Describe the causes of deadlocks and evaluate at least four strategies for reducing them.
5. Explain how write-ahead logging, checkpoints, backups, and transaction-log recovery contribute to overall recoverability.

Analytical / Application Questions

1. Two transactions both read an inventory quantity of 1 and then each attempt to sell one unit. Design a SQL modification pattern that prevents overselling and explain how the application determines success.
2. A nightly MERGE fails after updating some target rows but before inserting all new rows. Propose a transaction, staging, audit, and restart strategy that avoids an ambiguous final state.
3. An analytics team wants to run a four-hour report under Serializable isolation to guarantee a consistent result, but operational users experience extensive blocking. Analyze alternative approaches, including snapshot-based reading, reporting replicas, controlled extracts, and reconciliation.

Multiple Choice Questions with Answers

Question 1

Which practice most directly reduces the risk of updating unintended rows?

A. Omitting the WHERE clause

B. Testing the WHERE predicate with SELECT first

C. Using a larger table lock

D. Disabling constraints

Correct Answer: B. Testing the WHERE predicate with SELECT first

Question 2

Which ACID property means that committed changes survive an ordinary system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 3

What is a dirty read?

A. Reading a value written by an uncommitted transaction

B. Reading the same committed row twice with different values

C. Reading an inserted row that matches a later predicate

D. Reading a row protected by an exclusive lock

Correct Answer: A. Reading a value written by an uncommitted transaction

Question 4

What is the principal purpose of a foreign key?

A. To format decimal values

B. To enforce a relationship to a referenced key

C. To automatically create a backup

D. To guarantee serializable isolation

Correct Answer: B. To enforce a relationship to a referenced key

Question 5

Two transactions each hold a lock needed by the other. This situation is called:

A. A phantom read

B. A checkpoint

C. A deadlock

D. An upsert

Correct Answer: C. A deadlock

Question 6

Which statement best describes an atomic conditional inventory update?

A. It reads stock in one transaction and updates it much later

B. It decreases stock only when the quantity predicate is satisfied

C. It disables all foreign keys during the sale

D. It always allows negative inventory

Correct Answer: B. It decreases stock only when the quantity predicate is satisfied

Question 7

What is the usual purpose of a ROLLBACK?

A. To make uncommitted transaction changes undone

B. To create a new primary key

C. To increase lock duration permanently

D. To convert a table into a view

Correct Answer: A. To make uncommitted transaction changes undone

Question 8

Which technique is most closely associated with maintaining log records before dependent data pages are safely written?

A. Write-ahead logging

B. Logical deletion

C. Column aliasing

D. Read uncommitted isolation

Correct Answer: A. Write-ahead logging

Question 9

Why can a unique request identifier help with transaction retries?

A. It permits duplicate requests to create duplicate orders

B. It makes repeated requests distinguishable and prevents unintended duplication

C. It removes the need for a transaction

D. It guarantees that no deadlock can occur

Correct Answer: B. It makes repeated requests distinguishable and prevents unintended duplication

Question 10

Which isolation level provides the strongest general guarantee that the result is equivalent to some serial execution?

A. Read Uncommitted

B. Read Committed

C. Repeatable Read

D. Serializable

Correct Answer: D. Serializable