Problem z Transaction

Witam, mam problem z prawidłową realizacją tranzakcji. Opisanie problemu wydaje się być nieco trudne, ale spróbuję zrobić to najlepiej jak potrafię.
Tablica Uzytkownik zawiera klucz główny na polu id_uzytkownika oraz UNIQUE na polu login_uzytkownika, w zabezpieczenia przed identycznym loginem w bazie danych.
Starałem się zaprojektować jak najlepiej tabelę, tak aby pozwalała na archiwizowanie danych o użytkownikach, w związku z czym jedno z pól tablicy zawiera ewentualny identyfikator aktualniejszej wersji konta. Aby zachować wszystkie informacje o wcześniejszych danych łącznie z loginem chciałem użyć tranzakcji w celu zespolenia kilku poleceń. Przede wszystkim zmieniam login użytkownika według wzoru: '#' + id_uzytkownika + '#' + login, tak aby nie UNIQUE nie zgłosiło wyjątku. Pole login_uzytkownika ma długość 30 znaków, natomiast aplikacja blokuje do długości 20, przez co w bazie mogę przechowywać w najgorszym wypadku do #99999999#. Dalej chciałem wprowadzić konto do bazy z nowymi danymi, a na koniec wprowadzić id_nast_uzytkownika czyli identyfikator aktualizacji konta. Problem pojawia się w momencie gdy dodaję nowy wpis, bo tranzakcja tak jakby nie wysłała jeszcze danych do bazy i widniał w niej używany już login. Czy muszę skożystać z poziomu tranzakcji czy jeszcze coś innego?

try
{
    connection.Open();
    transaction = connection.BeginTransaction("Edycja konta użytkownika");

    commandUpdateFirst.Transaction = transaction;
    commandInsert.Transaction = transaction;
    commandUpdateSecond.Transaction = transaction;

    commandUpdateFirst.Parameters.Clear();
    commandUpdateFirst.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);

    commandUpdateFirst.ExecuteNonQuery();

    commandInsert.Parameters.Clear();
    commandInsert.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);
    commandInsert.Parameters.AddWithValue("@nazwa_uzytkownika", nazwa_uzykownika);
    commandInsert.Parameters.AddWithValue("@login_uzytkownika", login_uzytkownika);
    commandInsert.Parameters.AddWithValue("@tel_uzytkownika", tel_uzytkownika);
    commandInsert.Parameters.AddWithValue("@email_uzytkownika", email_uzytkownika);
    commandInsert.Parameters.AddWithValue("@data_utwo_uzytkownika", now);
    commandInsert.Parameters.AddWithValue("@id_zalo_uzytkownika", UserLogin.id);

    commandInsert.ExecuteNonQuery();

    int id_nast_uzytkownika = Convert.ToInt32(commandInsert.ExecuteScalar());

    commandUpdateSecond.Parameters.Clear();
    commandUpdateSecond.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);
    commandUpdateSecond.Parameters.AddWithValue("@id_nast_uzytkownika", id_nast_uzytkownika);

    commandUpdateSecond.ExecuteNonQuery();

    transaction.Commit();
}

Podaj jeszcze treści zapytań inserta i obu update

Poniżej zamieszczam całą metodę odpowiedzialną za przedstawiony proces...

