Benny, ----- Original Message ----- From: ""Bernhard Schmidt"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, July 07, 2003 6:33 PM Subject: innodb transaction
> ------=_NextPart_000_0144_01C344AD.CB5B11C0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > hi all > > i started working with the innodb databasel. after reading the manual i = > am not sure how i shall use transctions. say i have the following case: > > SELECT read some row > UPDATE depending on the select > COMMIT > > to make the command sequence safe, the select has to lock the table. it = > would be nice to have something like: > > START TRANSACTION > SELECT read some row > UPDATE depending on the select > COMMIT > > any row accessed between START TRANSACTION and COMMIT automatically = > locks the rows. but the manual does not exactly describe the behavior of = > the commands START TRANSACTION, BEGIN etc. It only denotes that it = > servers for ad-hoc transactions. i know that i can use the LOCK IN SHARE = > MODE with the SELECT command, but is this the only way? SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; does the trick. Note that in InnoDB everything happens inside a transaction. If you have AUTOCOMMIT=1, then every SQL statement constitutes its own transaction (unless you enclose several statements inside BEGIN ... COMMIT). http://www.innodb.com/ibman.html#InnoDB_transaction_model " A detailed description of each isolation level in InnoDB: READ UNCOMMITTED This is also called 'dirty read': non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not 'consistent' reads under this isolation level; otherwise this level works like READ COMMITTED. READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, NOT the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since 'phantom rows' have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads there is an important difference from the previous isolation level: in this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECTs within the same transaction, these SELECTs are consistent also with respect to each other. SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE. " > best regards > benny Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
