Witam.

Napotkalem na problem, z ktorym borykam sie juz kolejny dzien - niestety odpowiedzi nadal nie znalazlem. Po krotce - choc nie wiem czy da sie tak bardzo ograniczyc

W pewnym systemie rozpoczalem uzytkowanie tabel InnoDB - jak sie pewnie domyslacie - ze wzgledu na koneicznosc uzytkowania transakcji.

Konfiguracja to MySQL 4.0.21 (serwer) oraz klient w wersji 5.0.33 (na tej maszynie stoi drugi serwer MySQL - w wersji wlasnie 5.0.33).

Teraz wezmy jeden z problemow - problem licznikow. Mam kilka tabel, do ktorych laduje rekordy - musze recznie utrzymywac liczniki dla kazdej z tych tabel (kazdy rekord posiada kilka kolumn zawierajacych rozne formaty roznych licznikow).

Ale nas interesuje sama tabela licznikow. Jest to tabela InnoDB z powiedzmy 3 kolumnami:



Teraz.. w systemie pracuje 200 uzytkownikow. Zdarzaja sie sytuacje, gdy to wielu z nich - praktycznie w tej samej chwili - stara sie dodac nowy rekord do tabel z danymi. Aby to zrobic skrypt PHP musi pobrac odpowiednie wartosci licznikow a nastepnie je zwiekszyc - skoro zostaly juz uzyte.. Bez zastosowania transakcji pojawil sie problem, iz ponizszy kod:

[SQL] pobierz, plaintext 
UPDATE table_liczniki SET licznik_1=licznik_1+1 WHERE id=1;
SELECT licznik_1 WHERE id=1;
[SQL] pobierz, plaintext 

wykonywany praktycznie rownolegle zwracal ta sama wartosc licznika. Oczywiscie problem bral sie stad, iz powyzszy kod wykonywany byl na tabelach MyIsam, ktore pobieraly rownolegle te same dane. Moglem zalatwic sprawe za pomoca table locka, lecz nie o to przeciez chodzi - skoro mozemy uzyc InnoDB oraz transakcji..

Teraz przejdzmy dalej. Jak sie domyslacie po napotkaniu powyzszego problemu zalozylem tabele InnoDB i rozpoczalem modyfikacje kodu dodajac obsluge transakcji. No i teoretycznie wszystko w porzadku. Na poczatku wytestowalem dzialalnosc za pomoca terminala - te same transakcje postaralem sie nalozyc na siebie. Wczesniej troche konfiguracji serwera:

[SQL] pobierz, plaintext 
mysql>  SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
mysql klient A> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql klient A> SELECT licznik_1 FROM table_liczniki WHERE id=1 FOR UPDATE;
+--------------------+
| licznik_1          |
+--------------------+
|              60000 |
+--------------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

W tej chwili odpalilem drugiego klienta i chcialem dokonac tego samego:

[SQL] pobierz, plaintext 
mysql klient B> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql klient B> SELECT licznik_1 FROM table_liczniki WHERE id=1 FOR UPDATE;
[SQL] pobierz, plaintext 

I klient B "zawisl" - oczekujac na zakonczenie transakcji u klienta A. Gdy ta zostala zakonczona - za pomoca COMMIT - kleint B sie "odwiesil" pobral co mial do pobrania i wszyscy szczesliwi

Teraz pozostala do sprawdzenia dzialalnosc systemu - czyli kodu PHP. W duzym uproszczeniu czesc kodu odpowiadajacego za owa transakcje wyglada tak:

[PHP] pobierz, plaintext 
<?php
$transaction = true;
 
$mysql = new mysql; // klasa do obslugi odbc
$mysql->query('SET autocommit=0'); // wylaczamy autocommit..
$mysql->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); // w zasadzie tylko dla pewnosci
 
$mysql->query('BEGIN;');
$res = $mysql->query('SELECT licznik_1 FROM table_liczniki WHERE id=1;');
if(!$res)
{
	$transaction = false;
}
else
{
	$row = mysql_fetch_assoc($res);
}
$res = $mysql->query('UPDATE table_liczniki SET licznik_1=licznik_1+1 WHERE id=1;');
if(!$res)
{
	$transaction = false;
}
if($transaction === true)
{
	$mysql->query('COMMIT;');
	$licznik = $row['licznik_1'];
}
else
{
	$mysql->query('ROLLBACK;');
	$licznik = false;
}
?>
[PHP] pobierz, plaintext 

Aby sie przekonac sprawdzilem dzialalnosc tego kodu w przegladarce - system dziala poprawnie, liczniki ladnie sie nabijaja itd. Ale to metoda 'singlowa' - a nam chodzi o dzialalnosc multi - userowa. Czyli musimy ja zasymulowac. Znow uruchamiamy sesje terminala, laczymy sie z baza danych i jak ostatnio:

[SQL] pobierz, plaintext 
mysql klient A> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql klient A> SELECT licznik_1 FROM table_liczniki WHERE id=1 FOR UPDATE;
+--------------------+
| licznik_1          |
+--------------------+
|              60000 |
+--------------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

I w tej chwili uruchamiamy skrypt w przegladarce.. no i skrypt wisi - strona sie ;wczytuje' - czyli wszystko w porzadku - PHP czeka na odpowiedz z serwera MySQL, a ten znow czeka na zakonczenie transakcji.. w chwili gdy dalem COMMIT w terminalu, strona w przegladarce sie "odwiesila" i wszystko zadzialalo poprawnie.

No to co tu duzo myslec - wystawiamy kod i testujemy na zywym organizmie. I juz w pierwszej minucie problemy - licznik powiela swoje stany, pomija niektore liczby.. slowem wariuje. Powiedzmy, ze mamy sytuacje taka po pobraniu kilku stanow - rekordy w tabelach zapisujacych stany licznikow wskazuja:



Itd... czyli cos sie.. cos nie dziala jak nalezy Teraz w skrocie - gasilem serwer MysqL 4, pozostawiajac dzialajacy jedynie MySQL 5 - i na nim odpalalem powyzsze. Czyli wszelkie podejrzenia odnoszace sie do gryzienia sie serwerow odpadaja. Problem nie zniknal.
Probowalem dzialac na domyslnym ISOLATION LEVEL, czyli REPEATABLE-READ - zadnych efektow - tak samo jak SERIALIZABLE.
Probowalem nie ruszac autocommit pozostawiajac go domyslnie (czyli wlaczony) - w koncu BEGIN "wylacza" autocommit az do czasu COMMIT badz ROLLBACK. I nadal nic.

No to teraz logi systemowe. Tutaj klient wykonal transakcje i niby wszystko w porzadku:



Widac, ze w porzadku, gdyz UPDATE lecacy po COMMITowaniu transakcji podaje nam poprawna wartosc licznika.
A teraz jackpot - dwoch uzytkownikow rozpoczelo transakcje praktycznie w tej samej chwili. I oto efekt:

KLIENT 1:


KLIENT 2:


No i jakis kosmos. Po pierwsze jedna transakcja wrypala sie w druga... co jest bardzo dziwne. A po drugie - skad kolejne transakcje u klienta 2? (notabene nie udane)?

Jesli ktos ma jakis pomysl to zapraszam do przedstawienia Glupioby bylo z takiego powodu tworzyc licznik przy uzyciu LOCKowania na poziomie rekordu w InnoDB. A poki co jedyne co mi pozostaje to chyba namierzyc powod, dla ktorych u klienta 2 na koncu pojawily sie 3 rozpoczecia transakcji