[MySQL] InnoDB: transaction isolation level

R

raven

Well-known member
20 April 2006
5.038
540
Hi,

icebreaker und ich schaffen es auch mit vereinten Kräften nicht, das transaction isolation level zu verstehen - deswegen müsst ihr jetzt helfen :)

https://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html

Es geht dabei um InnoDB-Transaktionen, die verhindern, dass race conditions auftreten.

Ich möchte gerne wissen, was genau das Isolation Level serializable bewirkt, was es für Unterschiede zwischen den einzelnen Leveln gibt. icebreaker hat auch ein Buch dazu, da widersprechen die Aussagen sich anscheinend:

indem sie an das ende eines select-kommandos die schlüsselwörter lock in share mode anfügen, erreichen sie, dass mit der ausführung des kommandos solange gewartet wird, bis alle bereits begonnen transaktionen abgeschlossen sind.
Zum Vergrößern anklicken....
Durch einen shared lock gesperrte datensätze können weiterhin von alles clients gelesen werden, und zwar selbst dann, wenn andere clients ebenfalls select ... lock in share mode verwenden. jeder versuch eines clients, derartige datensätze zu verändern, führt aber dazu, dass der client blockiert ist, bis sie ihre transaktion beenden.
Zum Vergrößern anklicken....
Sollte da tatsächlich noch jemand durchblicken - nur her damit :)

Danke,
raven
 
jo, sind 2 teile aus meinem mysql-buch - irgendwie wiedersprechen die sich, oder wir sind dumm^^

im grunde suchen wir nach einem isolations-level, dass datensätze die selected wurden (innerhalb einer transaktion) gesperrt werden. also ein sperren auf zeilen-ebene.

Serializable soll ja lock in shared mode nutzen, nur was das nun heisst, wir sind auf den kopf gefallen :biggrin:
 
ne lese sperre für datensätze die sich inner transaktion befinden mit mysql... hrm das gibts meiner meinung nach nicht. Aber mal ne kurze erklärung was das lock level serializable macht... der datensatz wird wärend der transaktion zum schreiben für andere gespert, jedoch können die anderen den datensatz lesen in dem zustand wie er vor der transaktion war. Alle änderungen die während der transaktion passieren sind nicht sichtbar und werden erst mit dem abschluss dieser sichtbar. hoffe das ist verständlich genug...

*edit* sry war falsch... hab grade nochmal nachgeschaut, hatte schon beim schreiben irgendwie das gefühl das ich da was verhaue. das was ich erklärt hab ist nen repeatable read isolation. und serialize ist das was ich gedacht hab was es unter mysql nicht gibt... mit der isolationsstufe serialize wird selbst ein lese sperre gesetzt. also es sorgt dafür dass wirklich alle transaktionen die in irgendner art abhängig sind von einnander (datensatz bezogen), hintereinader abgearbeitet werden.
 
Zuletzt bearbeitet:
Also in meinen Augen widerspricht sich da nix. So wie ich das verstehe kann der Client die datensätze zwar lesen und somit evtl. ausgeben aber Veränderungen werden unterbunden.
Das lock scheint sich nur auf Veränderungen zu beziehen.
Durch einen shared lock gesperrte datensätze können weiterhin von alles clients gelesen werden, und zwar selbst dann, wenn andere clients ebenfalls select ... lock in share mode verwenden. jeder versuch eines clients, derartige datensätze zu verändern, führt aber dazu, dass der client blockiert ist, bis sie ihre transaktion beenden.
Zum Vergrößern anklicken....
indem sie an das ende eines select-kommandos die schlüsselwörter lock in share mode anfügen, erreichen sie, dass mit der ausführung des kommandos solange gewartet wird, bis alle bereits begonnen transaktionen abgeschlossen sind.
Zum Vergrößern anklicken....

Unter dem Ausdruck " ausführen eines Kommandos" naja da kann man sich jetzt streiten ob lesen dazu gehöhrt. Oder hab ich was übersehen?
 
Zero hats im Prinzip schon gesagt, ich wills nur nochmal verdeutlichen:

SERIALIZE => SELECT .. LOCK IN SHARE MODE

Beispiel: Ich will alle User auslesen, ein anderer hat die selbe Idee und will noch den Namen von wem aendern.

Code: 
[color=green]start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
+----+-------+
| id | user  |
+----+-------+
|  1 | bart  |
|  2 | raven |
|  3 | ice   |
|  4 | zero  |
+----+-------+
4 rows in set (0.00 sec)[/color]
[COLOR=Red]mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+----+-------+
| id | user  |
+----+-------+
|  1 | bart  |
|  2 | raven |
|  3 | ice   |
|  4 | zero  |
+----+-------+
4 rows in set (0.00 sec)[/COLOR]
[COLOR=Green] mysql> update users set user='barty' where user='bart';
*haeng*
[/COLOR]

Man sieht, beide koennen die selben Daten lesen, jedoch beim Versuch was zu aendern nachdem eine andere Transaction ebenfalls diese Daten gelesen hat und nicht beendet ist, wird geblockt bis die andere fertig ist - in diesem fall die rote.

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. A shared mode lock prevents others from updating or deleting the row we have read. Also, if the latest data belongs to a yet uncommitted transaction of another client connection, we wait until that transaction commits.
Zum Vergrößern anklicken....

Um aber ein exklusives Lock auf diese Rows zu bekommen, muss mans anders machen.

Beispiel: Ein Spiel an einem Slot beginnt, man liest die Groeße des Gewinntopfes und den Jackpot ein. Race-Condition-Gefahr: 2 Gewinne die insg. > |Gewinntopf| sind oder 2x Jackpot Gewinne.

