Wyznaczenie ID tabeli

Hej,
Podobno SELECT MAX(ID) ... Jest bardzo zły. Więc jak się powinno to robić ?
Auto-Increment nie odpowiada mi. Potrzebuje od razu ID do wykonania podrzędnych pytań oraz co przede wszystkim - potrzebuję postać postać całego zapytania w celu implementacji własnej replikacji BD (przy auto-inc klucze by się rozjechały ; oraz na klucz główny składa się zazwyczaj więcej jak 1 kolumna).
Lock na daną tabelę oraz SELECT MAX() będzie najoptymalniejszym rozwiazaniem?

Najpierw mała złośliwość: nie ma w języku polskim tworu najoptymalniejszy. Jest optymalny, co oznacza że jest to już najlepsze.

Cóż lock tabeli i policzenie max zadziała niewątpliwie. Ale czy to dobre rozwiązanie? W pewnych przypadkach można uznać że do zaakceptowania, ale generalizując raczej nikt nie zaleci tego.
Część systemów bazodanowych ma coś takiego jak sekwencje (np. Oracle). Systemy, które oferują rozwiązania typu autoincrement mają także mechanizmy do pobrania takiego id zaraz po wykonaniu insert. Więc możesz mieć je od razu do wykonania "podrzędnych pytań" , czymkolwiek one są.
Jeśli klucz główny składa się z kilku kolumn, to raczej nie ma sensu aby jego składową była kolumna autoincrement. Takich kolumn używa się do posiadania sztucznego klucza głównego, który najczęściej okazuje się najwygodniejszym rozwiązaniem.
Cześć systemów ma mechanizmy replikacji, warto ich użyć. Zapewne producent zrobił to lepiej niż tobie się to uda. Rozumiem replikację chcesz i tak zrobić na poziomie warstwy dostępu do danych, a nie na poziomie bazy danych, więc nic nie stoi na przeszkodzie, żeby w replice zdjąć autoincrement z pól, a po wstawieniu do bazy głównej pobrać rekord(-y) i wstawić je (łącznie z id) do bazy replikowanej.

1. MaxID pobieram poprzez program i tam jest Castowany jako Integer, wiec nie ma problemu z NULLem.
2. Rozwiazanie powinno byc niezalezne od SZBD (mam MySQL, ale moze w przyszlosci bedzie trzeba przesiasc sie na MSSQL ?)
3. Wlasna replikacja jest niezbedna - replikuje n-baz do jednej głownej (ale nie wszelkie dane). Dziala to takze w drugą stronę (serwer synchronizuje klienta).
4. Tworzenie sztucznych kluczy poprzez auto-inc jest wg mnie glupie i zbedne.
5. mowisz o mechanizmie a'la LAST_INSERT_ID ? Co jesli ja dodam rekord, ktos inny doda rekord, a potem ja uzyje LAST_INSERT_ID ? Do podrzednego zapytania zaaplikuje cudzy ID ...

Poki co, to nie widze innego rozsadnego rozwiazania poza Lockiem i MAX(ID) + 1 ....

add 1. Strzał w kolano. Jeśli całość operacji od pobrania Id do wstawienia rekordu nie jest w transakcji i z blokadą tabeli może wygenerować błędy. Z kolei jeśli cała operacja potrwa zbyt długo poważnie obniży wydajność systemu. Tak czy siak samo błąd jest już na etapie projektu takiego rozwiązania.
add 2. z tym trochę gorzej jeśli od strony samego sql chcesz to rozwiązać, ale przez warstwę dostępu do danych da się. Po prostu masz odpowiedni kawałek warstwy zależny od typu SZBD.
add 3. Hmm a co jeśli klient traci połączenie z centralą? Twoje rozwiązanie uniemożliwia pracę w takiej sytuacji. A w tego typu architekturze właśnie o to chodzi żeby klient mógł działać nawet offline, a po przywróceniu połączenia dokonała się synchronizacja. Inaczej takie rozwiązanie nie ma sensu, bo po co bazy klienckie?
add 4. tia, widać że życia nie znasz. Ile prawdziwych systemów napisałeś? A jaki unikalny klucz wymyślisz dla klienta, albo lepiej dla zamówienia?
add 5. poczytaj jaki zasięg (scope) mają techniki wyciągania klucza ostatnio wstawionego rekordu. Jasne jak strzelasz pojedynczymi zapytaniami do bazy to możesz mieć kłopot
np w MS SQL
insert into Tab1 values ('bla')
select SCOPE_IDENTITY()
wysyłasz jako jedno zapytanie do bazy w transakcji.

Nikt nie wspomniał o jeszcze jednej możliwości, mianowicie o puli identyfikatorów. Załóżmy że na systemie rozproszonym pracują pracownicy, każdy ma swój liczbowy ID i pulę do wykorzystania na konkretne tabele. Pula każdego pracownika rozpoczyna się np. od jego ID * 100000 i tworząc np. zamówienia ID tych zamówień to 100001, 100002 itd.
Pracownik ID = 2 tworzy zamówienia z identyfikatorami 200001, 200002 itd.
Z takim mechanizmem spotkałem się kilka razy i zawsze się sprawdza w systemach rozproszonych gdzie występują replikacje danych.

