Transaction Processing: Concepts and Techniques (The Morgan Kaufmann Series in Data Management Systems)

Author: Jim Gray, Andreas Reuter
4.4
All Stack Overflow 11

Transaction Processing: Concepts and Techniques (The Morgan Kaufmann Series in Data Management Systems)

4.4

Review Date:

Comments

by lbruck   2017-10-11
I like "Architecture of a Database System" by Stonebraker, Hamilton, and Hellerstein (https://www.amazon.com/Transaction-Processing-Concepts-Techn...) for the storage-side of things. Granted these are a little old (especially G&R) so extra thought must be given for modern hardware (memory, CPU performance, processor counts, network, disks, etc) as well as distributed processing, replication, and consensus.
by anonymous   2017-08-20

Gray wrote a book titled "Transaction Processing: Concepts and Techniques" - http://www.amazon.com/Transaction-Processing-Concepts-Techniques-Management/dp/1558601902 - which covers a great deal of what you would need to build your own database.

One starting point for file systems would be http://www.amazon.com/The-Design-UNIX-Operating-System/dp/0132017997 - but I suspect you would have to chase down individual papers or Linux source code to keep up with the variety of different file systems created since then.

by anonymous   2017-08-20

If the locking is not done properly it certainly is possible to get this type race condition, and the default locking mode (read committed) does allow it. In this mode, the reads only place a shared lock on the record, so they can both see 0, increment it and write 1 out to the database.

In order to avoid this race condition, you need to set an exclusive lock on the read operation. 'Serializable' and 'Repeatable Read' concurrency modes will do this, and for an operation on a single row they are pretty much equivalent.

To make it completely atomic you have to:

  • Set an appropriate transaction isolation level such as Serializable. Normally you can do this from a client library or explicilty in SQL.
  • Begin the transaction
  • Read the data
  • Update it
  • Commit the transaction.

You can also force an exclusive lock on the read with a HOLDLOCK (T-SQL) or equivalent hint, depending on your SQL dialect.

A single update query will do this atomically but you can't split the operation (perhaps to read the value and return it to the client) without ensuring that the reads take out an exclusive lock. You will need to get the value out atomically in order to implement a sequence, so the update by itself is probably not quite all you need. Even with the atomic update, you still have a race condition to read the value after the update. The read will still have to take place within a transaction (storing what it got in a variable) and issue an exclusive lock during the read.

Note that to do this without creating a hot spot your database needs to have proper support for autonomous (nested) transactions within a stored procedure. Note that sometimes 'nested' is used to refer to chaining transactions or save points, so the term can be a bit confusing. I've edited this to refer to autonomous transactions.

Without autonomous transactions your locks are inherited by the parent transaction, which can roll back the whole lot. This means they will be held until the parent transaction commits, which can turn your sequence into a hot spot that serialises all transactions using that sequence. Anything else trying to use the sequence will block until the whole parent transaction commits.

IIRC Oracle supports autonomous transactions but DB/2 didn't until fairly recently and SQL Server doesn't. Off the top of my head I don't know whether InnoDB supports them, but Grey and Reuter go on at some length about how difficult they are to implement. In practice I'd guess it's quite likely that it might not. YMMV.

by anonymous   2017-08-20

The thing with the Paxos algorithm is that it's fairly recent (published as a journal article in 1998 according to http://en.wikipedia.org/wiki/Paxos_(computer_science)) and classic books like Readings in Database Systems or Transaction Processing cover the other topics you care about, but actually pre-date Paxos!