public bool EditUser(int id_uzytkownika, string nazwa_uzykownika, string login_uzytkownika, string tel_uzytkownika, string email_uzytkownika, ref TextBox tbLogin, ref ErrorProvider erLogin)
        {
            SqlCommand commandUpdateFirst = new SqlCommand("UPDATE Uzytkownik SET login_uzytkownika = '#' + CONVERT(VARCHAR(20), @id_uzytkownika) + '#' + (SELECT login_uzytkownika FROM Uzytkownik WHERE id_uzytkownika = @id_uzytkownika) WHERE id_uzytkownika = @id_uzytkownika", connection);
            SqlCommand commandInsert = new SqlCommand("INSERT INTO Uzytkownik (nazwa_uzytkownika, login_uzytkownika, haslo_uzytkownika, tel_uzytkownika, email_uzytkownika, data_utwo_uzytkownika, id_zalo_uzytkownika) VALUES(@nazwa_uzytkownika, @login_uzytkownika, (SELECT haslo_uzytkownika FROM Uzytkownik WHERE id_uzytkownika = @id_uzytkownika), @tel_uzytkownika, @email_uzytkownika, @data_utwo_uzytkownika, @id_zalo_uzytkownika); SELECT @@IDENTITY", connection);
            SqlCommand commandUpdateSecond = new SqlCommand("UPDATE Uzytkownik SET id_nast_uzytkownika = @id_nast_uzytkownika WHERE id_uzytkownika = @id_uzytkownika", connection);
            
            SqlTransaction transaction = null;
            
            bool error = false;
            DateTime now = DateTime.Now;

            try
            {
                connection.Open();
                transaction = connection.BeginTransaction("Edycja konta użytkownika");

                commandUpdateFirst.Transaction = transaction;
                commandInsert.Transaction = transaction;
                commandUpdateSecond.Transaction = transaction;

                commandUpdateFirst.Parameters.Clear();
                commandUpdateFirst.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);

                commandUpdateFirst.ExecuteNonQuery();

                commandInsert.Parameters.Clear();
                commandInsert.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);
                commandInsert.Parameters.AddWithValue("@nazwa_uzytkownika", nazwa_uzykownika);
                commandInsert.Parameters.AddWithValue("@login_uzytkownika", login_uzytkownika);
                commandInsert.Parameters.AddWithValue("@tel_uzytkownika", tel_uzytkownika);
                commandInsert.Parameters.AddWithValue("@email_uzytkownika", email_uzytkownika);
                commandInsert.Parameters.AddWithValue("@data_utwo_uzytkownika", now);
                commandInsert.Parameters.AddWithValue("@id_zalo_uzytkownika", UserLogin.id);

                commandInsert.ExecuteNonQuery();

                int id_nast_uzytkownika = Convert.ToInt32(commandInsert.ExecuteScalar());

                commandUpdateSecond.Parameters.Clear();
                commandUpdateSecond.Parameters.AddWithValue("@id_uzytkownika", id_uzytkownika);
                commandUpdateSecond.Parameters.AddWithValue("@id_nast_uzytkownika", id_nast_uzytkownika);

                commandUpdateSecond.ExecuteNonQuery();

                transaction.Commit();
            }

            catch (SqlException ex)
            {
                if (ex.Number == 2627)
                {
                    error = true;
                    erLogin.SetIconPadding(tbLogin, 10);
                    erLogin.Icon = Icon.FromHandle(((Bitmap)imageList16x16.Images["exclamation.png"]).GetHicon());
                    erLogin.SetError(tbLogin, "Wprowadzony login jest już zajęty przez innego użytkownika!");
                }
                else
                {
                    MessageBox.Show(ex.ToString());
                }
                transaction.Rollback();
            }
            catch (Exception ex)
            {
                error = true;
                transaction.Rollback();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return error;
        }

miszczu :/ czemu dwa razy wykonujesz insert?

commandInsert.ExecuteNonQuery();
 
int id_nast_uzytkownika = Convert.ToInt32(commandInsert.ExecuteScalar());

powinieneś tylko wykonać ExecuteScalar() jeśli zwracasz SELECT @@IDENTITY z tej query.

Moje niedopatrzenie... wszystko już działa, Massther wypowiadałeś się na temat innego rozwiązania zaproponowanego prze zemnie na tym forum. Powiedz co myślisz o takiej archiwizacji? System w ten sposób zbiera wszystkie dane o najmniejszych zmianach, przez co w łatwy sposób można przeglądać wszystkie zmiany, a stosując to przy np. danych kontrahentów, zmieniając dane nie ingerujemy w dokumenty które korzystając ze starych danych.

