Transaction i C# eller stored procedure med cursor i MySQL

Jeg vil anbefale at lave det med en transaction i din C# kode.

Det maa give en noget paenere logik.

Ja, det var også min umiddelbare tanke. Jeg tænkte bare om der var performance- eller andre grunde til ikke at gøre det? Jeg er ikke så stærk i SQL, så jeg vil hente et datasæt ind med én SQL-forespørgsel, og så gennemgå det med C# og lave en SQL-kommando for hver item i datasættet. Det giver en masse små SQL-kommandoer. Men det burde ikke være noget problem?

Hvis du i forvejen anvender datasets, så kunne du også have den anden tabel i datasettet.
I stedet for de små sql-kommandoer, kunne du ændre de værdier du ønsker i tabel 2, og så køre adapterens Update metode, som så opdaterer alle de rækker i db'en som er ændret i datatable'en.

neoman: Nå ja, jeg havde helt glemt begrebet DataSet (jeg tænkte egentlig bare på en enkelt tabel). Men det lyder som om det er det, jeg gør.

... altså at det er din ide, jeg vil bruge.

Hvordan er det nu... er man sikker på, at man låser databasen mellem start og slut på en transaktions-definition?

1 BeginTransaction
2 Læs tabeller ind i DataSet
3 Manipuler med tabellerne
4 Update data ned til MySQL
5 Commit /rollback

Altså kan man roligt foretage sig hvad som helst mellem 1 og 5?

Jeg mener at have set et sted at det er bedre at holde reads/writes adskilt, så det er nok noget med at læse, manipulere, og når klar til at kalde .Update lægge alle kald til .Update i en transaction.

transaktioner har mig bekendt intet med låsning at gøre - det skal du formentligt gøre separat - det er kun en mekanisme til at sikre at enten alle eller selt ingen sql-statements bliver udført.

Hvad locking angår, især i web-miljø, vent på nogen som har bedre forstand på det end jeg.

Ok, jeg googlede lidt:

"An important property of transactions is that they are not visible to other sessions until they are complete and committed. No other thread can read inconsistent data from the table(s) while you are in the process of updating it."
(http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html)

... hvordan tolker du det?

Hmm kom i tanker om jeg selv kører transaktioner på nogle data sets. For at hindre problemer, hvis en anden opdaterede de underliggende data , så har jeg optimistic concurrency - transaktionen bliver rolled back, hvis de underliggende data er blevet ændret i mellemtiden, og så læser jeg dem ind igen og starter forfra.

I øvrigt, hvis du ikke kender typed datasets så er de værd at kaste et blik på - det tager 1½ time at sætte sig ind i, men er bare så nemme at have med at gøre.

31/10-2007 10:16:17 - jeg tolker det som jeg ikke ved hvad jeg taler om:) Der skal nok nogle på banen som er 100 på hvordan og hvorledes - det er jeg nemlig ikke.

ok, smid et svar, selvom jeg ikke er helt 100 selv. Jeg er godt på vej. Jeg prøver at reposte det specifikke med transaktioner i en anden kategori.

FYI, her er et linke mere, som jeg lige læser op på. Jeg har noteret mig at skulle kigge på typed datasets.

http://www.asp.net/learn/data-access/tutorial-63-cs.aspx

Jeg fortsætter med mere om transaktioner og concurrency her: http://www.eksperten.dk/spm/803646

neoman: 31/10-2007 10:17:35: Hvordan gør du med INSERTs?

Jeg kører ca.95% af min interaktion med db via typed datasets. For inserts kalder jeg bare tabellens .NewMyTableNameRow metoden for at lave en ny række. Når jeg så kører tableadapterens .Update metode, så finder den selv ud af hvad der skal insertes eller opdateres.

Besvarer dette spørgsmålet ?

nja, jeg hr bare kigget lidt løseligt på DataSet og Concurrency, og der finder jeg ingen omttale af INSERT, bare UPDATE, så jeg tænkte på, om der er noget automatik for det tilfælde, at man vil indsætte en række som allerede er indsat (Du henter 6 rækker, som du vil rette, og så indsætter du 2 mere, og skriver tilbage, men en anden som hentede de samme 6 rækker har i mellemtiden også indsat nye rækker. Da indholdet af de nye rækker har en logisk sammenhæng med de forrige rækker, så må der kun forekommer én insert for hver gang der er foretaget en select)

