Hi!
>Hi,I'm a beginner with MySql. Could anyone give me an example how transaction
>isolation works on InnoDB tables under Win2000? (If you have any examples
>I'll be happy). Thanks in advance.
>Adi
Below is pasted an example from the InnoDB
manual at http://www.innodb.com/ibman.html
....
When you issue a consistent read, that is, an ordinary SELECT statement,
InnoDB will give your transaction a timepoint according to which your query
sees the database. Thus, if transaction B deletes a row and commits after
your timepoint was assigned, then you will not see the row deleted.
Similarly with inserts and updates.
You can advance your timepoint by committing your transaction and then doing
another SELECT.
This is called multiversioned concurrency control.
User A User B
set autocommit=0; set autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set;
COMMIT;
SELECT * FROM t;
----------------------
| 1 | 2 |
----------------------
Thus user A sees the row inserted by B only when B has committed the insert,
and A has committed his own transaction so that the timepoint is advanced
past the the commit of B.
If you want to see the 'freshest' state of the database, you should use a
locking read:
SELECT * FROM t LOCK IN SHARE MODE;
.....
Regards,
Heikki
Innobase Oy
---------------------------------------------------------------------
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