Moim zdaniem wybrałeś dość osobliwy sposób na realizację twojego celu.
Archiwizacja chyba nie jest tu najwłaściwszym terminem.
Z tego co zrozumiałem chcesz móc edytować dane użytkownika, łącznie z loginem (który na tabeli ma założony unique) oraz zachować dane historyczne, aby stosować je do dokumentów zakładanych kiedy te dane obowiązywały.
Twoje rozwiązanie nazwałbym raczej jako workaround :)
Oczywiście trzeba by mieć szersze spojrzenie na twój system żeby zaproponować optymalne rozwiązanie.
Może napiszę tylko o jakiś sugestiach, wariantach.
Może na przykładnie jakiegoś sklepu czy systemu do faktur. Użytkownik X składa zamówienie na jakiś produkt, wcześniej miał zdefiniowany jakiś adres, oczywiście ten adres później mógł ulec zmianie, po której użytkownik złożył kolejne zamówienie. Oczywiście że pierwsze zamówienie powinno pokazywać że dostawa była na stary adres (ten zdefiniowany w momencie składania zamówienia). Można to zrealizować na kilka sposobów.
Albo wydzielamy takie dane jak adres do osobnej tabeli, a każda zmiana w aplikacji powoduje wstawienie nowego rekordu, nie modyfikację. W tabeli z użytkownikami trzymamy, które id adresu jest aktualne. W zamówieniu stosujemy id adresu, które w danej chwili jest aktualne, ale jeśli zmieni się aktualne id adresu w tabeli użytkownicy, w tabeli zamówienia nie ulegnie zmianie.
Można też dokonać redundancji danych i odpowiednie dane z tabeli użytkownicy, adres, przepisać do tabeli zamówienia.
Jeśli chodzi o zmianę loginu, to zawsze jest to pewna kłopotliwa kwestia w serwisach. Przede wszystkim jak rozwiązać problem ponownego użycia starego loginu. Czyli najpierw miałem login X, zmieniłem go na Y (w historii masz że miałem X). Rejestruje się inny użytkownik, który chce mieć login X. Pozwolić mu, czy nie? W sumie ja teraz używa loginu Y, ale kiedyś używałem X i to mogłoby być mylące dla innych użytkowników, którzy pamiętają mnie pod innym loginem. To jest kłopot w szczególności w serwisach typu fora itp.

Patrząc jeszcze na twój kod rozważyłbym wykonanie całej operacji jako jednej query (procedury).
Zanim zacząłbym dokonywać operacji na bazie, za pomocą select, sprawdziłbym czy nowy login nie koliduje z istniejącym i wyświetlił stosowny komunikat, zamiast jeździć po bazie i łapać odpowiedni wyjątek. Taką obsługę i tak możesz zostawić, jednak lepiej walidacji dostępności loginu dokonać wcześniej.

Wiem że na pewnym etapie projektu wprowadzenie niektórych zmian jest bardzo kłopotliwe, bo wymaga modyfikacji bardzo wielu elementów systemu. Jednak warto zastanowić się czy w dłuższej perspektywie pewne zmiany nie są lepsze.

Co do loginu to system pozwala wyłącznie na 'litery' + 'liczby' + '_', do 20 znaków, a procesie podmiany stary login zmieniany jest według wzoru # + stary_id + # + login do długości 30 znaków, przez co nie ma problemu z loginami, a cała reszta jest do przemyślenia...

Jasne, zrozumiałem. Dlatego napisałem że wg mnie to workaround, a nie profesjonalne rozwiązanie. Jestem też świadom że stosuję się takie sztuczki, bo czasem inaczej się nie daje bez głębokich zmian systemu. Sam pracowałem przy systemach gdzie robiło się takie rzeczy. Pamiętaj tylko że workaroundy kiedyś się mszczą. Szczególnie jeśli odchodzą autorzy takich rozwiązań, a nikt inny nie wie co autor miał na myśli. Czasem sam autor po pewnym czasie nie wie co miał na myśli :D
Jeśli jesteś jeszcze na jakimś wstępnym etapie rozwoju systemu, czy też masz dodatkowy tydzień na zmiany, to proponuję aby zastanowić się nad innym rozwiązaniem.