Übungen Transaktionen mit InnoDB Tabellen in Mysql
Zwei Tabellen anlegen und mit Daten füllen:
CREATE TABLE alpha_bank (
id int(16) unsigned NOT NULL default '0',
value decimal(6,2) NOT NULL default '0.00',
user_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY user_id (user_id)
) TYPE=InnoDB;
INSERT INTO alpha_bank VALUES (1010, '1410.43', 11);
INSERT INTO alpha_bank VALUES (1011, '4310.12', 12);
INSERT INTO alpha_bank VALUES (1012, '112.12', 13);
INSERT INTO alpha_bank VALUES (1013, '-12.12', 14);
CREATE TABLE omega_bank (
id int(16) unsigned NOT NULL default '0',
value decimal(6,2) NOT NULL default '0.00',
user_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY user_id (user_id)
) TYPE=InnoDB;
INSERT INTO omega_bank VALUES (21010, '878,12', 101);
INSERT INTO omega_bank VALUES (21011, '112,3', 102);
Select und Update als Transaktion durchführen:
SET AUTOCOMMIT=0;
Begin;
select * from alpha_bank;
SELECT @A:=value FROM alpha_bank WHERE user_id=14;
UPDATE omega_bank SET value=@A WHERE user_id=101;
commit;
Was passiert im Fehlerfall?
SET AUTOCOMMIT=0;
Begin;
select * from alpha_bank;
SELECT @A:=value FROM alpha_bank WHERE user_id=14;
UPDATE omega_bank SET value=99 WHERE user_id=101;
UPDATE omega_bank SET user_id=102 where value=99 ;
commit;
|
Druckerversion [valid html 4.01]
[english]
