Oracle select for update comportement

Le problème que nous essayons de résoudre ressemble à ceci.

Nous avons une table avec plein de lignes qui représentent les cartes. L'objectif de la réservation opération consiste à attribuer une carte à un client
Une carte ne peut pas appartenir à plusieurs de nos clients
Après un certain temps (si il n'est pas acheté) une carte doit être retournée à la piscine de disponible resurces
Réservation peut être effectuée par de nombreux clients en même temps
Nous utilisons la base de données Oracle pour le stockage des données, de sorte que la solution est de travailler au moins sur Oracle 11

Notre solution est d'attribuer un statut à la carte, et de les stocker de la date de réservation. Lors de la réservation d'une carte de nous faire à l'aide de "select for update". La requête recherche les cartes disponibles et pour les cartes qui ont été réservés depuis longtemps.

Cependant notre requête ne fonctionne pas comme prévu.

J'ai préparé simplifiée de la situation pour expliquer le problème.
Nous avons un card_numbers table, plein de données - toutes les lignes non nulles les numéros d'identification.
Maintenant, nous allons essayer de verrouiller certains d'entre eux.

-- first, in session 1
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

Nous ne commettons pas la transaction ici, la ligne doit être verrouillé.

-- later, in session 2
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

Le comportement attendu est que dans les deux séances, nous n'obtenir qu'un seul, l'autre ligne qui satisfait les conditions de la requête.

Toutefois, il ne fonctionne pas de cette façon. Selon que nous utilisons à ignorer "verrouillé" partie de la requête ou non - les changements de comportements:

sans ignorer "verrouillé" - deuxième session est bloquée en attente pour l'opération commit ou rollback lors de la première session
avec "skip verrouillé" - deuxième requête est immédiatement retournée aucun résultat

Alors, après cette longue introduction de la question.

Est le genre de verrouillage comportement possible dans Oracle? Si oui, alors, que faisons-nous mal? Quelle serait la bonne solution?

OriginalL'auteur mateusz.fiolka | 2011-05-01

17

Le comportement que vous avez rencontrées pour la mise à JOUR de SAUTER VERROUILLÉ a été décrit dans ce blog note. Ma compréhension est que la clause FOR UPDATE est évalué d'APRÈS la clause where. Le SKIP VERROUILLÉ, c'est comme un filtre supplémentaire qui garantit que, parmi les lignes qui aurait été retourné, aucun n'est verrouillé.

Votre déclaration est logiquement équivalent à: trouver la première ligne de card_numbers et le retourner si il n'est pas verrouillé. Évidemment, ce n'est pas ce que vous voulez.

Voici un petit cas de test qui reproduit le comportement que vous décrivez:
```
SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------
```
Aucune ligne n'est retournée à partir de la deuxième sélection. Vous pouvez utiliser un curseur pour contourner ce problème:
```
SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2
```
Depuis que j'ai explicitement cherché le curseur, une seule ligne sera retournée (et une seule ligne sera verrouillé).

Le blog que vous mentionnez dit quelque chose de différent: Certaines lignes ne sont pas retournés non pas parce qu'ils sont verrouillés, mais parce que l'ITL fentes sur le bloc, où la ligne réside sont épuisées. L'augmentation du nombre de ITL fentes correction d'un problème dans leur cas. Mais je ne veux pas exclure que votre code proposé pourrait aider ainsi. Mon expérience est que ROWNUM <= xxx" est la partie la plus délicate.
Je suis d'accord le cas est différent, mais le comportement est le même: Oracle retourne de moins en moins de lignes que demande même si certaines lignes sont disponibles. Ma compréhension est que c'est parce que SAUTER la clause est évalué d'après la clause where. L'ajout de ITL fentes n'aide pas dans le cas que j'ai testé.
Merci, Vincent. Cette méthode semble fonctionner dans le cas que j'ai décrit. Cependant, il s'est avéré que nous avons besoin de verrouiller plusieurs lignes dans une sélection unique de mise à jour. Lors de l'utilisation de votre méthode de chargement et de quelques rangées en deux sessions distinctes, les numéros d'id de commencer à se chevaucher.
Il devrait y avoir aucun chevauchement: vous ne pouvez pas verrouiller une ligne de deux fois dans le même temps. Peut-être que vous avez trouvé un bug, ou, plus probablement, la ligne a été verrouillé, puis libéré (à la fin de la transaction), avant d'être à nouveau verrouillé. Peut-être vous pouvez créer un cas de test qui reproduit votre constat?
+1 pour le "non désiré conséquences" post de blog. Ça m'a vraiment aidé!

OriginalL'auteur Vincent Malgrat
6

Tandis que les autres réponses déjà suffisamment expliqué ce qu'il se passe dans votre base de données avec les différents SELECT .. FOR UPDATE variantes, je pense qu'il vaut la peine de mentionner que d'Oracle déconseille d'utiliser FOR UPDATE SKIP LOCKED directement et encourage à l'aide de Oracle AQ à la place:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

Nous utilisons Oracle AQ dans notre application, et je peux confirmer que, après une assez longue courbe d'apprentissage, il peut être tout à fait pratique de traiter producteurs/consommateurs directement dans la base de données

Que faire si nous ne pouvons pas utiliser Oracle AQ?
Eh bien, cette question ne peut être répondu de manière simple :-). Pourquoi ne pas créer un nouveau Débordement de Pile question avec tous les détails?

OriginalL'auteur Lukas Eder
2

Pas que Vincent la réponse est fausse, mais j'aurais conçu différemment.

Mon premier instinct est de sélectionner pour mettre à jour le premier enregistrement disponible et mis à jour l'enregistrement avec un "reserved_date". Après XXX le temps a passé et que la transaction n'est pas finalisée, mettre à jour le dossier de l'reserved_date null libérer à nouveau l'enregistrement.

J'essaie de garder les choses aussi simples que possible. Pour moi, c'est plus simple.

Vincent réponse est une bonne indication pour résoudre ce problème, donc je vais choisir comme corrects. Hovever mon vote va à vous aussi, comme nous l'avons également décidé de faire de cette façon. De plus, nous devons déplacer notre opération de connexion à partir de Java pour un PL/SQL de la procédure, pour le rendre aussi près de db que possible. De cette façon, les opérations doivent être très courts.

OriginalL'auteur user734922

Vous devez vous connecter pour publier un commentaire.