Jak to jest z tą blokadą wiersza w InnoDB

Witam

jak już większości wiadomo, zapis danych do bazy MySQL o typie InnoDB, odbywa się sekwencyjnie, czyli występuje kolejkowanie, czyli dany wiersz jest blokowany na czas zapisu (przeciwnie niż MyISAM, gdzie blokowana była cała tabela).
Inni piszą, że można dodać dodatkową kolumnę np. 'blocked', której nadajemy wartość 1, gdy wykonujemy jakieś operacje na niej i sprawa załatwiona :)

Ja na przykład chcę wykonać taką operację :
otóż chciałbym po zapisie wiersza do bazy zablokować go od razu, gdyż nie wszystkie pola są uzupełnione, np. identyfikator, który powstaje na zapisanych wcześniej danych - MD5(IDuser + NameUser + DT). Po utworzeniu 'identyfikatora' i wykonaniu UPDATE, odblokowuje dany wiersz.

W tym samym czasie inni użytkownicy wykonują to samo, wtedy wiem, że dany wiersz jest bezpieczny i co najważniejsze ID (właściwość autoincrement) będzie to które odpytuje a nie inne, kolejne.

Ktoś napisał :

W pracy mam baze oracle i do obsługi jest wykorzstywana java.
Podczas zapisu do bazy za pomocą jakiejś funkcji(nie pamiętam nazwy) blokuje dany rekord,
a po zapisie odblokowuje go.
Pytanie moje po części dotyczyło tego czy php (a może mysql) posiada funkcję do blokowania rekordów

... wpadł mi do głowy pomysł, aby zrobić to za pomocą transakcji (TRANSACTION), ale znalazłem coś takiego :

http://forum.webhelp.pl/php-i-bazy-danych/blokowanie-transakcji-w-mysql-lock-in-share-mode-t205428.html

i znowu w czarnej d...

Napisz trigger który wygeneruje identyfikator w chwili dodawania rekordu i masz problem z głowy, ew. przed "dotknięciem" rekordu w innej części aplikacji sprawdzaj czy ma wygenerowany identyfikator.

na pewno rozwiązuje to część problemu, ale nie rozwiązuje głównego, blokowania wierszy

Zastanów się czy rzeczywiście masz jakiś problem czy może sam go sobie tworzysz?

takie coś robi się inaczej - właśnie za pomocą transakcji

1. rozpoczynasz transakcję
2. robisz insert
3. generujesz identyfikator
4. robisz update
5. robisz commit
   i teraz dopóki nie wykonasz punktu 5 to NIKT poza transakcją, która zrobiła inserta nie widzi tego wiersza więc nie ma konieczności jego blokowania

Transakcje to jedno rozwiązanie, ale podobno można to też zrobić za pomocą "lock in share mode"

SESSION1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tst values(1);
Query OK, 1 row affected (0.00 sec)

SESSION2:
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tst;
Empty set (0.01 sec)

#Session2 does not see any rows as transaction was not commited yet.

SESSION1:
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

SESSION2:
mysql> select * from tst;
Empty set (0.00 sec)

mysql> select * from tst lock in share mode;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select * from tst for update;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

#Standard SELECT does not see rows while SELECT for UPDATE and LOCK IN SHARE MODE  sees it. 

można no i co z tego? BTW w tym co podałeś masz DOKŁADNIE opisane OBIE metody

Aby nie rozpoczynać nowego postu, chciałbym zapytać o Triggery. Prosty przykład z :
http://hengrui-li.blogspot.com/2009/11/solution-for-cant-update-table-in.html

mam dokładnie to samo, tzn. przy próbie wstawienia wiersza - działam tylko na jednej testowej tabeli- otrzymuje komunikat :

Error Code : 1442
Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Skoro nie mogę na tej samej tabeli wykonać wyzwalacza, to jak inaczej ?

Jeśli zas zrobię to, tak :

DELIMITER |

CREATE
    TRIGGER TcreateName_AI AFTER INSERT ON test 
    FOR EACH ROW BEGIN
	SET NEW.nazwa = "test" ;
    END;
|

DELIMITER ;

Wówczas błędu brak, ale nie widzę nowej wartości dla kolumny 'nazwa'

jeśli potrzebujesz zrobić coś takiego to w 99% przypadków oznacza to błąd w projekcie. Jak sobie wyobrażasz działanie np. trigerra before/after insert, który wstawiał by inny wiersz w TEJ SAMEJ tabeli??

AFTER jest wywoływane PO wstawieniu wiersza

no i tak ma działać, tylko nie PO a PRZED, sorry za zamieszanie... ;)

Jeżeli chcesz ustawić wartość kolumny w tabli na której wywołujesz triggera to nie możesz tego zrobić w triggerze typu AFTER. Musisz użyć triggera typu BEFORE.

Wracając jeszcze do transakcji, na przykładowym kodzie MySQL - PHP :

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO test (nazwa) VALUES('blabla')");
printf ("Ostatnio dodany rekord ma id %d\n", mysql_insert_id());  

if ($a1) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

Rozumiem, że otrzymam ostatnie ID tylko dla tej transakcji, tak ? które jeszcze nie jest widoczne dla innych, używając fun. mysql_insert_id()

Tak. Dodam jeszcze, że w przypadku używania transakcji funkcja musi zostać wywołana przed commitem, ponieważ w innym wypadku może zwrócić bzdury.

dzięki za potwierdzenie, a z funkcją to całkiem zrozumiałe, że wywołanie musi być przed COMMITEM

Aby niepotrzebnie tworzyć nowy post postanowiłem tutaj zadać pytanie bo najbardziej pasuje gdyż sytuacja jest zbliżona.

Opis sytuacji:

Użytkownik chce np wycofać/usunąć dokument WZ. Program sprawdza czy do tego dokumentu nie została wystawiona np FV i wyświetla okno z zapytaniem czy na pewno chcesz usunąć WZ? - bo można

...W tym samym czasie... Inny użytkownik wystawia FV do tej WZ i pojawia się problem. Początkowo myślałem ok pierw okna z zapytaniem czy usunąć WZ a potem sprawdzanie czy nie został do niego wystawiony jakiś dokument. Całość dać w transakcję ale :)

wykonanie takiej sekwencji

START TRANSACTION
SELECT ... ( sprawdza czy wystawiono dokument )
DELETE ... (kasowanie WZ)
COMMIT

Wydaje mi się że wywołanie select nie zabezpieczy tego wiersz przed modyfikację przez innego użytkownika. Czyli może dojść do modyfikacji wiersza pomiędzy instrukcjami SELECT a DELETE.

Pytanie czy dobrze myślę czy wymyślam sobie problem :)

sam select nie blokuje wiersza (i dobrze bo jakby blokował to by to sensu nie miało) - do blokowania jest select * from where FOR UPDATE albo w bazach, które nie wspierają takiej składni trzeba zrobić tzw. pusty update UPDATE id = id WHERE id = :id