Heri,
----- Original Message -----
From: ""H. Steuer"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, December 03, 2002 11:49 AM
Subject: Re: C API problems with InnoDB
> Hello Mark,
>
> thanks for your answer. In fact the mysql shell where I update the row is
> using AUTOCOMMIT=1.
> Even after I issue a COMMIT manually the changes are not seen by the
> application.
> What I dont understand is that the program doing a SELECT has to issue an
> COMMIT to have all data available.
> Maybe I cannot see through the transaction model at all, but as far as I
can
> see all other connections should have
> the data available after the session that changes data issues a COMMIT
> command. But in fact that doesnt happen
> here. For what reason ever.
under the default isolation level, which is REPEATABLE READ, it is logical
that consistent reads within one transaction read the same snapshot.
Consider, for example, a case where you have two bank accounts A1: 1000
euros and A2: 1000 euros. Suppose you move 100 euros from A1 to A2. If you
in another transaction read first A1 and then read A2, the sum should be
2000 euros. This is guaranteed under REPEATABLE READ because both reads read
the same snapshot. If they would read different snapshots, the sum could be
2100 euros.
> Any other ideas ?
Upgrade to MySQL-4.0.5a and set the transaction isolation level READ
COMMITTED:
"
Consistent reads behave like in Oracle: each consistent read, even within
the same transaction, sets and reads its own fresh snapshot.
"
"
In terms of the SQL-1992 transaction isolation levels, the InnoDB default is
REPEATABLE READ. Starting from version 4.0.5, InnoDB offers all 4 different
transaction isolation levels described by the SQL-1992 standard. You can set
the default isolation level for all connections in the [mysqld] section of
my.cnf:
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
A user can change the isolation level of a single session or all new
incoming connections with the
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
"
"
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.
"
> regards,
> Heri
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
sql query
---------------------------------------------------------------------
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