Loesung: SELECT ... FOR UPDATE

Code: 
[color=red]mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from game for update;
+------------+---------+
| gewinntopf | jackpot |
+------------+---------+
|        100 |     500 |
+------------+---------+
1 row in set (0.00 sec)
[/color][color=green]
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from game for update;[/color][color=red]

mysql> update game set gewinntopf = gewinntopf + 100;             
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.05 sec)
[/color][color=green]+------------+---------+
| gewinntopf | jackpot |
+------------+---------+
|        200 |     500 |
+------------+---------+
1 row in set (26.07 sec)[/color]

Zu den Isolation Levels:
Bei REPEATABLE READ/READ COMMITED lockt ein SELECT by default keine Rows die er liest, sie koennen die ganze Zeit veraendert werden. Bei REPEATABLE READ erhaelst du jedoch - selbst wenn die Zeilen sich in der Zwischenzeit geaendert haben - bei einem erneuten SELECT auf die selben Rows dein anfaengliches Ergebnis, d.h. er erzeugt einen exklusiven Snapshot fuer dich. READ COMMITED liest hingegen immer die neusten, commit'eten Sachen.
READ UNCOMMITTED ist alles ganz egal, der nimmt was ihm vor die Flinte kommt, auch es noch nichtmal commited ist. (Client A aendert in einer Transaktion nach SELECT .. FOR UPDATE einen Wert, commited noch nicht, Client B mit Isolevel R UC sieht schon den neuen Wert)

hth
 
bartman schrieb:
Zero hats im Prinzip schon gesagt, ich wills nur nochmal verdeutlichen:

SERIALIZE => SELECT .. LOCK IN SHARE MODE

Beispiel: Ich will alle User auslesen, ein anderer hat die selbe Idee und will noch den Namen von wem aendern.

Code: 
[color=green]start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
+----+-------+
| id | user  |
+----+-------+
|  1 | bart  |
|  2 | raven |
|  3 | ice   |
|  4 | zero  |
+----+-------+
4 rows in set (0.00 sec)[/color]
[COLOR=Red]mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+----+-------+
| id | user  |
+----+-------+
|  1 | bart  |
|  2 | raven |
|  3 | ice   |
|  4 | zero  |
+----+-------+
4 rows in set (0.00 sec)[/COLOR]
[COLOR=Green] mysql> update users set user='barty' where user='bart';
*haeng*
[/COLOR]

Man sieht, beide koennen die selben Daten lesen, jedoch beim Versuch was zu aendern nachdem eine andere Transaction ebenfalls diese Daten gelesen hat und nicht beendet ist, wird geblockt bis die andere fertig ist - in diesem fall die rote.
Zum Vergrößern anklicken....

das ist aber falsch... das hab ich anfangs auch so gedacht. das ist aber nur
"nur" repteable read... serialize geht noch nen stück weiter und blockiert wirklich alles was mit den datensätzen zu tun hat. da würde deine 2te transaktion schon bei SELECT hängen bleiben bis die 1te transaktion abgeschlossen ist und nicht beim update.
 
bartman schrieb:
Eben nicht, ich habs ja getestet ;)

Serializable ist ja nur repeated read und select per default .. lock in shared mode.

und lt. Doku:
Zum Vergrößern anklicken....

hrm... :ugly: mach mich nicht schwach... ich hab gestern abend extra nochmal in meine aufzeichnungen geschaut. und es ist so wie es da steht... zum testen:


PHP: 
CREATE TABLE `blub` (
  `name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Daten für Tabelle `blub`
-- 

INSERT INTO `blub` VALUES ('nicole');
INSERT INTO `blub` VALUES ('cindy');
INSERT INTO `blub` VALUES ('kati');
INSERT INTO `blub` VALUES ('sunny');
INSERT INTO `blub` VALUES ('marie');
INSERT INTO `blub` VALUES ('jana');


Transaktion 1:
PHP: 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM blub;
+--------+
| name   |
+--------+
| nicole |
| cindy  |
| kati   |
| sunny  |
| marie  |
| jana   |
+--------+
6 rows in set (0.02 sec)

mysql> UPDATE blub SET name = 'nicole!' WHERE name = 'nicole';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Transaktion 2 (während transaktion1 noch nicht abgeschlosse ist)
PHP: 
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from blub;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
 
Naja, es kommt auf den Zeitpunkt des SELECTs an, wenn ich Transaction 1: SELECT, T2 SELECT mache gehts, wenn ich T1 SELECT, T1 UPDATE, T2 SELECT mache, gehts logischerweise nicht.

Wann fuehrst dus aus? Ich mach gern Screenshots von meiner Konsole, bei mir gehts ;)
 
bartman schrieb:
Naja, es kommt auf den Zeitpunkt des SELECTs an, wenn ich Transaction 1: SELECT, T2 SELECT mache gehts, wenn ich T1 SELECT, T1 UPDATE, T2 SELECT mache, gehts logischerweise nicht.

Wann fuehrst dus aus? Ich mach gern Screenshots von meiner Konsole, bei mir gehts ;)
Zum Vergrößern anklicken....

wenn ich das update weg lasse gehts bei mir natürlich auch. habs so wies da steht gemacht... erst transaktion 1 und dann transaktion 2.
 
Dann ist ja alles klar :D Race-Conditions kann man damit aber nur bedingt verhindern, da ist SELECT .. FOR UPDATE schon besser geeignet
 
klar, aber es ging eben darum AUCH den select gelockt hinzubekommen, denn sonst wäre die daten-konsistenz in unserem per icq besprochenen beispiel nicht gegeben
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben