Une autre solution est d'utiliser des verrous optimistes en activant le READ COMMITTED SNAPSHOT au niveau de la base....
A +
Une autre solution est d'utiliser des verrous optimistes en activant le READ COMMITTED SNAPSHOT au niveau de la base....
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Voici en quelques minutes un programme C# illustrant ce qui t'arrive.
Rien d'anormal.
C'est ton programme qui gère mal le lock.
Soit tu fais un ExecuteNonQueryAsync() avec tout ce que ça implique, soit ton timer lance un thread dédié à chaque insert (qui utilise sa propre connexion et sa propre commande) sans attendre la fin du thread.
On ne jouit bien que de ce qu’on partage.
C'est encore plus simple que je ne pensais.
Remplace juste "ExecuteNonQuery" par "ExecuteNonQueryAsync" (et rien d'autre).
Le timer ne se met donc plus en pause, car n'attends pas la fin du INSERT pour rendre la main.
Et dès que le verrou est libéré, tous les INSERT passent bien tranquillement. Et ceci alors même que j'utilise la même connexion et le même objet command !
Code csharp : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace TestTimerSQL { public partial class Form1 : Form { SqlConnection cnx = new SqlConnection("Server=.;Database=SandBox;Trusted_Connection=True;"); SqlCommand cmd; SqlParameter p; public Form1() { InitializeComponent(); cmd = cnx.CreateCommand(); cmd.CommandText = "insert into matablequivabien (nom) values (@nom)"; p = cmd.Parameters.Add("@nom", SqlDbType.VarChar); cnx.Open(); timer1.Interval = 1000; } private void timer1_Tick(object sender, EventArgs e) { p.Value = DateTime.Now.ToString("yyyyMMdd HH:mm:ss fff"); cmd.ExecuteNonQueryAsync(); textBox1.Text += string.Concat(p.Value, "\r\n"); } private void checkBox1_CheckedChanged(object sender, EventArgs e) { timer1.Enabled = checkBox1.Checked; } private void Form1_Load(object sender, EventArgs e) { } private void Form1_FormClosing(object sender, FormClosingEventArgs e) { if (timer1.Enabled) { timer1.Stop(); } cnx.Close(); } } }
Attention cependant : s'il se produit des erreurs lors des INSERT, ou si l'application est fermée avant que le lock ne rendre la main, tu vas avoir des données perdues !
(mais bon, ça sera pas pire qu'actuellement)
On ne jouit bien que de ce qu’on partage.
Nous sommes bien d'accord, ce n'est pas SQLServer qui envoi ce message.
Mon problème est que le SELECT bloque les écritures, et donc mon applicatif qui attend une réponse du serveur avant d'envoyer une nouvelle demande d'INSERT, en synchrone, n'envoie ce nouvel INSERT que plusieurs secondes plus tard.
Et, donc, en asynchrone plante avec le message en question.
Donc l'assynchrone n'est pas la solution.
Je voudrais que mon SELECT ne bloque pas les écritures.
Pas changer assiettes pour fromage.
Non mais en fait, j'ai déjà essayé. Ca lance le query en asynchrone. Sauf que mon appli le fait en Delphi avec les composants Firedac.
Ca ne résout pas mon problème.
Je me retrouve dans le cas du plantage avec le msg pré-cité.
Attention, il faut que le SELECT dure plus longtemps que la fréquence d'écriture, d'envoi des INSERT, pour mettre le problème en lumière.
Pas changer assiettes pour fromage.
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
A mon avis, si ça plante, c'est parce que comme moi, tu utilises la même connexion et probablement aussi la même commande.
Utilise une connexion dédiée et une commande dédiée à chaque INSERT.
Avec le pooling de connexion côté SQL Server ça ne changera rien aux performances, mais devrai éviter à Firedac de se prendre les pieds dans le tapis.
Accessoirement, SQLPro a tout à fait raison à propos de Delphi : c'est (était ?) un très bon langage pour apprendre, comme le Pascal ou Ada.
Mais il est aussi complètement dépassé à l'heure actuelle.
On ne jouit bien que de ce qu’on partage.
Par contre, il est possible que le problème vienne de la façon d'utiliser les composants. C'est pour ça que j'ai essayé deux collections différentes de composants. Le résultat est identique.
De toute façon je lance le SELECT dans SQLServer Studio avec l'isolation SNAPSHOT.
Il ne devrait donc pas bloquer les écritures.
Pas changer assiettes pour fromage.
Je voulais envoyer "une démo" mais ça ne sert à rien, puisqu'il faut au-moins une table avec 17 millions de lignes.
Pas changer assiettes pour fromage.
J'ai bien re-regardé ta vidéo StringBuilder, et justement, ça démontre que c'est mon problème qui n'est pas compris :
En fait tu le prends à l'inverse :
1- tu bloques la table pour faire freezer les écritures. (c'est normal)
2- ta requête sur la table non lockée est instantanée, et ne feeze donc pas les écritures. (c'est normal aussi)
Et il n'y a donc pas de problème, je suis d'accord.
Mon problème :
1- si je bloque la table, ça freeze les écritures. (c'est normal)
2- ma requête sur la table non lockée dure plus de 10 secondes, et feeze les écritures. (je ne sais pas si c'est normal, mais ça m'embête )
Du coup, je me demade s'il n'y aurait pas une limite en nombre de lignes dans une table au-delà de laquelle SQLServer ne peut plus assurer les SELECT normalement.
Pas changer assiettes pour fromage.
Euh, la limite doit être de l'ordre de quelques milliards de milliards... probablement plus.
Soit la requête est très mal optimisée.
Soit les index sont très mal placés.
Soit la requête fait vraiment des calculs trop complexes.
Mais imagine quand tu vas sur le site des impôts, les formules de calcul, tout ça. Ben quand tu remplis ta déclaration, le calcul de l'estimation est instantané, et pourtant y'a quelques dizaines de million de lignes, rien que dans la table des utilisateurs...
On ne jouit bien que de ce qu’on partage.
Je me pose aussi des questions de ce genre, mais ça n'explique pas le problème de fond. En SNAPSHOT, le SELECT ne devrait pas bloquer les écritures.
Demain, je vais nettoyer la table pour voir à partir de quel seuil, j'ai (ou pas) le fonctionnement attendu.
Pas changer assiettes pour fromage.
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
est-ce qu'on peut voir la tête de la requête SELECT dont il est question ?
Cette requête prend 20 secondes sur une table de 4,7 millions de lignes.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; SELECT * FROM TABLE_NAME WITH (INDEX(Index_TABLE_NAME_id_TIME)) --index sur les 2 premiers champs id et TIME WHERE TIME BETWEEN '2019-01-10 00:00:00' AND '2019-01-10 23:59:59' ORDER BY id, TIME ASC ; COMMIT TRANSACTION
Soit prêt de 2 mois d'écritures.
Il y a 4 autres tables remplies dans les mêmes conditions.
Le SELECT ci-dessus freeze les écritures dans les 5 tables.
Et le résultat des freezes dans les 5 tables (sachant que c'est un exemple portant sur des tables de 4,7 Mega lig et non sur 17,4 Mega lignes qui donne des résultats bien plus problématiques) :Pièce jointe 452423
Pas changer assiettes pour fromage.
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager