[2005] Lock exclusif sur une table

Bonjour,

Etant occupé à developper une petite interface pour le moment, me voici confronté à un porblème de locking.
Mon problème est le suivant :
A partir du début d'une transaction Trn1, je veux qu'une table T1 soit lockée ,aussi bien en lecture qu'en ecriture, jusque la fin de Trn1.

J'ai essayé differentes solutions, le niveau d'isolation de ma transaction est Serializable et j'utilise le hit with (ROWLOCK,XLOCK) lors de ma premiere instruction SQL.

Ayant ajouter un petit waitfor delay dans la suite de ma transaction, je peux facilement observer le lock exclusif qui est défini sur ma row de la table T1.

Cependant, il m'est toujours possible de lire la valeur de cette row !!!
Y a t'il un moyen de bloquer cela completement jusque la fin de la transaction ?

Citation:

---

J'ai essayé differentes solutions, le niveau d'isolation de ma transaction est Serializable et j'utilise le hit with (ROWLOCK,XLOCK) lors de ma premiere instruction SQL.

---

Autrement dit pour enfoncer un clou vous prenez un marteau et une presse et vous les faîtes se téléscoper pour voir ce que cela donne !

Soit vous pilotez vos transaction avec un niveau d'isolation et sans tag de verrouillage, soit vous mettez des tags de verrouillage et vous vous abstenez de de piloter une niveau d'isolation. Mais faire les deux est à la fois stupide et incompréhensible, notamment pour le moteur SQL qui essaye d'interpréter deux ordres contradictoire !

Citation:

---

Cependant, il m'est toujours possible de lire la valeur de cette row !!!

---

Et pourquoi voulez vous qu'il en soit autrement ? La pose d'un verrou exclusif ne bloque pas heureusement votre lecture. Elle bloque la lecture des autres...

Citation:

---

Y a t'il un moyen de bloquer cela completement jusque la fin de la transaction ?

---

Essayez de nous dire quel est le but de votre traitement au lei de verrouiller à l'aveugle...

A +

Voici le contexte dans lequel je veux faire ceci :
je suis en train de creer un KM (knowledge module) pour ODI (oracle data integrator) permettant de generer des OID.
Que sont les OID ? On peut le décrire comme un datatype char(16) avec une collation en binaire (latin1_general_bin).
Ils sont la représentation d'un nombre en base 36 (alphanumerique).
Ces OID sont construit de la facon suivante :
- les 2 premiers caractères corresponde à un code application.
- Les 6 suivants correspondent à la valeur "high".
- Les 8 suivants à la valeur "Low".
Ces nombres représentent une clé unique séquentielle pour un de nos datamodele et est utilisée pour différentes tables.

Nous avons une table decrite ci dessous, contenant la valeur de nos différentes séquences :

OID
appCode char(2)
high char(6)
low char(8)

(Ca me rappele les registres en assembleur avec un peu de recul...)

C'est précisement cette table que je veux bloquer, au niveau de la row ou appCode = l'app code des datas que j'upload.
Voulant un package chargeant plusieurs interfaces (plusieurs fichiers) en parralèle, je veux cependant m'assurer que la valeur de de la ligne ne soit lue que par une seule interface à la fois afin d'etre sur qu'un OID est unique dans l'ensemble des tables de mon datamodel.

Cependant, mes tests ne furent pas très concluant, ni avec les isolation level ni avec les hints de lock.

Je pense pouvoir cependant solutionner cela en gardant un transaction isolation level serializable et en ajoutant comme premiere instruction un update de la table d'OID comme ceci :

update oid set low = low, high = high where appcode = monAppCode

De cette manière je suis sur qu'une et une seule transaction aura lieu en paralllè.

Voici comment se passerait le process :
- lecture des valeurs high, low correspondant à l'appCode
- generation des oid suivants (incrementation de la sequence de 1 par objet - addition avec le row_number() )
- insertion des données dans les tables target
- mise à jour de la table OID - dernier OID inseré

Ce que je veux à tout pris éviter : 2 interfaces lisant en même temps la valeur OID et inserant dans les tables de destination 2 OID identiques.


