- Transactions
A transaction is a mechanism for grouping multiple operations on multiple objects into one unit of execution.
- Transactions provide guarantees about the behavior of data that are fundamental to the old SQL style of operation.
- Transactions were the initial casualty of the NoSQL movement, though they are starting to make a bit of a comeback.
- Not all applications need transactions. Not all applications want transactions. And not all transactions are the same.
BASE: Explanation of BASE terminology
Basically available indicates that the system does guarantee availability, in terms of the CAP theorem.
Soft state indicates that the state of the system may change over time, even without input. This is because of the eventual consistency model.
Eventual consistency indicates that the system will become consistent over time, given that the system doesn't receive input during that time.
- Consistent in replication means evantural consistent and read your own write consistent
- Consistent in CAP means linearizability, when there is one client successfully write, after that, other client's read must see the value just be writtern
- Consistent in ACID means database is correct between transection, like money transfer between the two
Isolation Level
(image from: https://blog.acolyer.org/2016/02/24/a-critique-of-ansi-sql-isolation-levels/)
Isolation levels table
(image from: https://blog.acolyer.org/2016/02/24/a-critique-of-ansi-sql-isolation-levels/)
RAED UNCOMMITED：使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）
READ COMMITED：只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）
REPEATABLE READ ：多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）
SERIALIZABLE：InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题
脏写: 写入和提交之间，又别写入了别的数据.
脏读：一个事务还未提交，另外一个事务访问此事务修改的数据，并使用，读取了事务中间状态数据。
幻读：一个事务读取2次，得到的记录条数不一致，由于2次读取之间另外一个事务对数据进行了增删。 (insert & delete)
不可重复读：一个事务读取同一条记录2次，得到的结果不一致，由于在2次读取之间另外一个事务对此行数据进行了修改。(update)
更新丢失: 其含义为T1要更新x的数据，其首先读取x的值，然后再该值上加1再写回数据库。但是在读取x后，T2写入了新的x并成功提交，而T1还是在老的x值的基础上加1。这样，T2的更新对于T1而言就像被丢弃了一样
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).
Foreign key reference update/Second level index
Nosql update several document togehter
The strongest possible isolation guarantee is serializable isolation: transactions that run concurrently on the same data are guaranteed to perform the same as they would were they to run serially. However serializable isolation is costly. Systems skimp on it by offering weaker forms of isolation. As a result, race conditions and failure modes abound. Concurrent failures are really, really hard to debug, because they require lucky timings in your system to occur.
-
The weakest isolation guarantee is read committed.
When reading from the database, you will only see data that has been committed (no dirty reads).
When writing to the database, you will only overwrite data that has been committed (no dirty writes). -
This isolation level prevents dirty reads (reads of data that is in-flight) and dirty writes (writes over data that is in-flight).
-
Lack of dirty read safety would allow you to read values that then get rolled back. Lack of dirty write safety would allow you to write values that read to something else in-flight (so e.g. the wrong person could get an invoice for a product that they didn't actually get to buy).
-
Read committed does not prevent the race condition between two counter increments.
Hold a row-level lock on the record you are writing to. You could do the same with a read lock. However, there is a lower-impact way. Hold the old value in memory, and issue that value in response to reads, until the transaction is finalized. If a user performs a multi-object write transaction that they believe to be atomic (say, transferring money between two accounts), then performs a read in between the transaction, what they see may seem anomalous (say, one account was deducted but the other wasn't credited).
Snapshot isolation could address issue of read committed. Reads that occur in the middle of a transaction read their data from the version of the data (the snapshot) that preceded the start of the transaction. This makes it so that multi-object operations look atomic to the end user (assuming they succeed).
Using write locks and extended read value-holding (sometimes called "multiversion"). A key principle of snapshot isolation is readers never block writers, and writers never block readers.
- MVCC
-
- xmin - which defines the transaction id that inserted the record
- xmax - which defines the transaction id that deleted the row
* When you insert data, you insert the row with xmin equal to current transaction id and xmax set to null;
* When you delete the data, you find visible row that should be deleted, and set its xmax to the current transaction id
* When you update the data, for each updated row you first perform “delete” and then “insert”.
Concurrent transactions that encapsulate read-modify-write operations will behave poorly on collision. A simple example is a counter that gets updated twice, but only goes up by one. The earlier write operation is said to be lost.
Ways to address this problem that live in the wild:
- Atomic update operation (e.g. UPDATE keyword).
- Transaction-wide write locking. Expensive!
- Automatically detecting lost updates at the database level, and bubbling this back up to the application.
- Atomic compare-and-set (e.g. UPDATE ... SET ... WHERE foo = 'expected_old_value').
- Delayed application-based conflict resolution. Last resort, and only truly necessary for multi-master architectures.
As with lost updates, two transactions perform a read-modify-write, but now they modify two different objects based on the value they read.
Example in the book: two doctors concurrently withdraw from being on-call, when business logic dictates that at least one must always be on call. This occurs across multiple objects, so atomic operations do not help.
ALICE BOB
┌─ BEGIN TRANSACTION ┌─ BEGIN TRANSACTION
│ │
├─ currently_on_call = ( ├─ currently_on_call = (
│ select count(*) from doctors │ select count(*) from doctors
│ where on_call = true │ where on_call = true
│ and shift_id = 1234 │ and shift_id = 1234
│ ) │ )
│ // now currently_on_call = 2 │ // now currently_on_call = 2
│ │
├─ if (currently_on_call 2) { │
│ update doctors │
│ set on_call = false │
│ where name = 'Alice' │
│ and shift_id = 1234 ├─ if (currently_on_call >= 2) {
│ } │ update doctors
│ │ set on_call = false
└─ COMMIT TRANSACTION │ where name = 'Bob'
│ and shift_id = 1234
│ }
│
└─ COMMIT TRANSACTION
Since database is using snapshot isolation, both checks return 2. Both transactions commit, and now no doctor is on call. The requirement of having at least one doctor has been violated.
Write skew can occur if two transactions read the same objects, and then update some of those objects. You get a dirty write or lost update anomaly.
Ways to prevent write skew are a bit more restricted:
- Automatic detection at the snapshot isolation level and without serializability would require making consistency checks on every write, where is the number of concurrent write-carrying transactions in flight. This is way too high a performance penalty.
- Only transaction-wide record locking works. So you have to make this transaction explicitly serialized, using e.g. a FOR UPDATE keyword.
- Materializing conflicts
- You can theoretically insert a lock on a phantom record, and then stop the second transaction by noting the presence of the lock. This is known as materializing conflicts. This is ugly because it messes with the application data model, however. Has limited support. If this issue cannot be mitigated some other way, just give up and go serialized.
BEGIN TRANSACTION;
SELECT * FROM doctors
WHERE on_call = true
AND shift_id = 1234 FOR UPDATE;
UPDATE doctors
SET on_call = false
WHERE name = 'Alice'
AND shift_id = 1234;
COMMIT;- The most literal way is to run transactions on a single CPU in a single thread. This is actual serialization.
- This only became a real possible recently, with the speed-ups of CPUs and the increasing size of RAM. The bottleneck is obviously really low here. But it's looking tenable. Systems that use literal serialization are of a post-2007 vintage. However, this requires writing application code in a very different way.
- This method mplemeted in Redis(transactions-redis)
| Name | Details | Pros | Cons | Comments |
|---|---|---|---|---|
| Two-phase locking | * Transactions that read acquire shared-mode locks on touched records. * Transactions that write acquire, and transactions that want to write after reading update to, exclusive locks on touched records. * Transactions hold the highest grade locks they have for as long as the transaction is in play. |
* Reads do not block reads or writes, but writes block everything.(Compre snapshot isolation, reads do not block reads or writes and writes do not block reads) | * Because so many locks occur, it's much easier than in snapshot isolation to arrive at a deadlock. Deadlocks occur when a transaction holds a lock on a record that another transaction needs, and that record holds a lock that the other transaction needs. The database has to automatically fail one of the transactions, roll it back, and prompt the application for a retry. * It has very bad performance implications. Long-blocking writes are a problem. Deadlocks are a disaster. Generally 2PL architectures have very bad performance at high percentiles; this is a main reason why "want-to-be sleek" systems like Amazon have moved away from them. |
|
| Serializable snapshot isolation | * based on snapshot isolation, but adds an additional algorithmic layer that makes it serialized and isolated. | * Beats 2PL for certain workloads especially read-heavy workloads * better performance when there is low record competition and when overall load is low |
Poorer performance when lock competition is high and overall load is high. |
- 2 Phase Lock
- SSI (Postgis Serializable)
- SSI example from book
事务42先提交，并且成功了。当事务43提交时，发现事务42已经提交了与自己相冲突的写入，所以必须中止事务43
- How to avoid dead lock in 2PL(a separate thread checking)
-
How 2PL addresses phantom skew(implementation detail)
You can evade phantom skew by using predicate locks. These lock on all data in the database that matches a certain condition, even data that doesn't exist yet. Predicate locks are expensive because they involve a lot of compare operations, one for each concurrent write. In practice most databases use index-range locks instead, which simplify the predicate to an index of values of some kind instead of a complex condition. This lock covers a strict superset of the records covered by the predicate lock, so it causes more queries to have to wait for lock releases. But, it spares CPU cycles, which is currently worth it. -
SSI is an optimistic concurrency control technique. Two-phase locking is a pessimistic concurrency control technique. SSI works by allowing potentially conflicting operations to go through, then making sure that nothing bad happens; 2PL works by preventing potentially conflicting operations from occurring at all.
-
SSI detects, at commit time (e.g. at the end of the transaction), whether or not any of the operations (reads or writes) that the transaction is performing are based on outdated premises (values that have changed since the beginning of the transaction). If not, the transaction goes through. If yes, then the transaction fails and a retry is prompted.