CODEWITHSUNDEEP
oracle-databaseplsql
user1220719
user1220719

Reputation: 1

Concurrency problems with stored procedure

I need help. I have the following stored procedure but I have concurrency problems. Anybody can tell me how to modify the stored procedure to avoid duplicate key error because two users make the same selection at once?. The primary key is the field IDSOLICITUD and the table os SOLIC, that is numeric and I want to insert into this table a counter each time you access it and return that value to the calling application. The structure of the table I can not change, I can not put a AutoNumber.

CREATE procedure PRC_SOLIC(FECHA IN DATE, IDTRAMITE IN VARCHAR2, ESTADO IN NUMBER, ESTADO_FECHA IN DATE, MENSAJE_ERROR IN VARCHAR2, CPROVIN IN NUMBER, CMUNICI IN NUMBER, NHABITA IN NUMBER, NDOMICI IN NUMBER, REFORIGEN IN VARCHAR2,OPERACION_TIPO IN VARCHAR2, OPERACION_CODIGO IN NUMBER, USUARIO IN VARCHAR2, FINALIDAD IN VARCHAR2, RESULTADO IN VARCHAR2,NUM OUT NUMBER) is

    v_Numero  NUMBER;

    CURSOR c_Solic is SELECT MAX(IDSOLICITUD)+1 FROM SOLIC;

BEGIN
  OPEN c_Solic;
  FETCH c_Solic INTO v_Numero;

  IF (v_Numero is NULL) THEN
    v_Numero := 1;
  END IF;

  INSERT INTO SOLIC VALUES (v_Numero, FECHA, IDTRAMITE, ESTADO, ESTADO_FECHA, MENSAJE_ERROR, CPROVIN, CMUNICI, NHABITA, 
  NDOMICI, REFORIGEN, OPERACION_TIPO, OPERACION_CODIGO, USUARIO, FINALIDAD, RESULTADO);    

  NUM := v_Numero;

  close c_Solic;


END PRC_SOLIC;

Thank you.

Upvotes: 0

Views: 516

Answers (2)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

edit based on being unable to use AutoIncrement

The following assumes you are using MySQL, for Oracle please see the other answer

Seeing as you can't change it to be an AutoIncrement you seem to have to resort to to explicitly locking the table using LOCK TABLES

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

This gives

CREATE procedure PRC_SOLIC(FECHA IN DATE, IDTRAMITE IN VARCHAR2, ESTADO IN NUMBER, ESTADO_FECHA IN DATE, MENSAJE_ERROR IN VARCHAR2, CPROVIN IN NUMBER, CMUNICI IN NUMBER, NHABITA IN NUMBER, NDOMICI IN NUMBER, REFORIGEN IN VARCHAR2,OPERACION_TIPO IN VARCHAR2, OPERACION_CODIGO IN NUMBER, USUARIO IN VARCHAR2, FINALIDAD IN VARCHAR2, RESULTADO IN VARCHAR2,NUM OUT NUMBER) is

BEGIN
  // Declare the variable which will hold the "AI" field
  DECLARE thisSolicID INT UNSIGNED DEFAULT 0;
  // Lock the table for writing
  LOCK TABLES SOLIC WRITE;
  // Get the "AI" value
  SELECT COALESCE(MAX(IDSOLICITUD),0)+1 INTO thisSolicID FROM FROM SOLIC;
  // Insert it
  INSERT INTO SOLIC VALUES (thisSolicID, FECHA, IDTRAMITE, ESTADO, ESTADO_FECHA, MENSAJE_ERROR, CPROVIN, CMUNICI, NHABITA, NDOMICI, REFORIGEN, OPERACION_TIPO, OPERACION_CODIGO, USUARIO, FINALIDAD, RESULTADO);    
  // Unlock the table
  UNLOCK TABLES;
END PRC_SOLIC;

Upvotes: 0

John Doyle
John Doyle

Reputation: 7793

Your tags are confusing, is it Oracle or MySQL? If Oracle then look into using sequences for this sort of thing. See Managing Sequences. You can then create a sequence such as:

CREATE SEQUENCE IDSOLICITUD_SEQ
START WITH 1
INCREMENT BY 1;

And then in your code:

CURSOR c_Solic is SELECT IDSOLICITUD_SEQ.NEXTVAL FROM SYS.DUAL;

Upvotes: 1

Related Questions