Comment gérer plusieurs transactions SQL via différents threads .net (c #)

.net c# database sqlbulkcopy sqltransaction

Question

J'ai la méthode suivante pour l'insertion en bloc des données dans les tableaux. D'abord, mon code renseigne les données dans les tables de données et les insère dans les tables correspondantes à l'aide des claas SqlBulkCopy du .net.

J'ai l'obligation que les données soient insérées dans toutes les tables ou aucune d'elles. Pour cela, j'ai utilisé la classe SqlTransaction du .net.

Le scénario est, plusieurs threads exécutent le bloc de code suivant en même temps.

 public void Import()
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {

                SqlTransaction sqlTrans =null;
                try
                {
                    sqlConnection.Open();
                    sqlTrans = sqlConnection.BeginTransaction(IsolationLevel.Serializable)


                    SqlCommand cmd = sqlConnection.CreateCommand();
                    cmd.CommandText = "select top 1 null from lockTable with(xlock)";
                    cmd.CommandTimeout = 3600*3;
                    cmd.Transaction = sqlTrans;
                    SqlDataReader reader = cmd.ExecuteReader();


                    foreach (DataTable dt in DataTables)
                    {
                        ImportIntoDatabase(sqlConnection, dt, sqlTrans);
                    }

                    reader.Close();
                    sqlTrans.Commit();                    
                }
                catch (Exception ex)
                {
                    sqlTrans.Rollback();
                    throw ex;
                }
            }
        }

       private void ImportIntoDatabase(SqlConnection sqlConn, DataTable dt, SqlTransaction sqlTrans)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTrans))
            {
                bulkCopy.BulkCopyTimeout = dt.Rows.Count * 10;
                try
                {
                    bulkCopy.DestinationTableName = dt.TableName;                    
                    bulkCopy.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                   throw ex;
                }
            }
        }

Pour gérer cette concurrence, j'ai créé une table fictive (la table nommée 'lockTable') dans la base de données où réside l'autre table (les tables d'insertion en bloc). J'obtiens un verrou exclusif sur cette table factice dans SqlTransaction avec un délai de commande pouvant aller jusqu'à 3 heures.

Problème: je reçois l'exception suivante

: Impossible d'accéder à la table de destination 'Tbl1' (tbl1 est la table pour l'insertion en bloc)

suivi d'une autre exception, lors de l'annulation de la transaction dans le bloc catch

: Erreur lors de l'exécution de l'activité Le serveur n'a pas réussi à reprendre la transaction. Desc: 3a00000001. La transaction active dans cette session a été validée ou abandonnée par une autre session.

Quelqu'un peut-il m'aider pour ce comportement étrange du code. J'ai déjà beaucoup cherché sur ce sujet sur Internet, mais je n'ai rien trouvé qui puisse m'aider.

Réponse acceptée

J'ai résolu mon problème.

Voici les modifications que j'ai apportées à ma méthode d'importation

public void Import()
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlTransaction sqlTrans = sqlConnection.BeginTransaction())
                {
                    try
                    {                       
                        SqlCommand cmd = sqlConnection.CreateCommand();
                        cmd.CommandText = "select top 1 null from lockTable with(xlock)";
                        cmd.CommandTimeout = LOCK_TIME_OUT;
                        cmd.Transaction = sqlTrans;
                        SqlDataReader reader = cmd.ExecuteReader();


                        foreach (DataTable dt in DataTables)
                        {
                            ImportIntoDatabase(sqlConnection, dt, sqlTrans);                            
                        }

                        reader.Close();
                        sqlTrans.Commit();                        
                    }
                    catch (Exception ex)
                    {
                        sqlTrans.Rollback();
                        throw ex;
                    }
                }
                sqlConnection.Close();
            }
        }

Réponse populaire

En importation (DataTable dt dans DataTables) ne sera pas thread-safe.

sqlConnection a déjà un lecteur actif dans Import, de sorte que la connexion ne peut pas être utilisée dans ImportIntoDatabase.

Echo smp - si vous verrouillez une table, alors pourquoi utiliser plusieurs threads?

Si vous souhaitez générer les entrées alors que les insertions SQL ont lieu, utilisez une méthode Asynch telle que SqlCommand.BeginExecuteReader. Vous obtenez asynchrone sans la surcharge d'un fil. Et les DataTables sont relativement lents. J'insère à l'aide de TVP et d'objets légers. La fragmentation des index est un facteur important dans les performances des insertions. Si possible, insérez un ordre dans l'ordre de l'index clusterisé. La boucle est une entrée de construction simple, attendez asynch, exécutez asych. Ou bien, l’entrée de construction peut être lue à partir d’une file d’attente. Les insertions SQL dans les mêmes tables ne vont généralement pas aller plus vite en parallèle. Mon expérience est ordonnée des inserts de série, sans intervalle de temps entre inserts.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi