Updating a View with Changing Rows

Introduction

In this article, we will delve into the world of database views and explore how updating a view affects rows that are being deleted or modified in the underlying table. We will discuss the potential for blockage when running update statements on views, and provide examples to illustrate the concepts.

What is a View?

A view is a virtual representation of a subset of data from one or more tables. It can be thought of as a read-only copy of a table, where the actual data is stored in the underlying table(s). When you query a view, the database engine executes the query on the underlying tables and returns the results.

Database Views: A Quick Background

In MySQL, SQL Server, PostgreSQL, SQLite, and Oracle, views are supported. The syntax for creating a view can vary slightly between these databases, but the basic concept remains the same.

-- Create a new view (syntax may vary depending on the database)
CREATE VIEW OpenJobs AS
SELECT JobID, JobTitle, Open/Closed
FROM Jobs
WHERE Open = 'Open';

How Updates Affect Views

When you update a table directly, the changes are written to the underlying data. However, when you update a view, the database engine must determine how to apply the changes.

If the view is based on a SELECT statement (as in our OpenJobs example), the engine will simply return the updated results for the affected rows. This means that if you delete or modify a row in the underlying table, it may not be reflected immediately in the view until the next time the view is refreshed.

However, things become more complicated when you update the view directly using an UPDATE statement. In this case, the database engine must decide how to apply the changes to the underlying table.

Potential for Blockage

The question remains: if a row is deleted or modified in the view before the update statement can affect it, does this cause blockage? What happens when multiple updates are applied concurrently?

To answer these questions, let’s examine the database engine’s behavior more closely.

What Happens When You Update a View?

When you run an UPDATE statement on a view, the database engine will execute the query on the underlying table(s) and apply the changes. However, if another update statement is applied concurrently to the same table or view, things can get complicated.

Case 1: Concurrency with Deletes

Let’s consider the scenario where you delete a row in the Jobs table while another process is running an UPDATE statement on the OpenJobs view. The question remains: will the update statement block until the deletion has been committed or rolled back?

In most databases, when you run an UPDATE statement concurrently with a DELETE statement, the engine will apply the updates first and then undo any changes that were made by the delete operation. This is known as “optimistic concurrency control”.

For example, in MySQL, if you run:

UPDATE OpenJobs SET Open/Closed = 'Closed' WHERE JobTitle = 'Sales';
DELETE FROM Jobs WHERE JobID = 3;

The database engine will apply the updates first and then delete the row.

However, this raises an interesting question: what happens when the deletion is not committed? If the deletion is rolled back or abandoned, does the update statement still need to be rolled back as well?

Case 2: Concurrency with Updates

Now let’s consider a scenario where multiple updates are applied concurrently to the same table or view. In this case, things can get even more complicated.

For example, if you run:

UPDATE OpenJobs SET Open/Closed = 'Closed' WHERE JobTitle = 'Sales';
UPDATE Jobs SET Status = 'Closed' WHERE JobID = 3;

The database engine must now decide how to apply the updates in a way that minimizes contention and ensures consistency.

In most databases, this is achieved through a combination of locking and transaction management. When you run an UPDATE statement concurrently with another update statement, the engine will typically lock both tables or views until one of the updates has been committed or rolled back.

Conclusion

Updating a view with changing rows can be complex, especially when concurrency comes into play. While most databases provide mechanisms to mitigate contention and ensure consistency, it’s essential to understand how these mechanisms work under the hood.

In general, it’s recommended to run updates against tables rather than views whenever possible. However, in certain scenarios, using views can provide a convenient abstraction layer or improve performance.

By understanding how database views work and how updates affect them, you can write more efficient and effective code that takes advantage of the strengths of both tables and views.

Additional Considerations

There are several additional considerations to keep in mind when working with database views:

View Performance: Views can be slower than directly querying underlying tables because they require the engine to execute a query on multiple tables. However, some databases provide mechanisms for indexing and caching views that can improve performance.

**View Security**: Views can be used to create virtual tables that are restricted to specific users or roles. This can be an effective way to control access to sensitive data without having to implement complex permissions models.

View Auditing: Many databases provide auditing mechanisms for views, which allow you to track changes made to the underlying table.

In our next article, we will explore more advanced topics in database design and optimization.

Last modified on 2023-08-04