Ja generowałem dla każdego rekordu GUID, i miałem spokój z wartością klucza w relacjach. System musiał funkcjonować, generować dokumenty mimo braku połączenia z centralną bazą danych, a później ewentualnie mógł sobie synchronizować dane. Wszystko cacy, bez żadnych problemów funkcjonowało.

MiL znowu proponujesz rozwiązanie, które czasem będzie działało, ale teoretycznie ma ograniczenie i może spowodować po jakimś czasie błąd.
W takim przypadku lepiej mieć klucz główny złożony z dwóch kolumn, id autoincrement oraz kodu bazy (Klient1, klient2, centrala). W ten sposób jednoznacznie możemy identyfikować rekordy i nie martwić się o autoinkrementowalność id.
UUID (przez MS nazwany GUID) wydaje się faktycznie niezłym pomysłem.

Ale replikacja to nie tylko kwestia id rekordów. Często baza centralna, to nie tylko kopia danych od wszystkich klientów, ale także umożliwia pracę użytkowników centrali firmy i modyfikację danych. Także użytkownicy z filli 1 mogą modyfikować rekordy, których autorami jest fillia 2. Oczywiście zakładamy że zmiany nastąpiły w trybie offline.
Teraz należy rozwiązać konflikt, czyja zmiana jest ważniejsza, centrali, filli autora, inne filli, ... i to jest największa bolączka synchronizacji danych w bazach rozproszonych. Pomyślałeś jak będziesz rozwiązywał takie sytuacje?

Właśnie pracuję przy projekcie gdzie synchronizują się 3 bazy i zmiany które mają być rozpropagowane trafiają do puli i są sortowane po dacie zmiany. Z puli wybierana jest ostatnia i przenoszona na każdą bazę. W naszym przypadku działa to dobrze i jest wystarczające.

Co do korzystania z puli to oczywiście że nie wszędzie się to nadaje ale jest to prosty sposób rozwiązywania konfliktów i wielu przypadkach wystarczający.

Chyba coś źle opisałeś te pule, albo nie do końca coś zrozumiałem.
Jak ma się informacja z pierwszego postu o tworzeniu ID powiększanego o coś, do informacji z drugiego postu o sortowaniu i wybieraniu ostatniej zmiany?

Załóżmy że mamy tabelę Orders z kolumnami ID, ClientId, ProductId, Price, InsertedByUserId, InsertedDate, czy jakoś tak. I na jej przykładzie powiedz mi jak wprowadzane są, edytowane i synchronizowane zamówienia między centralną i dwoma filliami także w scenariuszu że jedna lub obie fillie tracą połączenie z centralą. Bo może rozwiązanie jest niegłupie, tylko nie złapałem idei.

Pula i synchronizacja występują w dwóch różnych systemach ale te mechanizmy można by było ze sobą połączyć w razie potrzeby.

Użycie puli jest proste. Każdy pracownik robiący zamówienie ma swój przedział identyfikatorów, zwykle początkiem jest jego ID * np. 100000, czyli może złożyć 100000 zamówień. Kluczem w tabeli ZAMOWIENIE jest samo ID, ono się nie powtórzy bo każdy pracownik ma inny zakres. Po zapisaniu zamówienia pula pracownika na zamówienia jest inkrementowana. Mechanizm wymyślony był dawno temu a działa w systemie gdzie zamówienia są robione offline i potem replikowane do bazy centralnej. Bardzo to ułatwia replikację bo ID zamówień jest stałe na obu bazach a zdarza się że jest ono wykorzystywane jako klucz obcy w innych tabelach (aktywności do zamówienia itp.). Następne ID jest przypisane do pracownika i nawet gdy zaczyna pracę z wyczyszczoną bazą to składając zamówienie wiadomo jakie to ID będzie. Wada to oczywiście ograniczona ilość takich identyfikatorów na pracownika ale można robić przedziały co milion a to już sporo.

Druga rzecz to synchronizacja trzech baz, to już jest inny system. Załóżmy że mamy tabele z asortymentem na trzech bazach i zmiana w jednej musi pociągać zmianę w dwóch pozostałych. Póki co działa to tak że każda zmiana jest logowana, potem logi zmian trafiają do bazy pośredniej i tam wybierana jest ostatnia zmiana i przenoszona na trzy bazy. Liczy się data modyfikacji, tylko ostatnia zmiana danego asortymentu jest przenoszona na wszystkie bazy. Synchronizacją zajmuje się job uruchamiany co pewien czas, tak że jeżeli połączenie zostanie utracone to po jego odzyskaniu wszystkie zmiany z tego czasu zostaną przeniesione.

Rozwiązanie z pulami działa już około 10 lat i nigdy nie było z tym problemów. Synchronizacja natomiast jest w fazie testów.