[mysql] Stored Procedures en table locks?

[Uhmmie]

Kan iemand mij uitleggen hoe dit nu precies zit.

Stel ik heb een stored procedure met een opbouw als (even snel uit mijn hoofd getypt aangezien ik mijn daadwerkelijke queries niet bij de hand heb)..

BEGIN
INSERT INTO test(a,b,c,d) SELECT (a,b,c,1) FROM test WHERE (a > 10 or b > 10) and d = 0;
hier maak ik een kopie van de records die ik ga wijzigingen (voor history).

Vervolgens update ik de originele records:
update test set a = a+1 WHERE a > 10 and d = 0;
update test set b = b+1 WHERE b > 10 and d = 0;

Tenslotte voeg ik in mijn stored procedure nog 1 nieuw record toe:
INSERT INTO test(a,b,c) VALUES(1,1,'iets');
END;

Op zich werkt het hele verhaal perfect. Echter nu stop ik even een "SELECT sleep(10); tussen de 2 update statements.

Vervolgens open ik 2 mysql clients in 1 roep ik de stored procedure aan en vervolgens roep ik tijdens de sleep in de 2e console nogmaals dezelfde stored procedure aan. Op dit moment gaat het helemaal mis. Aangezien je in Mysql geen tables direct mag locken binnen een stored procedure heb ik geprobeerd om de stored procedure te beginnen met START TRANSACTION; en af te sluiten met COMMIT; echter lost dit het probleem helaas niet op.

Wat ik wil is dat op het moment dat een stored procedure aangeroepen wordt er niks anders op de tabel mag uitgevoerd worden tot de stored procedure geheel is afgerond. Maar alles wat dus op dat moment staat te wachten moet dus uitgevoerd worden op de tabel zoals hij is NADAT de stored procedure is afgerond, dus ook bijvoorbeeld het insert record na de sleep, maar voor de commit.

Ik hoop dat mijn vraag zo eenbeetje duidelijk is en iemand mij uit kan leggen hoe ik dit voor elkaar kan krijgen.

[cariolive23]

Waarom je twee updates op dezelfde records zou doen, is mij een raadsel, maar het kán.

Voordat je de stored procedure aanroept, kun je een table lock zetten. Alleen jouw connectie kan dan gebruik maken van de tabel, het lijkt mij dat dit ook voor de stored procedure geldt.

Overigens wel bijzonder dat je geen lock in je stored procedure mag leggen, dit is juist iets waarvoor een stored procedure is gemaakt: Het afwerken van een complete procedure. Nu moet je alsnog een deel van de procedure buiten de stored procedure houden... Raar geval dat MySQL.

Ps. Gebruik je wel overal innoDB? Anders hebben START TRANSACTION en COMMIT weinig toegevoegde waarde, niet meer dan wat ruis op de lijn.

[marco_balk]

Wellicht vind je hier een oplossing:
http://www.jasonclawson.c...edure-performance-tricks/

Ze maken hier gebruik van tijdelijke tabellen waar ze hun wijzigingen op doen...

[Uhmmie]

De queries in mijn genoemende voorbeeld zijn een stuk simpeler dan dat ik echt uit wil voeren, maar voor het probleem maakt het geen verschil. Daarnaast zijn de updates ook niet altijd op de zelfde records (vooral de randwaarden maken het verschil). Eventueel zou dit wel naar 1 query te schrijven zijn, maar dan krijg je 1 brei van cases en dat maakt het geheel een stuk minder leesbaar.

Als ik de sleep ergens tussen de updates en de inserts zou plaatsen gaat het verder alsnog mis.

Een tijdelijke tabel zou verder ook niet werken aangezien de 2e aanroep (tijdens de sleep) weer van invloed is op alle records (dus ook die uit de laatste insert uit de procedure die op dat moment in de sleep zit).. Vandaar dat het geheel dus eigenlijk gewoon echt zou moeten wachten tot de eerste procedure klaar is.

Misschien dan toch maar de locks buiten de procedure.. Maar persoonlijk ben ik niet echt blij met die oplossing. Dus als iemand nog tips heeft.

[edit] Probleem lijkt voor nu opgelost. Ik kan wel gewoon een SELECT GET_LOCK( 'naam', 'timeout'); binnen een SP gebruiken (ok niet 100% ideaal maar iig voor mijn Proof of Concept is het tijdelijk goed genoeg).

[ Voor 10% gewijzigd door Uhmmie op 30-03-2010 22:49 ]

[Uhmmie]

Die serverwide get_lock functie bleek niet helemaal ideal te zijn. Dus ik ben toch maar weer even terug gestapt naar andere mogelijkheden. Echter loop ik nu tegen het volgende probleem aan (heb even makkelijkere tabellen gemaakt waarin zich exact hetzelfde probleem voor doet):

code:

