Chapter 7: Transactions
After a transaction has acquired the lock, it must continue to hold the lock until ____________. This where the name "two-phase" comes from: the first phase is when the lock is acquired, and the second phase is when all the locks are released.
(the) end of the transaction
Although retrying an aborted transaction is a simple and effective error handling mechanism, it isn't perfect: If the transaction also has side effects outside of the databases, those side effects may happen even if the transaction is ____________.
Aborted
The rate of ____________ significantly affects the overall performance of SSI. For example, a transaction that reads and writes data over a long period of time is likely to run into conflicts and abort, so SSI requires that read-write transaction be fairly short (long-running read-only transactions may be okay). (However, SSI is probably less sensitive to slow transactions than two-phase locking or serial execution.)
Aborts
For examples that check for the ____________ of rows matching some search condition and then adds a row matching the same condition, a query that adds locks to a row (e.g., SELECT FOR UPDATE) cannot be used because the query is looking to update only when there aren't any rows that match the condition, and the lock can be attached to nothing. These examples are susceptible to write skew.
Absence
Contention between transactions can be reduced with commutative ____________ operations. (e.g., increment counter)
Atomic
In general, __________ refers to something that cannot be broken down into smaller parts
Atomic
Many databases provide _____________ update operations, which remove the need to implement read-modify-write cycles in application code. These operations are usually the best solution to lost update problems.
Atomic
ACID __________ describes what happens if a client wants to make several writes, but a fault occurs after some of the writes have been processed -- for example, a process crashes, a network connection is interrupted, a disk becomes full, or some integrity constraint is violated.
Atomicity
ACID __________ guarantees that if the writes are grouped together into an atomic transaction, and the transaction cannot be competed (committed) due to a fault, then the transaction is aborted and the database must discard or undo any undo any writes it has made so far in that transaction.
Atomicity
With ACID __________, if an error occurs halfway through a sequence of writes, the transaction should be aborted, and the writes made up to that point should be discarded. In other words, the database saves you from having to worry about partial failure, by giving an all-or-nothing guarantee.
Atomicity
Without ACID __________, if an error occurs partway through making multiple changes, it's difficult to know which changes have take effect and which haven't. The application could try again, but that risks making the same change twice, leading to duplicate or incorrect data. With this guarantee, if a transaction was aborted, the application can be sure that it didn't change anything, so it can safely be retired.
Atomicity
The safety guarantees provided by transactions are often described by the well-known acronym ACID, which stands for __________.
Atomicity, Consistency, Isolation, and Durability
Systems that do not meet the ACID criteria are sometimes called BASE, which stands for __________.
Basically Available, Soft state, and Eventual consistency
To figure out whether your application needs transactions, you first need to understand exactly what __________ transactions can provide, and what costs are associated with them.
Safety guarantees
Dirty writes and lost updates are two kinds of race conditions that can occur when different transactions concurrently try to write to the _____________ objects.
Same
In the case when different transactions update the _____________ object, you get a dirty write or lost update anomaly (depending on the timing).
Same
In a database with __________ (almost everything except pure key-value stores), the indexes also need to be updated every time you change a value. These indexes are different database objects from a transaction point of view: for example, without transaction isolation, it's possible for a record to appear in one index but not another, because the update to the second index hasn't happened yet.
Secondary Indexes
(Serializable Isolation): Simple key-value data can often be partitioned very easily (and hence use the approach of partitioning + actual serial execution on a single thread [per partition] to achieve higher transaction throughput), but data with multiple __________ is likely to require a lot of cross-partition coordination.
Secondary indexes.
Literally executing transactions in ____________ order -- an approach to implementing serializable transactions: if you can make each transaction very fast to execute, and the transaction throughput is low enough to process on a single CPU core, this is a simple and effective option.
Serial
__________ of transactions can become a viable way of achieving serializable isolation within certain constraints: - Every transaction must be small and fast, because it takes only one slow transaction to stall all transaction processing. - It is limited to use cases where the active dataset can fit in memory. Rarely accessed data could potentially be moved to disk, but if it needed to be accessed in a single-threaded transaction, the system would get very slow. - Write throughput must be low enough to be handled on a single CPU core, or else transactions need to be partitioned without requiring cross-partition coordination - Cross-partition transactions are possible, but there is a hard limit to the exent to which they can be used.
Serial Execution
The classic database textbooks formalize ACID isolation as __________, which means that each transaction can pretend that it is the only transaction running on the entire database. The database ensures that when the transactions have committed, the result is the same as if they had run serially (one after another), even though in reality they may have run concurrently.
Serializability
A database with ____________ isolation must prevent phantoms
Serializable
An advantage of implementing ____________ isolation using actual serial execution is that a system designed for single-threaded execution can sometimes perform better than system supports concurrency, because it can avoid the coordination overhead of locking.
Serializable
Automatically preventing write skew requires true _____________ isolation.
Serializable
In practice, __________ isolation is rarely used, because it carries a performance penalty. Some popular databases typically offer a weaker isolation guarantee, such as snapshot isolation.
Serializable
Most databases that provide ____________ isolation today use one of three techniques: 1. Literally executing transactions in serial order. 2. Two-phase locking 3. Optimistic concurrency control techniques
Serializable
The simplest way of avoiding concurrency problems is to remove the concurrency entirely: to execute only one transaction at a time, in serial order, on a single thread. The resulting isolation is by definition ____________.
Serializable
This disadvantage of implementing ____________ isolation using actual serial execution is that the system's throughput is limited to that of single CPU core. In order to make the most of that single thread, transactions need to be structured differently from their traditional form.
Serializable
When using partitioning and actual serial execution on a single thread (for each partition), for any transaction that needs to access multiple partitions, the database must coordinate the transaction across all the partitions that it touches, to ensure __________ isolation across the whole system.
Serializable
With ____________ isolation, the database guarantees that if the transactions behave correctly when run individually, they continue to be correct when run concurrently; i.e., the database prevents all race conditions.
Serializable
With stored procedures and in-memory data (i.e., all data in the transaction fits in memory), executing all transactions on a single thread become feasible. As they don't need to wait for I/O, systems using this approach to implement __________ isolation avoid the overhead of other concurrency control mechanisms and can achieve quite good throughput on a single thread
Serializable
__________ isolation means that the database guarantees that transactions have the same effect as if they ran serially (i.e., one at a time, without any concurrency)
Serializable
____________ isolation guarantees that even though transactions may execute in parallel, the end result is the same as if they had executed one at a time, serially, without any concurrency.
Serializable
____________ isolation is usually regarded as the strongest isolation level.
Serializable
____________ (SSI) is a very promising approach to full serializability (i.e., serializable isolation) that has only a small performance penalty compared to snapshot isolation, a weaker isolation level.
Serializable Snapshot Isolation
Compared to serial execution, ____________ isolation is not limited to throughput of a single CPU core.
Serializable snapshot
Compared to two-phase locking, the big advantage of ____________ isolation is that one transaction doesn't need to block waiting for locks held by another transaction. Like under snapshot isolation, writers don't block readers, and vice versa. This design principle makes query latency much more predictable and less variable. In particular, read-only queries can run on a consistent snapshot without requiring any locks, which is very appealing for read-heavy workloads.
Serializable snapshot
For detecting writes that affect prior reads with ____________ isolation, when a transaction writes to the database, it must look in the indexes for any other transactions that have recently read the affected data. This process is similar to acquiring a write lock on the affected key range, but rather than blocking until the readers have committed, the lock acts as a tripwire: it simply notifies the transactions that the data they read may no longer be up to date. (The later ending write transaction is aborted)
Serializable snapshot
In order to prevent reads on a stale MVCC object with ____________ isolation, when the transaction wants to commit, the database checks whether any of the ignored writes (on concurrent transactions were in-progress but not committed when the transaction started) have now been committed. If so, the transaction must be aborted. Why wait until committing? At the time when the transaction makes a stale read, the database doesn't yet know whether that transaction is going to later perform a write. By avoiding unnecessary aborts, SSI preserves snapshot isolation's support for long-running reads from a consistent snapshot.
Serializable snapshot
In order to provide ____________ isolation, the database must detect situation in which a transaction may have acted on an outdated premise and abort the transaction in that case. There are two cases to consider to know if a query result might have changed: 1. Detecting reads on a stale MVCC object version (uncommitted write occurred before the read). 2. Detecting writes that affect prior reads (the write occurs after the read).
Serializable snapshot
____________ -- a type of race condition: a fairly new algorithm that avoids most of the downsides of the previous approaches. It uses an optimist approach, allowing transactions to proceed without blocking. When a transaction wants to commit, it is checked, and it is aborted if the execution was not serializable.
Serializable snapshot isolation (SSI)
The lock used in Two-phase locking is used as follows: If a transaction wants to read an object, it must first acquire the lock in ____________ mode. Several transactions are allowed to hold the lock in this mode simultaneously, but if another transaction already has an exclusive lock on the object, these transactions must wait.
Shared
Storage engines almost universally aim to provide atomicity and isolation on the level of a __________ (such as a key-value pair) on one node. Atomicity can be implemented using a log for crash recovery, and isolation can be implemented using a lock on each object (allowing only one thread to access an object at any one time).
Single object
__________ operations (such as increment and compare-and-set) are useful, as they can prevent lost updates when several clients try to write to the same object concurrently. However, they are not transactions in the usual sense of the word.
Single-object
In (PostgresSQL) MVCC-based _____________ isolation, if a transaction deletes a row, the row isn't actually deleted, but it is marked for deletion. At some later time, when it is certain that no transaction can any longer access the deleted data, a garbage collection process in the database removes any rows marked for deletion and frees this space.
Snapshot
In 2PL (Two-Phase Locking), writes block both other writers and readers and readers block other readers and writers. ____________ isolation, on the other hand, has the mantra readers never block writers, and writers never block readers. On the other hand, because 2PL provides serializability, it protects against race conditions like lost updates and write skew, which is not guaranteed with weaker isolation levels.
Snapshot
It is very hard to reason about the meaning of a long-running, read-only query if the data on which it operates is changing at the same time as the query is executing. The solution for this is to enforce _____________ isolation; when a transaction can see a consistent snapshot of the database, frozen at a particular point in time, it is much easier to understand.
Snapshot
____________ isolation avoids phantoms in read-only queries, but in read-write transactions, phantoms can lead to particularly tricky cases of write skew.
Snapshot
_____________ isolation is a useful isolation level, especially for read-only transactions. However, many databases call it by different names (e.g., serializable in Oracle, and repeatable read in MySQL)
Snapshot
_____________ isolation is the most common solution to solving problems of temporary inconsistencies (such as read skews).
Snapshot
The idea behind _____________ isolation is that each transaction reads from a consistent snapshot of the database -- that is, the transaction sees all the data that was committed in the database at the start of the transaction. Even if the data is subsequently changed by another transaction, each transaction sees only the data rom that particular point in time.
Snapshot (isolation)
Another options for preventing lost updates is to allow the read-modify-write cycles to execute in parallel, an if the transaction manager detects a lost update, abort the transaction and force it to retry its read-modify-write cycle. The advantage of this approach is that databases can perform this check efficiently in conjunction with _____________. This lost update detection is a great feature, because it doesn't require application code.
Snapshot isolation
A pro of ____________ is that modern implementations use existing general-purpose programming languages (i.e. VoltDB uses Java or Groovy).
Stored Procedures
Systems with single-threaded serial transaction processing don't allow interactive multi-statement transactions. Instead, the application must submit the entire transaction code to the database, as a ____________.
Stored procedure
A con of ____________ is that each database vendor typically has its own language for these procedures that look quite ugly and archaic. Furthermore, code running a database i difficult to manage, debug, and version control.
Stored procedures
A __________ is a way for an application to group several reads and writes into a logical unit.
Transaction
A __________ is usually understood as a mechanism for grouping multiple operations on multiple objects into one unit of execution.
Transaction
An application with simple access patterns, such as reading and writing only a single record, can probably manage without ____________.
Transactions
For complex access patterns, ____________ can hugely reduce the number of potential error cases you need to think about.
Transactions
Without ____________, various error scenarios (process crashing, network interruptions, power outages, disk full, unexpected concurrency, etc.) mean that data can become inconsistent in various ways.
Transactions
____________ are an abstraction layer that allows an application to pretend that certain concurrency problems and certain kinds of hardware and software faults don't exist.
Transactions
Although retrying an aborted transaction is a simple and effective error handling mechanism, it isn't perfect: it is only worth retrying after ____________ errors (for example due to deadlock, isolation violation, temporary network interruptions, and failover); after a permanent error (e.g., constraint violation) a retry would be pointless.
Transient
In ____________ (2PL), a lock is maintained on each object (e.g., row) in the database. The lock can either be in shared mode or in exclusive mode:
Two-phase locking
The big downside of ____________ is that transaction throughput and response times of queries are significantly worse under this approach for achieving serializable isolation than under weak isolation. This is most importantly due to the reduced concurrency.
Two-phase locking
Traditional relational databases don't limit the duration of a transaction, so when using ____________ to achieve serializable isolation, a queue may form if several transactions want to access the same object. For this reason, databases running this approach can have quite unstable latencies, and they can be very slow at high percentiles if there is contention in the workload.
Two-phase locking
With ____________, an approach to achieving serializable isolation, several transactions are allowed to concurrently read the same object as long as nobody is writing to it. But as soon anyone wants to write an object, exclusive access is required: 1. If transaction A has read an object and transaction B wants to write to that object, B must wait until A commits or aborts before it can continue. 2. If transaction A has written an object and transaction B wants to read that object, B must wait until A commits or aborts before it can continue.
Two-phase locking
____________ -- a type of race condition: for decades this has been the standard way of implementing serializability, but many applications avoid using it because of its performance characteristics.
Two-phase locking
____________ is an approach to achieving serializable isolation that doesn't perform well in general.
Two-phase locking
Things can go wrong in data systems: A client may read data that doesn't make sense because it has only partially been __________.
Updated
In a relational data model, a row in one table often has a foreign key references to a row in another table. (Similarly, in a graph-like data model, a vertex has edges to other vertices.) Multi-object transactions allow you to ensure that these references remain __________: inserting several records that refer to one another, the foreign keys have to be correct and up to date, or the data becomes nonsensical.
Valid
Concurrency bugs caused by ____________ transaction isolation have caused substantial loss of money, led to investigation by financial auditors, and caused customer data to be corrupted. Even many popular relational database systems (which are usually considered "ACID") use this isolation, so they wouldn't necessarily have prevented these bug from occurring.
Weak
____________ isolation levels (such as read committed) have good performance, but are prone to various race conditions (lost updates, write skews, phantom, etc.)
Weak
There are advantages to __________ transactional guarantees or abandoning them entirely (e.g., to achieve higher performance or higher availability).
Weakening
Serializable isolation has performance costs. It's therefore common for systems to use __________ levels of isolation, which protect against some concurrency issues but not all. These levels of isolation are much harder to understand, and they can lead to subtle bugs, but they are nevertheless used in practice.
Weaker
When using serial execution to achieve serializable isolation on the ____________, to avoid the system idling on a single long-running transaction by waiting for user input over a long checkout process that can span multiple requests, a transaction is committed within the same HTTP request -- a transaction does not span multiple requests. A new HTTP request starts a new transaction.
Web
When implementing serializable isolation, executing all transactions serially makes concurrency control much simpler, but limits the transaction throughput of the database to the speed of a single CPU core on a single machine. For applications with high __________ throughput, the single-threaded transaction processor can become a serious bottleneck
Write
Examples that follow a certain pattern are susceptible to ____________: 1. A SELECT query checks whether some requirement is satisfied by searching for rows that match some search condition (e.g., check if a username exists already) 2. Depending on the result of the first query, the application code decides how to continue (e.g., whether or not the username is available) 3. If the application decides to go ahead, it makes a write (INSERT, UPDATE, or DELETE) to the database and commits the transaction. The effect of this write changes the precondition of the decision of step 2. In other words, if you were to repeat the SELECT query from step 1 after committing the write, you would get a different result, because the write changed the set of rows matching the search condition. These steps may occur in a different order.
Write Skew
Unlike dirty writes and lost updates, a _____________ involves concurrent transactions updating different objects.
Write skew
____________ -- a type of race condition -- occurs when a transaction reads something (typically involving multiple objects), makes a decision based on the value it saw, and writes the decision to the database. However, by the time the write is made, the premise of the decision is no longer true. Only serializable isolation prevents this anomaly.
Write skew
_____________ can occur if two transactions read the same objects, and then update some of those objects (different transactions may update different objects).
Write skew
Some databases allow you to configure constraints, which are then enforced by the database (e.g., uniqueness, foreign key constraints, or restrictions on a particular value). However, most databases do not have built-in support for a constraint that involves multiple objects. If they did, this might be a solution for some examples of ____________.
Write skews
In ACID, atomicity and isolation describe what the database should do if a client makes several __________ within the same transaction.
Writes
The database will __________ a transaction if the entire transaction succeeds.
Commit
___________ bugs are hard to find by testing, because such bugs are only triggered when you get unlucky with the timing. Such timing issues might occur very rarely, and are usually difficult to reproduce.
Concurrency
Atomicity, isolation, and durability are properties of the database, whereas __________ (in the ACID sense) is a property of the application. The application may rely on the database's atomicity and isolation properties in order to achieve this guarantee.
Consistency
In the context of ACID, __________ refers to an application-specific notion of the database being in a "good state".
Consistency
It is the application's responsibility to define its transactions correctly so that they preserve ACID __________. This is not something that the database can guarantee. In general, the application defines what data is valid or invalid -- the database only stores it.
Consistency
For MVCC-based snapshot isolation: When a transaction reads from the database, transaction IDs are used to decide which objects it can see and which are invisible. By carefully defining visibility rules, the database can present a _____________ snapshot of the database to the application: 1. At the start of each transaction, the database makes a list of all the other transactions that are in progress at that time. Any writes that those transaction have made are ignored. 2. Any writes made by aborted transactions are ignored 3. Any writes made by transactions with a later transaction ID are ignored, regardless of whether those transaction have committed. 4. All other writes are visible to the application's queries These rules apply to both creation and deletion of objects
Consistent
Things can go wrong in data systems: The application may __________ at any time (including halfway through a series of operations)
Crash
Atomic operations, which are useful for solving lost update problems) are usually implemented by taking an exclusive lock on the object when it is read so that no other transaction can read it until the update has been applied. This technique is known as _____________.
Cursor stability
In 2PL, it can happen quite easily that transaction A is stuck waiting for transaction B to release its lock, and vice versa. This situation is called ____________. The database automatically detects when this happens between transactions and aborts one of them so that the others can make progress. The aborted transaction needs to retried by the application.
Deadlock
In Two-phase locking, when a transaction is aborted due to ____________ and is retried, it needs to do its work all over again. If this situation is frequent, this can mean significant wasted effort.
Deadlock
If a transaction needs to update several objects (multi-object transaction), a ____________ means that another transaction may see some of the updates but not others.
Dirty Read
____________ -- a type of race condition -- occur when one client reads another client's writes before they have been committed. The read committed isolation level and stronger levels prevent dirty reads.
Dirty Reads
Imagine a transaction has written some data to the database, but the transaction has not yet committed or aborted. Can another transaction see the uncommitted data? If yes, that is called a ____________.
Dirty read
If a transaction aborts, any writes it has made need to be rolled back. If the database allows ____________, that means a transaction may see data that is later rolled back.
Dirty reads
To satisfy read committed isolation, most databases prevent _____________ by using the following process: for every object that is written, the database remembers both the old committed value and the new value set by the transaction that currently holds the write lock. While the transaction is ongoing, any other transactions that read the object are simply given the old value. Only when the new value is committed do transactions switch over to reading the new value.
Dirty reads
Transactions running at the read committed isolation must prevent ____________. This means that any writes by a transaction only becomes visible to others when that transaction commits (and then all of its writes become visible at once).
Dirty reads
Consider when two transactions concurrently try to update the same object in a database. If the earlier write is part of a transaction that has not yet committed and the later write (from another transaction) can overwrite the uncommitted value of the first transaction; this is known as a __________. Transactions running at the read committed isolation level must prevent this situation, usually by delaying the second write until the first write's transaction has committed or aborted.
Dirty write
____________ -- a type of race condition -- occur when one client overwrites data that another client has written, but not yet committed. Almost all transaction implementations prevent dirty writes.
Dirty writes
ACID __________ is the promise that once a transaction has committed successfully, any data it has written will not be forgotten, even if there is a hardware fault or the database crashes.
Durability
Historically, __________ meant writing to an archive tape (hard drive). Then it was understood as writing to a disk or SSD. More recently, it has been adapted to mean replication. In practice, there is no one technique that can provide absolute guarantees. There are only various risk-reduction techniques, including writing to disk, replicating to remote machines, and backups -- and they can and should be used together.
Durability
In a replicated database, __________ may mean that the data has been successfully copied to some number of nodes. In order to provide a durability guarantee, a database must wait until these replications are complete before reporting a transaction as successfully committed.
Durability
In a single-node database, __________ typically means that the data has been written to nonvolatile storage such as a hard drive or SSD. It usually also involves a write-ahead log or similar, which allows recovery in the event that the data structures on disk are corrupted. In order to provide a durability guarantee, a database must wait until these writes are complete before reporting a transaction as successfully committed.
Durability
Perfect __________ does not exist: if all your hard disks and all your backups are destroyed at the same time, there's obviously nothing your database can do to save you.
Durability
The lock used in Two-phase locking is used as follows: If a transaction first reads and then writes the object, it may upgrade its shared lock to an ____________ lock.
Exclusive
The lock used in Two-phase locking is used as follows: If a transaction wants to write to an object, it must first acquire the lock in ____________ mode. No other transaction may hold the lock at the same time (in any mode), so if there is any existing lock on the object, the transaction must wait.
Exclusive
Things can go wrong in data systems: The database software or hardware may __________ at any time (including in the middle of a write operation).
Fail
Although retrying an aborted transaction is a simple and effective error handling mechanism, it isn't perfect: If the transaction actually succeeded, but the network failed while the server tried to a acknowledge the successful commit to the client (so the client thinks it __________), then retrying the transaction causes it to be performed twice.
Failed
Although retrying an aborted transaction is a simple and effective error handling mechanism, it isn't perfect: if the client process ____________ while retrying, any data it was trying to write to the database is lost.
Fails
Transactions were create namely to simply the programming model for application accessing a database. By sing transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead; we call these safety __________.
Guarantees
Unfortunately, predicate locks do not perform well: if there are many lock by active transactions, checking for matching locks becomes time-consuming. For that reason, most databases with 2PL actually implement ____________ locking (also known as next-key locking), which is a simplified approximation of predicate locking.
Index-range
____________ locking is a approximation of predicate locking that matches a greater set of objects (typically covered by an index) than covered by any predicate. In other words, an approximation of the search condition is attached to one of the indexes.
Index-range
For working with _____________ with MVCC-based snapshot isolation, one option is to have the index simply point to all versions of an object and require an index query to filter out any object versions that are not visible to the current transaction. When garbage collection removes old object versions that are no longer visible to any transaction, the corresponding index entries can also be removed.
Indexes
Things can go wrong in data systems: __________ in the network can unexpectedly cut off the application from the database, or one database node from another.
Interruptions
The idea of ACID consistency is that you have certain statements about you data, called __________, that must always be true -- for example, in an accounting system, credits and debits across all accounts must always be balanced. If a transaction starts with a database that is valid according to these conditions, and any writes during the transaction preserve the validity, then you can be sure that these conditions are always satisfied.
Invariants
(Unlike in multi-threading programming) In the context of ACID, atomicity is not about concurrency. That is covered under __________.
Isolation
Databases have long tried to hide concurrency issues from application developers by providing transaction __________, which should in theory let you pretend that no concurrency is happening.
Isolation
With ACID __________, concurrently running transactions shouldn't interfere with each other. For example, if one transaction makes several writes, then another transaction should see either all or none of those writes, but not some subset.
Isolation
__________ in the sense of ACID means that concurrently executing transactions are isolated from each other: they cannot step on each other's toes.
Isolation
When implementing serializable isolation using actual serial execution, if you find a way of partitioning your dataset so that each transaction only needs to read and write data within a single transaction, then each partition can have its own transaction processing thread running independently from the others. This way, you can give each CPU core its own partition, which allows your transaction throughput to scale __________ with the number of CPU cores.
Lineraly
Another option for preventing lost updates is for applications to explicitly _____________ objects that are going to be updated: if any other transactions tried to concurrently read the same object, it is forced to wait until the first read-modify-cycle has completed. This works, but to get it write, you need to carefully think about your application code -- it is easy to introduce race conditions.
Lock
If you can't use a serializable isolation level to solve write skews, the second best option in some cases is probably to explicitly ____________ the rows that the transaction depends on (e.g., using FOR UPDATE with SQL)
Lock
Incrementing a counter or updating an account balance is an example of writes that can suffer from the _____________ problem
Lost update
The _____________ problem can occur if an application reads some value from the database, modifies it, and writes back the modified value (a read-modify-write cycle). If two transactions do this concurrently, one of the modifications can be lost, because the write does not include the first modification. (We sometimes say that the later write clobber the earlier write).
Lost update
____________ -- a type of race condition -- occur when two clients concurrently perform a read-modify-write cycle. One overwrites the other's write without incorporating its changes, so data is lost. Some implementations of snapshot isolation prevent this anomaly automatically, while others require a manual lock (SELECT FOR UPDATE).
Lost updates
Storage engines that support snapshot isolation typically use _____________ for their read committed isolation level as well; a typical approach is that read committed isolation uses a separate snapshot for each query, while snapshot isolation uses the same snapshot for an entire transaction.
MVCC
____________ is an approach for preventing write skews with Snapshot isolation. It takes a phantom and turns it into a lock conflict on a concrete set of rows that exist in the database (i.e., instead of a query that checks for an absence of rows). However, this approach can be hard and error-prone to implement correctly. A serializable isolation level is much preferable in most cases.
Materializing conflicts
ACID isolation and atomicity guarantees typically assume that you want to modify several objects (rows, documents, records) at once (i.e., several writes in a single transaction). Such __________ transactions are often needed if several pieces of data need to be kept in sync.
Multi-object
In a document data model, the fields that need to be updated together are often within the same document, which is treated as a single object -- no multi-object transactions are needed when updating a single document. However, document databases lacking join functionality also encourage denormalization. When denormalized information needs to be updated, you need to update several documents in one go. __________ transactions are very useful in this situation to prevent denormalized data from going out of sync.
Multi-object
Many distributed datastores have abandoned __________ transactions because they are difficult to implement across partitions, and they can get in the way in some scenarios. (However, there is nothing that fundamentally prevents transactions in a distributed database)
Multi-object
To implement snapshot isolation, the database must potentially keep several different committed versions of an object, because various in-progress transactions may need to see the state of the database at different points in time. This technique is known as _____________ (MVCC)
Multi-version concurrency control
For preventing write skews, atomic single-object operations don't help, as _____________ objects are involved.
Multiple
For MVCC-based snapshot isolation: an _____________ is visible if both of the following conditions are true: - At the time when the reader's transaction started, the transaction that created the object had already committed. - The object is not marked for deletion, or if it is, the transaction that requested deletion had not yet committed at the time when the reader's transaction started.
Object
Conceptually, all the reads and writes in in a transaction are executed as __________.
One operation
If there is enough spare capacity and if contention between transactions is not too high, ____________ concurrency control techniques tend to perform better than pessimistic ones.
Optimistic
Serializable snapshot isolation is an ____________ concurrency control technique. Meaning that instead of blocking if something potentially dangerous happens, transactions continue anyway, in the hope that every thing will turn out all right. When a transaction wants to commit, the database checks whether anything bad happened (i.e., whether isolation was violated. If so, the transaction is aborted and has to be retried. Only transactions that executed serializable are allowed to commit.
Optimistic
____________ concurrency control (used by Serializable snapshot isolation) performs badly if there is high contention (many transactions trying to access the same objects), as this leads to high proportion of transaction needing to abort. The additional transaction load from retired transactions can make performance worse.
Optimistic
Things can go wrong in data systems: Several clients may write to the database at the same time, __________ each other's changes.
Overwriting
With transactions, error handling becomes much simpler for an application, because it doesn't need to worry about __________.
Partial failures
Two-phase locking and serial execution are so-called ____________ concurrency control mechanisms: they based on the principle that if anything might possible go wrong (as indicated by a lock held by another transaction in 2PL), it's better to wait until the situation is safe again before doing anything. It is like mutual exclusion, which is used to protect data structures in multi-threaded programming.
Pessimistic
A ____________ is where a write in one transaction changes the result of a search query in another transaction.
Phantom
____________ -- a type of race condition -- occur when a transaction reads objects that match some search condition. Then, another client makes a write that affects the results of that search. Snapshot isolation prevents straightforward phantom reads, but phantoms in the context of write skew require special treatment, such as index-range locks.
Phantom reads
A ____________ lock restricts access as follows: 1. If transaction A wants to read objects matching some condition, like in that SELECT query, it must acquire a shared-mode lock on the conditions of the query. If another transaction B currently has an exclusive lock on any object matching those conditions, A must wait until B releases its lock before it is allowed to make its query. 2. If transaction A wants to insert, update, or delete any object, it must first check whether either the old or the new value matches any existing predicate lock. If there is a matching predicate lock held by transaction B, then A must wait until B has committed or aborted before it can continue.
Predicate
A ____________ lock works similarly to the shared/exclusive lock but belongs to all objects that match some search condition (instead of a single object).
Predicate
If two-phase locking includes ____________ locks, the database prevents all forms of write skew and other race conditions, and so its isolation becomes serializable
Predicate
For write skews (race condition) in snapshot isolation, the transaction is taking an action based on a ____________ (a fact that was true at the beginning of the transaction, e.g., "There are currently two doctors on call"). Later, when the transaction wants to commit, the original data may have changed -- the original condition may no longer be true.
Premise
Most databases are accessed by several clients at the same time. If these clients are accessing the same database records, you can run into concurrency problems, also known as __________
Race conditions
Things can go wrong in data systems: __________ between clients can cause surprising bugs.
Race conditions
___________ only come into play when one transaction reads data that is concurrently modified by another transaction, or when two transactions try to simultaneously modify the same data.
Race conditions
Read Skew is considered acceptable under _____________ isolation.
Read committed
________ transaction isolation does not prevent the race condition between two counter increments (e.g., copy-modify-update), where transaction A increments and writes the counter (42 + 1 = 43) after transaction B reads the old value (42) then B increments and writes (42 + 1 = 43) after A commits (counter is 43 but should be 44 since it was incremented twice). In this case, the second write (by B) happens after the first transaction (A) has committed , so it's not a dirty write. It's still incorrect tho.
Read committed
____________ is the most basic level of transaction isolation. It makes two guarantees: 1. When reading from the database, you will only see data that has been committed (no dirty reads) 2. When writing to the database, you will only overwrite data that has been committed (no dirty writes)
Read committed
For preventing dirty reads, the approach of requiring ____________ does not work well in practice, because one long-running write transaction can force many read-only transactions to wait until the long-running transaction has completed.
Read locks
____________ (non-repeatable reads) -- a type of race condition -- occurs when a client sees different parts of the database at different points in time. This issue is most commonly prevented with snapshot isolation, which allows a transaction to read from a consistent snapshot at one point in time. It is usually implemented with multi-version concurrency control (MVCC).
Read skew
_____________ (a.k.a nonrepeatable read) happens when a transaction A reads a value set by another transaction B that started after it but committed before it (i.e., A starts -> B starts -> B commits X -> A reads X).
Read skew
Some situations cannot tolerate _____________ : Taking a backup requires making a copy of the entire database, which may take hours on a large database. During the time that the backup process is running, writes will continue to be made to the database. Thus, you could end up with some parts of the backup containing an older version of the data, and other parts containing a newer version. If you need to restore from such a backup, the inconsistencies (such as disappearing money) become permanent.
Read skews
Some situations cannot tolerate _____________ : you may want to run a query that scans over large parts of the database (i.e., for an analytic query or for an integrity check). These queries are likely to return nonsensical results if they observe parts of the database at different points in time.
Read skews
Read committed and snapshot isolation levels are primarily about the guarantees of what a _____________ transaction can see in the presence of concurrent writes.
Read-only
Although retrying an aborted transaction is a simple and effective error handling mechanism, it isn't perfect: if the error is due to overload, retrying the transaction will make the problem worse, not better. To avoid such feedback cycles, you can limit the number of _________, use exponential backoff, and handle overload-related errors differently from other errors (if possible).
Retries
The database will __________ (or abort) a transaction if it fails.
Rollback
Most commonly, databases prevent dirty writes by using __________ locks: when a transaction wants to modify a particular object (row or document), it must first acquire a lock on that object. It must then hold the lock until the transaction is committed or aborted.
Row-level
Databases that don't provide transactions sometime offer an atomic _____________ operation: the purpose of this operation is to avoid lost updates by allowing an update to happen only if the value has not changed since you last read it. However, it's important to check whether this operation is safe with your database before relying on it; for instance, check if the database allows the WHERE clause to read from an old snapshot.
compare-and-set
In most ACID databases, if the database is in danger of violating its guarantee of atomicity, isolation, or durability, it would rather abandon the transaction entirely than allow it to remain half-finished. However, datastores with __________ replication work much more on a "best effort" basis, which could be summarized as "the database will do as much as it can, and if it runs into an error, it won't undo something it has already done" -- so it's the application's responsibility to recover from errors.
leaderless
A common approach to prevent _____________ in multi-leader and leaderless replicated databases is to allow concurrent writes to create several conflicting versions of a value (also known as siblings), and to use application code or special data structures to resolve and merge these versions after the fact.
lost updates
In replicated databases, preventing _____________ takes on another dimension: since they have copies of the data on multiple nodes, and the data can potentially be modified concurrently on different nodes, some additional steps need to be taken.
lost updates
Options for preventing _____________ include: atomic operations, explicit locking, automatically detection, compare-and-set operations, and conflict resolution in replicated databases.
lost updates
A key idea with ____________ locks is that it applies even to objects that do not yet exist in the database, but which might be added in the future (phantoms).
predicate
Object-relational mapping frameworks make it easy to accidentally write code that performs unsafe _____________ cycles instead of using atomic operations provided by the database.
read-modify-write
Databases with multi-leader or leaderless replication usually cannot guarantee that there is a _____________ copy of the data. Thus, techniques based on locks or compare-and-set do not apply in this context.
single up-to-date
A key principle of _____________ isolation is readers never block writers, and writers never block readers. This allows a database to handle long-running read queries on a consistent snapshot at the same time as processing writes normally, without any lock contention between the two.
snapshot
Like read committed isolation, implementations of _____________ typically use write locks to prevent dirty writes, which means that a transaction that makes a write can block the progress of another transaction that writes to the same object. However, reads do not require any locks.
snapshot isolation
