Saturday, June 13, 2009

transaction isolation level (%%language)

Basic context behind the jargon:
* shared data -- in memory or disk
* at least 2 threads. Isolation between threads.

0) concurrent, interleaved writes -- no transaction. no isolation. 2 threads can each update half of a row's data, corrupting it. I don't think any DB allows this.

1) dirty read or "read uncommitted"(RU) -- Easy. when a reader thread/session gets to see another thread's uncommitted data change, that's one count of read-uncommitted offense. We say this system[4] "allows read-uncommitted". This is the lowest isolation level, but higher than (0) above.

2) read committed (RC) [3] -- If system doesn't expose uncommitted data change, then read-uncommitted will never occur. If system is configured this way, then /each thread only reads committed changes/. However, each thread doesn't get repeatable-read-by-id guarantee.

3) repeatable read (RR) by id -- Your thread reads an item by id and writes something /based/ on the read. If you can reliably read, read, read for a long time ... to get the *same* data before your write, then you are lucky [2] -- no other session is allowed to touch that object. You get Repeatable Read on that object[1] guarantee by the system. This system is operating in the RR mode.

Basically, system locks all rows of your first read and reserves them for you -- pessimistic locking. [5] Once you read a row, it's reserved for you. You are an emperor -- once you cast your eyes on a girl, you have the right on her.

4) phantom read -- However, if you read with a range-select like "where price > 0", then your first read can reserve 500 rows and a repeated read can turn up 501 rows. This is a count of Phantom Read. I feel this query is not repeatable. It's not a read-by-id, so RR is not relevant.

Solution for phantom read is -- a range-lock rather than a row or page lock. Some say "serialize all threads that access a given range".

Say Transaction1 has just finished a read with "from table1 where age > 22", uncommitted. Can system allow Transaction2 to start, one that doesn't mention this table? If system lets Transaction2 start, Transaction2 may lock up another table. Since Transaction1 locks up table1, there's risk of deadlock. It's safest to serialize all transactions.

[1] but not necessarily on that query
[2] luckier than the threads in a RC system
[3] RC is the default in most databases.
[4] Now the "system" could be a table or a database or a multi-threading app. There's absolutely(?) no control, coordination, discipline or "isolation" in this RU mode. Isolation is the I in ACID.
[5] Now we know every SELECT is implicitly in a new transaction. In RR mode, it locks up all the rows involved until commit. This is more strict than the default RC mode.

No comments:

Total Pageviews

my favorite topics (labels)

_fuxi (302) _misLabel (13) _orig? (3) _rm (2) _vague (2) clarified (58) cpp (39) cpp_const (22) cpp_real (76) cpp/java/c# (101) cppBig4 (54) cppSmartPtr (35) cppSTL (33) cppSTL_itr (27) cppSTL_real (26) cppTemplate (28) creditMkt (14) db (65) db_sybase (43) deepUnder (31) dotnet (20) ECN (27) econ/bank` (36) fin/sys_misc (43) finGreek (34) finReal (45) finRisk (30) finTechDesign (46) finTechMisc (32) finVol (66) FixedIncom (28) fMath (7) fMathOption (33) fMathStoch (67) forex (39) gr8IV_Q (46) GTD_skill (15) GUI_event (30) inMemDB (42) intuit_math (41) intuitFinance (57) javaMisc (68) javaServerSide (13) lambda/delegate (22) marketData (28) math (10) mathStat (55) memIssue (8) memMgmt (66) metaProgram` (6) OO_Design (84) original_content (749) polymorphic/vptr (40) productive (21) ptr/ref (48) py (28) reflect (8) script`/unix (82) socket/stream (39) subquery/join (30) subvert (13) swing/wpf (9) sysProgram` (16) thread (164) thread_CAS (15) thread_cpp (28) Thread* (22) timeSaver (80) transactional (23) tune (24) tuneDB (40) tuneLatency (30) z_ajax (9) z_algoDataStruct (41) z_arch (26) z_arch_job (27) z_automateTest (17) z_autoTrad` (19) z_bestPractice (39) z_bold (83) z_bondMath (35) z_book (18) z_boost (19) z_byRef^Val (32) z_c#GUI (43) z_c#misc (80) z_cast/convert (28) z_container (67) z_cStr/arr (39) z_Favorite* (8) z_FIX (15) z_forex (48) z_fwd_Deal (18) z_gz=job (33) z_gzBig20 (13) z_gzMgr (13) z_gzPain (20) z_gzThreat (19) z_hib (19) z_IDE (52) z_ikm (5) z_IR_misc (36) z_IRS (26) z_javaWeb (28) z_jdbc (10) z_jobFinTech (46) z_jobHunt (20) z_jobRealXp (10) z_jobStrength (15) z_jobUS^asia (27) z_letter (42) z_linq (10) z_memberHid` (11) z_MOM (54) z_nestedClass (5) z_oq (24) z_PCP (12) z_pearl (1) z_php (20) z_prodSupport (7) z_py (31) z_quant (14) z_regex (8) z_rv (38) z_skillist (48) z_slic`Problem (6) z_SOA (14) z_spring (25) z_src_code (8) z_swingMisc (50) z_swingTable (26) z_unpublish (2) z_VBA/Excel (8) z_windoz (17) z_wpfCommand (9)

About Me

New York (Time Square), NY, United States
http://www.linkedin.com/in/tanbin