DELIMITER ;
DROP TABLE IF EXISTS website;
CREATE TABLE IF NOT EXISTS website (
  sort int(11) NOT NULL, 
  item varchar(32) NOT NULL,
  created timestamp NOT NULL default CURRENT_TIMESTAMP,
  closed timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (sort, created, closed)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER //
DROP PROCEDURE IF EXISTS web_before;//
CREATE PROCEDURE web_before(IN in_item VARCHAR(32), IN into_pos INT) BEGIN
  START TRANSACTION;
  INSERT INTO website(sort, item, created, closed) SELECT sort, item, created, CURRENT_TIMESTAMP FROM website WHERE sort >= into_pos AND closed = '0000-00-00 00:00:00'; //history records aanmaken.
  UPDATE website SET sort = sort+1 WHERE sort >= into_pos AND closed = '0000-00-00 00:00:00'; // ruimte creeeren.
  SELECT SLEEP(10); //fictief de SP langer laten duren.
  INSERT INTO website VALUES(into_pos, in_item, CURRENT_TIMESTAMP, '0000-00-00 00:00:00'); //nieuw record inserten.
  COMMIT;
END;//

DELIMITER ;

Vervolgens heb ik 2 connecties naar mysql, waarbij ik de volgende handelingen uitvoer:

Connectie 1: call web_before("test", 1);
Direct daarna in connectie 2 (tijdens de sleep van connectie 1)
Connectie 2: call web_before("test2", 1);
<Op dit moment blijft connectie 2 hangen wachtend op connectie 1>
<Vervolgens zodra connectie 1 commit is gaat connectie 2 verder>

Maar op de SQL regel: INSERT INTO website VALUES(into_pos, in_item, CURRENT_TIMESTAMP, '0000-00-00 00:00:00'); in connectie 2 krijg ik de melding: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Hoe krijg ik het nu voor elkaar dat zodra een connectie web_before aanroept, niemand anders iets in de tabel mag selecten, inserten, updaten of deleten tot dat de gehele SP doorlopen is?
Wat ik gewoon wil is dat:
wanneer ik Connectie 1: call web_before("test", 1); aanroep en iemand anders Connectie 2: call web_before("test2", 1); aanroept. De 2e aanroep net zolang blijft wachten tot de eerste aanroep klaar is. Zodra dit gebeurd is wil ik dat de 2e aanroep werkt met de database waarin alle aanpassingen van de eerste aanroep verwerkt zijn.

Hetzelfde telt voor als ik Connectie 1: call web_before("test", 1); aanroep en een andere connectie gewoon een select, delete, update of insert aanroept. Ook deze acties van connectie 2 mogen pas uitgevoerd worden als de hele SP van connectie 1 klaar is.

Is locking buiten de SP echt de enige mogelijkheid? (vindt dit echt zon ranzige oplossing).

[Verwijderd]

Hoi Uhmmie,

Ik relateer het topic even naar MSSQL omdat ik daar iets beter bekend mee ben, maar als ik het zo lees komt het redelijk overeen.
Transacties maken een snapshot van de database zoals die is voordat ze starten en zetten een flag 'uncommited' bij elke row die ze bewerken.

heel simpel gezegt als een transactie 4 taken verricht (read, update, delete, read).
zal de lock (bij het standaard isolatie level) alleen in stand worden gehouden tijdens het moment dat hij update of delete.

Als het echt zeker wil weten dat query 1 altijd werkt met de data van het moment dat query 1 van start ging,
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

hierdoor gaat query 2's veranderingen (die eerst naar temp gaan) pas naar de echte database nadat de eerste query compleet klaar is (en in geval van transacties, gecommit is).

hoop dat dit je probleem oplost.

[Uhmmie]

Ik heb wat onder mssql zitten testen en daar lijkt TRANSACTION ISOLATION LEVEL SERIALIZABLE; wel te werken zoals ik het wil (zoals ik het begrepen heb is dit gewoon een lock table ... write, daar geen andere connectie dezelfde records mag updaten/deleten, maar daarnaast ook niks mag inserten tot de procedure klaar is).

Onder mysql lijkt dit echter gewoon niet te werken,daar ze datzelfde transaction nivo anders implementeren (is gewoon een multiread waarbij hij aan elke select IN SHARED MODE toevoegd).

Beetje jammer dat mysql hierin voor mij te kort komt. Ik moet mssql nog even verder gaan testen of dat wel echt doet wat ik wil, maar zo op het eerste gezicht lijkt dit (in ieder geval beter) te werken. Uit eindelijk als ik mijn prove of concept echt verder wil gaan uit ontwikkelen is het echter natuurlijk niet echt handig dat het alleen mssql support en geen mysql .

[cariolive23]

MySQL is zéér beperkt met de ondersteuning van stored procedures, wanneer je dat vergelijkt met de mogelijkheden van SQL Server, PostgreSQL, Oracle, etc. dan zakt je de moed direct in de schoenen. Wat je hierboven beschrijft, werkt ook in PostgreSQL en Oracle, is geen enkel probleem.

MySQL is met heel veel zaken een uitzondering, dat is met stored procedures niet anders.

Maar, wanneer jij meerdere databases wilt gaan ondersteunen, dan is het niet handig om stored procedures te gebruiken. PostgreSQL en Oracle lijken nog wel wat op elkaar, maar zelfs dan zul je vrijwel altijd nog wat punten en komma's anders moeten plaatsen. De verschillen met SQL Server zijn groter, al is het wel te vertalen.

[Uhmmie]

Dat ik per database een implementatie moet maken van de stored procedures is geen probleem (sterker nog liever een sp implementatie per database, dan dat ik die logica naar de code moet halen), zolang de input/output van de procedure maar hetzelfde is. Maar mysql lijkt idd helaas gewoon zijn eigen weg te varen (ik zou bijna zeggen de internet explorer onder de databases lol ). Wel jammer data het niet mogelijk is .