En stored procedure er normalt lidt hurtigere end multiple SQL saetninger. Men naeppe hvis
du skal til at rode med cursor. Og generelt vil jeg ikke anbefale den form for
optimering forend man har konstateret et problem.

Brug af DataSet har stor betydning for struktur i kode, men i sidste ende bliver det
stadig til et antal INSERT/UPDATE/DELETE saetninger der udfoeres mod databasen. Transaktioner
virker helt normalt. Og performance vil vaere den samme evt. en lille smule ringere (fordi
de auto genererede SQL saetninger maaske ikke er helt optimale).

Transaktioner hat meget med concurrency at goere via det begreb der hedder transaction
isolation level.

http://en.wikipedia.org/wiki/Transaction_isolation_level

31/10-2007 16:33:18 - systemet kan ikke af sig selv gætte hvad du ønsker - hvis du maintainer noget "record keeping" et sted, som du opdaterer, kan denne bruges til at checke (og faile, ved concurrency violation) om der nu er blevet tilføjet noget ekstra eller ej. Så hvis inserts kan lede til problemer, så skal der være en record 8eller felt i en bestående record) et sted med f.eks. "lastUpdate" eller noget i den dur. Kan ikke helt se hvordan det skulle kunne løses anderledes (men nu er der mange ting jeg ikke kan se:)

... sikke en masse der sker, bare fordi man går hjem fra arbejde :-) Det ser ud til at neoman, arne og mig, sagde hunden, er de eneste med denne interesse, så lad os bare fortsætte i én tråd.
Mht MySQL, så er det nok 5.1, jeg har (tjekker lige imorgen når jeg er tilbage på job).
Men for lige at opsummere: Mine prioriteter er: Enkel kode fremfor perfomance. Den omtalte operation er en bogføring i et regnskabsprogram, så det er en sjældent forekommende begivenhed - hvilket vel så taler for optimistic concurency på en eller anden måde.
Jeg er stadig forvirret mht. isolation level - er problemet, at man godt kan angive SERIALIZABLE i sin C# kode, men man aner ikke hvad man får, før man har læst dokumentationen for DBMSen?
Jeg tænker, at jeg måske kan strukturere operationen, så den kun laver updates og ikke inserts.
Lad mig lige skitsere lidt mere detaljeret:

Jeg har en tabel, Posteringer. Den indeholder posteringer for alle regnskaber og for alle brugere. Den bruger, som til enhver tid er logget på, har adgang til at skrive i Posteringer, hvor hver record bliver stemplet med brugerID og regnskabID (Der er mange brugere pr. regnskab). Dvs. når han trykker på "bogfør"-knappen, så skal jeg finde alle records med pågældende brugerID og regnskabID. De skal markeres i feltet Bogføringsnummer som skal have (SELECT MAX(Bogføringsnummer) from Posteringer where regnskabID=[regnskabID])+1, altså næste ledige nummer i bogføringstælleren for det pågældende regnskab (Hver bruger bogfører KUN sine egne bilag, men bogføringsnummeret gælder hele regnakbet). Alene i denne operation kan det vel potentielt gå galt, hvis MAXbogføringsnummer når at blive overskrevet inden jeg når at lægge 1 til og skrive tilbage (eller er jeg ude over det problem ved at samle det i én SQL-statement
(Update Posteringer SET bogføringsnummer=(SELECT MAX(Bogføringsnummer) from Posteringer where regnskabID=[regnskabID])+1 WHERE brugerID=[brugerID])?

Så langt, så godt. Så kunne man i princippet stoppe her, for så har man informationerne liggende i Posteringer, hvor man kan se, hvilke beløb der bøgført på hvilke konti, og så kan man tælle sig frem til summerne for de enkelt konti.
Men så er det jeg tænker, at lige netop her kunne det være godt aht. performance at opdatere en tabel med summer for de enkelte konti hver gang man bogfører, således at man ikke konstant skal tælle samtlige posteringer sammen (det kan hurtigt løbe op i tusindvis). Så derfor ville jeg lave det som en transaktion, der i et hug bogfører bilagene og opsummerer summerne for kontiene.

Måske er det nemmeste at lave en attribut DerBogFøresIØjeblikket [true|false] i min tabeller over regnkaber, og så sætte dentrue når der bogføres, og på den måde styre, at der kun er én ad gangen der kan bogføre i det samme regnskab. Hvis man så bygger "SET DerBogFøresIØjeblikket=true" ind i transaktionen, så skulle man være sikret i tilfælde af der rollback'es, således at man ikke får låst regnakabet for evigt?

Man boer aldrig bruge SELECT MAX+1 men derimod (i MySQL) LAST_INSERT_ID().

Det vil vaere daarligt database design at putte opsummeringer i tabel strukturen. Det er redundant data.

Du skal endvidere huske paa at database transaktioner er designet til at laase i millisekunder.

Til langtids laasning er der brug for andre mekanismer.

Har du laest http://www.eksperten.dk/artikler/996 ?

nej, men den vil jeg fa lige læse.
Så du vil anbefale IKKE at have en opsummeringstabel? Jeg har ikke erfaringer med, hvor lang tid det tager at selecte hundred eller tusindvis af records, men hvis det kan gøres hurtigt nok, er det jo nok at foretrække.

Det boer ikke tage lang tid.

Redundante data i en database er slemt.

Så fik jeg læst den. Den er et udmærket overblik.
Jeg tænker på, at min ide med en markering af, at der bogføres, vel er den type pessimistic locking, du omtaler i artiklen? Jeg forestiller mig at bogføringen sker ved et en enkelt klik, og altså tælles i milisekunder.
Så det må (forudsat jeg holder fast i ideen om en opsummeringstabel) enten være den model, eller en transaction med isolataion level serializable. Det ser ud til, at MySQL understøtter den - så det springende punkt, er om jeg kan forlade mig på at bruge SERIALIZABLE på MySQL (5.1, såvidt jeg husker)?

Hvis du laver dine SELECT og dine INSERT/UPDATE indenfor millisekunder kan du
bruge database transations med passende isolation level.

Du får brug for lobg time alternativerne hvis du laver SELECT, har et menneske til at kigge
på tallene i nogle minutter (evt. tre kvarter hvis det er frokost pause !) og så laver
INSERT/UPDATE.

Jep, ok, nu begynder det at lysne. Det med long time concurrency har jeg styr på. Jeg bruger .NETs standard med INSERT tekst=nytekst WHERE tekst=oldtekst.  Tak iøvrigt for det med MySQL 6. Jeg har fatktisk den installeret. Hvad er den seneste stabile version? 5.2?

Mht ISOLATION LEVEL SERIALIZABLE: Hvordan tester man, om det virker (Om MySQL vil æde min C#-kode, som instruerer MySQL om dette isolation level)? Man kan jo ikke så godt sætte applikationen i drift og vente på den første fejl?

5.0 - nyeste for production
5.1 - release candidate (d.v.s. snart klar)
6.0 - Alpha (hold dig langt væk)

Jeg lavede et lille eksempel i C#.

using System;
using System.Data;
using System.Threading;
using MySql.Data.MySqlClient;

namespace E
{
    public class Test
    {
        private const int NINS = 1000;
        private IsolationLevel isolvl;
        public Test(IsolationLevel isolvl)
        {
            this.isolvl = isolvl;
        }
        public void Run()
        {
            using(MySqlConnection con = new MySqlConnection("Database=TestInnoDB;Data Source=localhost;User Id=;Password="))
            {
                con.Open();
                int retries = 0;
                for(int i = 0; i < NINS; i++)
                {
                    MySqlTransaction tx = con.BeginTransaction(isolvl);
                    MySqlCommand sel = new MySqlCommand("SELECT * FROM tt", con);
                    sel.Transaction = tx;
                    MySqlDataReader rdr = sel.ExecuteReader();
                    while(rdr.Read())
                    {
                        // nothing;
                    }
                    rdr.Close();
                    MySqlCommand ins = new MySqlCommand("INSERT INTO tt(f2) VALUES('X')", con);
                    ins.Transaction = tx;
                    bool done = false;
                    while(!done)
                    {
                        try
                        {
                            ins.ExecuteNonQuery();
                            done = true;
                        }
                        catch (MySqlException)
                        {
                            retries++;
                        }
                    }
                    tx.Commit();
                }
                Console.WriteLine("retries=" + retries);
            }
        }
    }
    public class Program
    {
        private const int NTHR = 5;
        public static void TestLevel(IsolationLevel isolvl)
        {
            using(MySqlConnection con = new MySqlConnection("Database=TestInnoDB;Data Source=localhost;User Id=;Password="))
            {
                con.Open();
                MySqlCommand cre = new MySqlCommand("CREATE TABLE tt (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 VARCHAR(10)) TYPE=INNODB", con);
                cre.ExecuteNonQuery();
            }
            DateTime dt1 = DateTime.Now;
            Test[] tst = new Test[NTHR];
            Thread[] thr = new Thread[NTHR];
            for(int i = 0; i < thr.Length; i++)
            {
                tst[i] = new Test(isolvl);
                thr[i] = new Thread(new ThreadStart(tst[i].Run));
            }
            for(int i = 0; i < thr.Length; i++)
            {
                thr[i].Start();
            }
            for(int i = 0; i < thr.Length; i++)
            {
                thr[i].Join();
            }
            DateTime dt2 = DateTime.Now;
            Console.WriteLine(isolvl + " : " + (dt2 - dt1).TotalSeconds);
            using(MySqlConnection con = new MySqlConnection("Database=TestInnoDB;Data Source=localhost;User Id=;Password="))
            {
                con.Open();
                MySqlCommand drp = new MySqlCommand("DROP TABLE tt", con);
                drp.ExecuteNonQuery();
            }
        }
        public static void Main(string[] args)
        {
            TestLevel(IsolationLevel.RepeatableRead);
            TestLevel(IsolationLevel.Serializable);
            Console.ReadKey();
        }
    }
}

retries=0
retries=0
retries=0
retries=0
retries=0
RepeatableRead : 34,90625
retries=713
retries=686
retries=711
retries=732
retries=712
Serializable : 310,40625

Der er helt klart en effekt af serializable.

(ikke lige den effekt du efterspørger, men det er altså bagsiden af et højt
isolation level : performance lider under det !)

Ok, skal vi til at dele nogle points ud? Så smid et par svar.

Jeg prøvede at lave min C#kode serializable. Så lavede jeg et breakpoint midt i koden og prøvede at tilgå den samme tabel via kommadoprompten... og det gik fint! Så jeg stoler ikke helt på mit sytems implementation. Jeg endte med nedenstående i stedet.
Jeg har lavet en lås for det regnskab, jeg vil ind og rode med. Så ved jeg godt, at så er tabellen pivåben for evt. andre applikationer som måtte tilgå den, men på den anden side... så ved man at det er sådan, i stedet for at regne med en isolationlevel som måske/måske ikke virker.
Jeg bliver, så vidt jeg kan gennemskue, nødt til at bruge SELECT MAX, idet det jeg skal selecte max på, er en mængde af numre, som ikke har noget at gøre med ID, og den rækkefølge, som recordsne indsættes. Men når jeg så sørger for at der er en lås tilknyttet, som man (medgivet... skal HUSKE at) bede om, så burde det vel være ok?

public void Bogfoer(Int32 RegnskabID, String UserName, Int32 RegnskabsPeriodeID)
    {
        try
        {
            int NextBogfoeringsnummer;
            if (GetBogfoeringsLaasForRegnskab(UserName, RegnskabID))
            {
                // find bogføringsnummeret
                try
                {
                    NextBogfoeringsnummer = MaxBogfoeringsnummer(RegnskabID) + 1;
                }
                catch
                {
                    throw new Exception("Fandt intet max-bogføringsnummer");
                }

                // opdater bogføringsnummeret (I en transaktion, fordi der muligvis af performancehensyn skal laves opdatering af totaler.
                OdbcCommand command = new OdbcCommand();
                OdbcTransaction trans = null;
                OdbcConnection conn = new OdbcConnection(connectionStringMySQL);
                command.Connection = conn;

                try
                {
                    conn.Open();
                    trans = conn.BeginTransaction();
                    command.Transaction = trans;

                    // første kommando
                    command.CommandText = "UPDATE postering a, konto b SET a.BogfoeringsNummer=? where a.BogfoeringsNummer=0 and a.KontoID = b.KontoID and b.RegnskabID=? and a.UserName=? and RegnskabsPeriodeID=?";
                    command.Parameters.AddWithValue("", NextBogfoeringsnummer);
                    command.Parameters.AddWithValue("", RegnskabID);
                    command.Parameters.AddWithValue("", UserName);
                    command.Parameters.AddWithValue("", RegnskabsPeriodeID);
                    command.ExecuteNonQuery();

                    trans.Commit();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    throw e;
                }
                finally
                {
                    try { conn.Close(); }
                    catch (Exception) { }
                }
            }
            else // Hvis man ikke kan få låsen. Prøv også at slippe den, hvis man nu selv har den (pga. en evt. tidligere programfejl)
            {
                throw new Exception("En anden bruger var ved at bogføre. Prøv igen.");
            }
        }
        finally
        {
            ReleaseBogfoeringsLaasForRegnskab(UserName, RegnskabID); // Man kan kun slippe låsen, hvis ens navn står på den.               
        }
    }

    public bool GetBogfoeringsLaasForRegnskab(String UserName, Int32 RegnskabID)
    {
        int succeeded = 0;

        OdbcConnection conn = new OdbcConnection(connectionStringMySQL);
        OdbcCommand command = new OdbcCommand("UPDATE Regnskab SET DerBogFoeresNuAf=? Where RegnskabID=? and DerBogFoeresNuAf is null;", conn);
        command.Parameters.AddWithValue("", UserName);
        command.Parameters.AddWithValue("", RegnskabID);

        try
        {
            conn.Open();
            succeeded = command.ExecuteNonQuery();
        }

        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            try { conn.Close(); }
            catch (Exception) { }
        }

        return (succeeded == 1);
    }

    public bool ReleaseBogfoeringsLaasForRegnskab(String UserName, Int32 RegnskabID)
    {
        int succeeded = 0;

        OdbcConnection conn = new OdbcConnection(connectionStringMySQL);
        OdbcCommand command = new OdbcCommand("UPDATE Regnskab SET DerBogFoeresNuAf=null Where RegnskabID=? and DerBogFoeresNuAf=?;", conn);
        command.Parameters.AddWithValue("", RegnskabID);
        command.Parameters.AddWithValue("", UserName);

        try
        {
            conn.Open();
            succeeded = command.ExecuteNonQuery();
        }

        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            try { conn.Close(); }
            catch (Exception) { }
        }

        return (succeeded == 1);
    }

    // OBS: Her kan flere brugere/processer komme ind på forskellige tidspunkter og læse forskellige værdier. Tjek først låsen på regnskabet.
    public Int32 MaxBogfoeringsnummer(Int32 RegnskabID)
    {
        int maxnummer;
        OdbcConnection conn = new OdbcConnection(connectionStringMySQL);
        OdbcCommand command = new OdbcCommand("SELECT MAX(a.BogfoeringsNummer) as maxnummer FROM postering a, konto b where a.KontoID = b.KontoID and b.RegnskabID=?", conn);

        command.Parameters.AddWithValue("", RegnskabID);
        OdbcDataReader reader = null;

        try
        {
            conn.Open();
            reader = command.ExecuteReader();
            reader.Read();
            maxnummer = Convert.ToInt32(reader["maxnummer"]);
        }
        catch
        {
            throw;
        }

        finally
        {
            try { conn.Close(); }
            catch (Exception) { }
            try { reader.Close(); }
            catch (Exception) { }
        }

        return maxnummer;
    }

Dette er  arne's show. Jeg er blot spændt på om, han har kommentarer til din løsning:)

btw: hvis du vover dig ud i typed datasets, så er der en god artikel her :http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

Jeg ved ikke om der er så meget at sige.

Vi har tidligere set transaction isolation level have en effekt. Vi har ikke nær informtaion
nok til at sige hvorfor det ikke virkede da du testede. Alt kan indeholde bugs, men jeg tvivler
lidt på at der er tale om en bug her. Det er ret basal funktionalitet.

Din lås er OK. Du kan overveje at indsætte et tidsstempel også og så definere at en
lås kun er gyldig i f.eks. X minutter. Huske at finally ganske vist udføres i tilfælde
af en exception, men den udføres ikke hvis strømmen forsvinder fra computeren !

Selvom du ikke kan bruge eller allerede har brugt auto_increment i din tabel, så kan man
lave en hjælpe tabel med kun et auto_increment felt i til at generere en sekvens af værdier.

og et svar

Tak skal du have, arne v. Neoman, du siger til, hvis du ville have haft nogle af pointsne?