Title: 네이버 메일
 |
|
|
박찬민 (naracom2002) 님께 보내신 메일 <InnoDB and SET TRANSACTION ISOLATION LEVEL, etc...>
이 다음과 같은 이유로 전송 실패했습니다.
|
| -------------------------------------------- |
|
|
수신자의 메일 보관 용량이 가득차 있습니다. 나중에 다시 시도하십시오.
|
|
| -------------------------------------------- |
|
--- Begin Message ---
Okay, I'm trying to make sense of several things here (so that I can
clearly document them), so bear with me.
First of all, there was some syntax added to MySQL for setting
transaction isolation levels:
http://www.mysql.com/doc/S/E/SET_TRANSACTION.html
around the time that Gemini/MySQL work first started happening, I
think.
Since InnoDB uses MVCC and row-level locking for transactions, I'm
wondering how that syntax interacts with InnoDB. Does it have any
effect? And what isolation levels does it support?
Here's what I think I know.
I'm reading about InnoDB's transaction model:
http://www.mysql.com/doc/I/n/InnoDB_transaction_model.html
and using some PostgreSQL docs for reference:
http://www.postgresql.org/idocs/index.php?transaction-iso.html
The first case is READ-UNCOMMITTED.
InnoDB's doesn't have the capability as far as I can tell. Is that
true? No dirty reads?
(PostgreSQL doesn't support READ-UNCOMMITTED.)
Next comes READ-COMMITTED.
http://www.mysql.com/doc/I/n/InnoDB_consistent_read.html
This is InnoDB's default, with non-locking reads and all that good
stuff. Easy. :-)
(PostgreSQL, of course, supports READ-COMMITTED.)
Then comes REPEATABLE-READ.
http://www.mysql.com/doc/I/n/InnoDB_locking_reads.html
InnoDB handles this via SELECT ... LOCK [IN SHARE MODE|FOR UPDATE].
Or that's what it would appear. However, I'm a bit confused by the
information on how IN SHARE MODE differs from FOR UPDATE.
On IN SHARE MODE, the docs say:
Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read. If
the latest data belongs to a yet uncommitted transaction of
another user, we will wait until that transaction commits. A
shared mode lock prevents others from updating or deleting the row
we have read.
Which makes sense, I think. We get the *latest available* data,
meaning the most recently committed transaction's data. But if the
data is part of an open transaction, then we wait for it. Once we
have the data, we get a shared lock so that nobody can change the data
until we're done. Right? Just want to be positive I have this right
(and document it correctly).
On FOR UPDATE, the docs say:
A SELECT ... FOR UPDATE will read the latest available data
setting exclusive locks on each row it reads. Thus it sets the
same locks a searched SQL UPDATE would set on the rows.
This one also reads the *latest available data* but it DOES NOT say if
it will wait for an open transaction that involves the data like IN
SHARE MODE does. Are the docs correct, or is this an omission?
Asked another way, is FOR UPDATE a superset of IN SHARE MODE? Or is
it different in a subtle way?
(PostgreSQL doesn't support REPEATABLE-READ.)
Finally, we have the SERIALIZABLE isolation level.
The description of how InnoDB solves the phantom read problem (which
is the whole point of the SERIALIZABLE isolation level):
http://www.mysql.com/doc/I/n/InnoDB_Next-key_locking.html
leads me to believe that is is ALWAYS in effect. If true, that means
that what I thought was REPEATABLE-READ in InnoDB is actually
SERIALIZABLE. Is that correct?
(PostgreSQL supports SERIALIZABLE.)
In Summary
- PostgreSQL does READ-COMMITTED and SERIALIZABLE.
- InnoDB *appears* to do the same (and no more). Is that correct,
or have I missed something important?
- Gemini supports all four isolation levels.
And the meta-question stated again:
Does the SET TRANSACTION ISOLATION LEVEL have any effect on any
table hander other than Gemini? The manual really doesn't say
(that I can find).
I could construct tests for each of these, but what are the odds that
I'd get them all right? :-)
Thanks for any clarification and/or confirmation on this stuff.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 24 days, processed 539,920,742 queries (259/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail <[EMAIL PROTECTED]>
--- End Message ---
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
