Integralność danych przy jednoczesnym dostępie Opcje

[Kikert]

Cześć,
stworzyłem stronę z konkursami online, jest oparta o chat, w którym użytkownicy wpisują odpowiedzi. Jeżeli trafią jedną z trzech opcji, użytkownik wygrywa. W takiej sytuacji do tabeli z wiadomościami była dodawania nowa, a później wykonywał się UPDATE w tabeli z konkursami, żeby ustawić zwycięzcę, jeżeli odpowiedź jest prawidłowa. Niestety, ostatnio są problemy - skrypt dwa razy dodał prawidłową odpowiedź do chatu, ale nie ustawił zwycięzcy i wygrała osoba, która podała odpowiedź jako trzecia. Niestety kto inny zajmuje się administracją - nie mogę założyć dodatkowych logów itp.

Zastanawiam się, jak zabezpieczyć skrypt przed taką sytuacją. Korzystam z mysqli, obydwie tabele na silniku InnoDB. Uwarunkowałem dodanie wiadomości do chatu od tego, czy wykonano UPDATE, ale obawiam się, że to za mało. Poniżej fragment pliku odbierającego ajaxowe zapytania:

[PHP] pobierz, plaintext 
$update = $db->query(
	"UPDATE {$DBP_}contests SET `winner`='{$_SESSION['id']}' ".
	"WHERE `id`='{$contest['id']}' AND winner IS NULL AND '{$message_lowercase}' IN (answer, answer2, answer3)"
);
 
if ($update) {
 
	if ($db->affected_rows === 1)
		$json['won'] = array(
			'answers' => array($message),
			'winner_login' => $_SESSION['username'],
			'winner_gravatar' => $_SESSION['gravatar']
		);
 
	// ADD MESSAGE
	$db->query("INSERT INTO {$DBP_}chat SET `user_id`='{$_SESSION['id']}', `date`=NOW(), `text`='{$message}'");
 
}
[PHP] pobierz, plaintext 

Czy ktoś mógłby podać jakąś wskazówkę co jeszcze można zrobić?

[Crozin]

1. Wszelkie dane modyfikujące stan bazy danych powinny być objęte jawną transakcją.
2. W tej chwili IF sprawdzający czy zmodyfikowano dane odnosi się wyłącznie do $json['won'] = ... - zapytanie INSERT wykona się właściwie zawsze.

[Kikert]

Tak, wiadomość ma się wysyłać za każdym razem, bo to chat Jednak jeżeli ktoś trafi z odpowiedzią, to przy okazji baza danych zapisuje informację, że ten user jest zwycięzcą.

Wcześniej wyglądało to w taki sposób (wyciąłem treści zapytań, są takie same):

[PHP] pobierz, plaintext 
// INSERT INTO chat...
// UPDATE contests...
if ($update && $db->affected_rows === 1)
    $json['won'] = array(...);
[PHP] pobierz, plaintext 

Problem był, taki, że jeżeli wiadomość była odpowiedzią, to dodawała się do tabeli z wiadomościamu z chatu, ale nie aktualizowała kolumny contests.winner. Efekt był taki, że podano poprawną odpowiedź, która nie wygrywała konkursu.

Nie znam się za bardzo na transakcjach, ale z tego, co mi wiadomo, transakcja korzysta z tabeli w takim stanie, w jakim była w momencie rozpoczęcia transakcji. Czyli w sytuacji kiedy w minimalnych odstępach czasu przychodzą dwa wpisy z wygrywającą odpowiedzią, to wygra ten późniejszy, bo w momencie rozpoczęcia transakcji konkurs nie był jeszcze wygrany.

Ten post edytował Kikert 18.07.2012, 11:34:40

[Crozin]

[SQL] pobierz, plaintext 
LOCK TABLE tbl_contests READ;
START TRANSACTION;
INSERT INTO tbl_chat ...;
UPDATE tbl_contents ...;
 
-- PHP if affectedRows
 
COMMIT;
UNLOCK TABLES;
[SQL] pobierz, plaintext 

[Kikert]

Wielkie dzięki, zaraz zobaczę, jak to śmiga

Jeszcze drobne pytanie - co stanie się w sytuacji, kiedy ktoś inny też będzie wykonywał kod w tym samym czasie? Czy jego żądanie zostanie po prostu zignorowane, czy wykonane z opóźnieniem?

Ten post edytował Kikert 18.07.2012, 12:52:39

[Crozin]

W przypadku gdy na tabelę jest założona blokada inne zapytania będą musiał poczekać ze swoim wykonaniem do czasu zdjęcia tej blokady, czyli wykonają się z opóźnieniem.

Swoją drogą, jeżeli w mechanizmie sprawdzania czy dany wpis jest zwycięski korzystasz z zapytania UPDATE oraz affectedRows możesz nawet zrezygnować z blokady odczytu tabeli.