Auriez vous des suggestions vis à vis de ce problème? (Changer le datamodele ou l'OID ne sont pas des choix possibles...)

Le plus simple est de faire ceci de la manière suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION   ... ordre SQL 1 ... IF @@ERROR GOTO LBL_ERRROR   ... ordre SQL 2 ... IF @@ERROR GOTO LBL_ERRROR   ...   ... ordre SQL n ... IF @@ERROR GOTO LBL_ERRROR   COMMIT TRANSACTION   GOTO LBL_RESUME   LBL_ERROR: ROLLBACK TRANSACTION   LBL_RESUME: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

---

C'est tout et c'est absolument garantit !

Lisez les articles que j'ai écrit sur la gestion de l'isolation des transactions.
http://www.sqlspot.com/Gerer-l-isola...nsactions.html

M'est avis que vous avez oublié de gérer EXPLICITEMENT une transaction par BEGIN TRAN... / COMMIT...
Sans doute une habitude héritée d'Oracle !

A +

Frédéric,

Merci pour cette réponse ainsi que pour le lien vers votre article.

La solution que vous proposez est effectivement celle plus ou moins en place dans notre système actuel. Cependant cela ne solutionne pas à 100% le process que je souhaite mettre en place.

Si je reprend le morceau de flow ci dessus et l'étoffe quelque peu nous aurons ceci :

- Chargement du fichier dans une table I$
- Flag des records selon insert/update/delete
DEBUT de transaction
- lecture des valeurs high, low correspondant à l'appCode
- generation des oid suivants (incrementation de la sequence de 1 par objet - addition avec le row_number() )
- insertion des données dans les tables target
- mise à jour de la table OID - dernier OID inseré
FIN de transaction
- Autres traitements

Ceci doit etre considéré comme une interface pour un fichier. Principe de developpement avec ODI.

Bien que vu la gestion des OID, nous ne puissions pas vraiment parler ici de load completement en parralèle, ce que je souhaite est de lancer toutes les interfaces (fichiers) même temps, que toutes les étapes préliminaires à la gestion des OID soient effectuées en parallèle pour ainsi réduire le temps de processing.

Ensuite utiliser une "sémaphore" - un fichier à la fois lorsque l'on process les OID pour cette partie du flow.
"Premier arrivé, premier servis".
Le lock serait relaché à la fin de la transaction permettant à un autre fichier de poursuivre son load et ainsi de suite.
Les taches préliminaires et suivants la transaction pouvant etre effectuées en parallèle.
C'est pourquoi l'implémentation de la commande d'update de la row en debut de transaction me semble une bonne solution, ce qui nous donne un process comme ceci :

- Chargement du fichier dans une table I$
- Flag des records selon insert/update/delete
DEBUT de transaction
- Update table HIGHLOW - Semaphore en qlq sorte.
- lecture des valeurs high, low correspondant à l'appCode
- generation des oid suivants (incrementation de la sequence de 1 par objet - addition avec le row_number() )
- insertion des données dans les tables target
- mise à jour de la table OID - dernier OID inseré
FIN de transaction
- Autres traitements

BAV,
Jerome

Qu'appelez vous table I$ ??? Est-cen une table ou non ? Est-ce une table temporaire et dans ce cas locale ou globales ???

Soyez précis !

A +

I$_tableName - table générée par le KM (knowledge module - procédure générique générant du code SQL) d'intégration de ODI.
I$ étant le préfixe par défaut pour de telles tables.
C'est en effet une table temporaire créée par ODI ou l'on peut effectuer différentes vérification/update...
Une table I$ est par exemple une table temporaire chargée sur base d'une jointure entre différentes sources de données (qui peuvent très bien être un fichier, une DB MS SQL Server, Oracle, MySQL, DB2 .... Ou autre. Pour plus d'info vous pouvez vous référer à la documentation ODI ou passer faire un ptit tour sur le forum de BI)
Un KM étant générique, on le réutilise généralement pour différentes interfaces suivant la même procédure.

Cependant, les opérations préalables à la transaction ne sont pas un problème en soit et pourraient être tout aussi variées.

Afin de recadrer le problème ici, il s'agit vraiment d'empecher toute interface générant des OID sur base de la table OID. Pour ce faire, je souhaite vraiment "poser un verrou" - sous forme de update ou hint sql.
Comme je ne trouve pas de hint permettant d'empecher la lecture d'une table, je vais sans doute me tourner vers la solution "sémaphore" utilisant le statement update.

Bien a vous,
Jerome

dans ce cas :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION   - Chargement du fichier dans une table I$ - Flag des records selon insert/update/delete - Update table HIGHLOW - Semaphore en qlq sorte. - lecture des valeurs high, low correspondant à l'appCode - generation des oid suivants (incrementation de la sequence de 1 par objet - addition avec le row_number() ) - insertion des données dans les tables target - mise à jour de la table OID - dernier OID inseré COMMIT TRANSACTION SET TRANSACTION ISOLATION LEVEL READ COMMITTED - Autres traitements

---

A +