Tony’s Oracle Tips

August 24, 2011

ACID and TPC-C

Filed under: ACID — tonyhasler @ 7:53 pm

I saw Tom Kyte yesterday and he stated that in his opinion the ACID issue had no bearing on TPC-C tests.  Well, I beg to differ.  The current specification can be viewed in full here but here are the three key parts of the specification.

First from section 3.4 on isolation requirements:

P1 (“Dirty Read”): Database transaction T1 modifies a data element. Database transaction T2 then reads that data element before T1 performs a COMMIT. If T1 were to perform a ROLLBACK, T2 will have read a value that was never committed and that may thus be considered to have never existed.

A little ater on it says that dirty reads are not allowed, as you might expect.

Isolation Level P0 P1 P2 P3
0 Not Possible Possible Possible Possible
1 Not Possible Not Possible Possible Possible
2 Not Possible Not Possible Not Possible Possible
3 Not Possible Not Possible Not Possible Not Possible

skipping ahead a bit…

2. {Ti, Tn}1 ≤ i ≤ 4 P0, P1, P2 Ti Level 2 isolation for New-Order, Payment, Delivery, and Order-Status transactions relative to any arbitrary transaction.

Now, in section 3.5.2 we have the committed property definition:

A transaction is considered committed when the transaction manager component of the system has either written the log or written the data for the committed updates associated with the transaction to a durable medium.

Therefore, Oracle violates the isolation property because data not yet committed has been made visible to other transactions and “Dirty Reads” are not prevented.  Specifically, the read-only order status transaction may see changes made by the other read-write transactions listed above.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: