[SQL Server] Transaction geeft deadlock error

[Verwijderd]

Mijn doel is het volgende:
1. Een uniek random nummer genereren met een x aantal digits
2. Deze in een database zetten en hangen aan een unieke ID

Ik heb hier een stored procedure van gemaakt. Wat deze doet is een transaction openen met de hoogste isolation level (Serializable), een unieke nummer bedenkt, kijkt of deze al bestaat en zo ja een nieuwe code maken, en zo niet een nieuwe nummer genereren. De tabel kan inderdaad volraken, dit los ik op en valt hier verder buiten. Ik wil echt een uniek nummer, dus niet een automatisch ophogend getal. De betreffende tabel bevat dus een kolom de code en nog wat data. Deze kolom met code is gemarkeerd als uniek.

De stored procedure die dit allemaal keurig doet is geschreven in C#. Deze doet alleen wat hierboven beschreven staat dus laat je niet afschrikken Ik heb speciaal overal commentaar bijgezet zodat iedereen het kan volgen.

C#:

[SqlProcedure]
public static void GenerateConnectCode(int digits, Guid ayudonId, out int code)
{
    // Context verbinding gebruiken
    using (SqlConnection connection = new SqlConnection("Context connection = true"))
    {
        // Verbinding openen
        connection.Open();
        try
        {
            // Hoogste en laagste waarde vaststellen. Met 3 digits dus bereik van 100 tot 1000
            Random random = new Random();
            int minValue = (int)Math.Pow(10, digits - 1),
                maxValue = (int)Math.Pow(10, digits);

            // Transaction beginnen met hoogste isolation level
            using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
                try
                {
                    int randomCode;

                    // Loop die de code gaat genereren en kijken of die al bestaat
                    while (true)
                    {
                        // Random code maken
                        randomCode = random.Next(minValue, maxValue);

                        // Kijken of deze bestaat
                        using (SqlCommand command = new SqlCommand(
                            "SELECT COUNT(Id) FROM [Connect Codes] WHERE Code = @Code;", connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Code", randomCode);
                            
                            // Als Count(Id) 0 is dan bestaat deze nog niet en gaan we verder
                            if ((int)command.ExecuteScalar() == 0)
                                break;
                        }
                    }

                    // Code in de database zetten
                    using (SqlCommand command = new SqlCommand(
                        "INSERT INTO [Connect Codes] (Code, [Ayudon Id]) VALUES (@Code, @AyudonId);", connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Code", randomCode);
                        command.Parameters.AddWithValue("@AyudonId", ayudonId);
                        command.ExecuteNonQuery();
                    }

                    // Transaction beëindigen
                    transaction.Commit();

                    code = randomCode;
                }
                catch
                {
                    // Er ging iets mis => rollback
                    transaction.Rollback();
                    throw;
                }
        }
        finally
        {
            // Altijd verbinding sluiten
            connection.Close();
        }
    }
}

Nu heb ik uiteraard een stress test programmaatje gemaakt die duizenden keren deze stored procedure aanroept. Dit werkt allemaal prima. Totdat ik dit stressen vanuit meerdere threads ga doen. Ik krijg dan namelijk een SqlException met de boodschap dat er een deadlock is ontstaan. Ik kan dit niet plaatsen; ik gebruik expliciet de hoogste isolation level die juist deadlocks moet voorkomen.

Heeft iemand een oplossing voor dit probleem? Of zijn er betere manieren om dit aan te pakken?

Als ik slechts 3 digits zou gebruiken had ik alle 899 mogelijkheden in de tabel gezet en een UPDATE uitgevoerd met een WHERE clause en gekeken of deze succesvol was (of er rows affected waren) in plaats van de huidige aanpak. Echter, ik wil beginnen met vijf digits en later naar zes of zeven kunnen indien nodig. Miljoenen records erin zetten lijkt me dan niet nodig. Maargoed, dat is de kern van het probleem ook niet. Ik wil weten waarom deze isolation level niet doet waar hij voor gemaakt is. Bij voorbaat dank.

/edit
SQL Server heeft trouwens gelijk wat betreft die deadlock even voor de duidelijkheid. De threads die de database stressen moeten inderdaad op elkaar wachten. SQL Server hanteert een time-out van 5 seconden ofzo en geeft dan de deadlock exception. De vraag is dus hoe deze deadlock ontstaat. Waarom wachten meerdere threads in bovenstaand voorbeeld op elkaar?

[ Voor 3% gewijzigd door Verwijderd op 04-11-2007 19:07 ]

[Verwijderd]

Wellicht dat je hier wat aan hebt:
http://msdn2.microsoft.com/en-us/library/ms177433.aspx
Waarschijnlijk is het op te lossen door van de 2 queries 1 te maken dmv. een subquery.

[Verwijderd]

Verwijderd schreef op zondag 04 november 2007 @ 18:57:
Wellicht dat je hier wat aan hebt:
http://msdn2.microsoft.com/en-us/library/ms177433.aspx
Waarschijnlijk is het op te lossen door van de 2 queries 1 te maken dmv. een subquery.

Bedankt voor de reactie. Hoe bedoel je de queries bij elkaar voegen? Je kunt een INSERT toch niet met SELECT combineren?

De exception valt trouwens op het inserten van de code. Blijkbaar kunnen twee threads tegelijkertijd die Insert uitvoeren, terwijl ze allebei in een transaction zitten met de hoogste isolation level. Dit mag toch niet? Dat is toch het hele principe van IsolationLevel.Serializable?

Een lagere isolation level (bijv. repeatable read) geeft geen deadlocks, maar dan krijg je na een tijdje een exception dat een code al bestaat omdat een andere thread diezelfde toevallig ook bedacht had en nét iets eerder geinsert had.


/edit
Het probleem is opgelost met wat grof geweld. Zelfde code verder, alleen de table hint TABLOCKX in de SELECT. Dat hij dus de tabel exclusief moet locken tot het einde van de transactie. Blijkbaar deed hij dit eerst niet. Dit lijkt mij juist de hele raison d'être van Isolation.Serializable, maargoed, Bill Gates had er vast een reden voor. Isolation.Serializable heb ik dus weggehaald.

Het ziet er nu als volgt uit:

C#:

while (true)
{
    randomCode = random.Next(minValue, maxValue);

    using (SqlCommand command = new SqlCommand(
        "SELECT COUNT(Id) FROM [Connect Codes] (TABLOCKX) WHERE Code = @Code;",
        connection, transaction))
    {
        command.Parameters.AddWithValue("@Code", randomCode);
        if ((int)command.ExecuteScalar() == 0)
            break;
    }
}

using (SqlCommand command = new SqlCommand(
    "INSERT INTO [Connect Codes] (Code, [Ayudon Id]) VALUES (@Code, @AyudonId);",
    connection, transaction))
{
    command.Parameters.AddWithValue("@Code", randomCode);
    command.Parameters.AddWithValue("@AyudonId", ayudonId);
    command.ExecuteNonQuery();
}

[ Voor 40% gewijzigd door Verwijderd op 04-11-2007 19:51 ]