Deutsch

This blog post is based on a talk that I have given in the Ruby on Rails User Group.

Who knows Transactions?

Who knows really well what transactions are?

What is a Transaction?

Just common sense what transactions mean:

• We open an transaction (implicitely or explicitely)
• We perform a certain number of operations (for example SQL-statements)
• We decide in the end to either „roll-back“ or „commit“
• Roll-back restores the state before the transaction
• Commit accepts the changes for good

That’s it?

• So, that’s it, we are done…
• Just scrap these stupid confusing advanced stuff, do it simple…

Simple world

• In the simple world, only one DB-user at a time exists
• Then we are really almost done..

Complicated World

• In a complicated world we have parallel activities taking place:
• Multiple threads, processes or external database clients simultanously using the database

Complications of Complication

• What happens now?
• When things get parallel, everybody is good in doing that, but it usually does not work.
• So DB-developers are really good, because databases do actually sometimes work quite well, even under load…
• What happens with incomplete transactions?
• What happens with complete transactions during one complicated read query?

Naïve Requirement

• Other clients should see an transaction either completely or not at all.
• Is that enough?
• Think of an auditorium for a boring lecture: 90 min, people continuously leaving the room (so boring), but at the end it is still quite full.
• How many people are in the room?
• Counting is hard, because more people leave during the count.

Snapshot too old

• Oracle has solved that problem of counting the students and similar ones…
• The SELECT-statement gets for its duration a consistent data content, based on what was in the DB when the statement started.
• Transactions that have been committed successfully after starting the SELECT are not taken into account
• This is called snapshot. Long SELECTs that work on data that is actively changed sometimes fail with „Snapshot too old“

Now the hardcore stuff

• Read phenomena
• Transaction Isolation Levels
• ACID

Read Phenomena: Dirty Read

• „Dirty read“ refers to uncommitted data being available for reading by other transactions. Sounds cool, right? Intuitive?
• Interesting question: can you read your own uncommitted data within the running transaction?

Read Phenomena: Non-repeatable Read

• „Non-repeatable read“ refers to a reading operations on the same piece of data that happen during the same transaction (or SELECT statement in the Oracle world) and give different results.
• With Oracle this should not happen, we get snapshot too old instead.

Read Phenomena: Phantom reads

• Data records are the same
• But the set of records may change

Isolation Level: Read Uncommited

• Allows „dirty Read“
• I do not know of any case where this is done
• You can configure it and may get „better“ than that

Isolation Level: Read Committed

• Read data only from completed transaction
• Non-repeatable read is possible
• Default for many databases, like PostgreSQL

Isolation Level: Repeatable Reads

• During one transaction data does not change
• Phantom reads are still possible
• Default for many databases, for example Oracle

Isolation Level: Serializable

• Pretend all transactions are queued and only one at a time is happening
• No phantom reads possible

Real databases

• They have some default
• But may always be „better“ than that
• So mixtures are possible
• For example Oracle has „read-only“ as isolation level…

Locking vs. Multiversion

• Two mechanisms:
• Locking of parts of the data
• Multiversion: multiple copies (maybe copy on write?) around, for each transaction… Have fun when merging. 😉

Different DB products

• Different DB products use the freedom to interpret the standard quite liberally
• Oracle is the standard, they only pretend to care about SQL-standard…
• Make a wise decision which DB to support with your software in the beginning
• Remember: some commercial DB vendors might not have any customers, they just have hostages

ACID

ACIDs in chemistry are substances with a pH-value < 7.
Here acronym for

• Atomicity
• Consistency
• Isolation
• Durability

Some Links

• http://juliandontcheff.wordpress.com/2011/02/23/isolation-levels-in-database-transactions-in-oracle-and-db2/
• http://www.dba-oracle.com/t_isloation_level_transaction_serialization.htm
• http://www.dba-oracle.com/t_oracle_dirty_reads.htm
• http://www.dba-oracle.com/t_oracle_isolation_level.htm
• https://en.wikipedia.org/wiki/Snapshot_isolation
• http://www.adp-gmbh.ch/ora/misc/isolation_level.html
• http://docs.oracle.com/cd/B14117_01/server.101/b10743/consist.htm
• http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html
• http://www.cs.umb.edu/~poneil/iso.pdf
• https://en.wikipedia.org/wiki/Concurrency_%28computer_science%29
• https://en.wikipedia.org/wiki/Multiversion_concurrency_control
• http://en.wikipedia.org/wiki/ACID
• https://en.wikipedia.org/wiki/Isolation_%28database_systems%29
• http://msdn.microsoft.com/de-ch/library/ms173763.aspx
• http://de.wikipedia.org/wiki/Isolation_%28Datenbank%29
• http://aboutsqlserver.com/2011/04/28/locking-in-microsoft-sql-server-part-2-locks-and-transaction-isolation-levels/
• http://www.mssqltips.com/sqlservertip/2977/demonstrations-of-transaction-isolation-levels-in-sql-server/
• https://technet.microsoft.com/en-us/library/ms378149%28v=sql.110%29.aspx
• https://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx
• http://msdn.microsoft.com/de-ch/library/ms173763.aspx
• http://stackoverflow.com/questions/4946986/how-to-obtain-current-isolation-level-on-db2
• http://db2portal.blogspot.ch/2009/06/know-your-isolation-levels.html
• http://www.dbatodba.com/db2/how-to-do/what-are-the-db2-isolation-levels
• http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html?lang=de
• https://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2isolationlevel/
• http://publib.boulder.ibm.com/infocenter/db2e/v9r1/index.jsp?topic=%2Fcom.ibm.db2e.doc%2Fdb2e_iso_levels.html
• http://www.dba-oracle.com/t_oracle_isolation_level.htm
• https://docs.oracle.com/cd/B14117_01/server.101/b10743/consist.htm
• https://brodowsky.it-sky.net/2014/01/03/snapshot-too-old-behandlung-langlaufender-selects/