Table lock caused by MysQL trigger

Question

Table lock caused by MysQL trigger

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Currently I have a problem apparently with some triggers in one of my tables as it is blocked, by doing a few "INSERTS" the table works correctly but by doing many simultaneous INSERTS (I'm using JMeter for load tests) a DeadLock appears in the table ..

In the table there are 2 types of Trigger AFTER INSERT and BEFORE INSERT in AFTER INSERT there are many things that are done and remove it temporarily, just leave the BEFORE INSERT and likewise there is blocking in the table.

Here is the trigger code:

DELIMITER 
CREATE
TRIGGER 'getNextIdTicket' BEFORE INSERT ON 'pos_doc_ticket' 
FOR EACH ROW 
BEGIN
IF (new.id_cat_producto = 0 AND new.buscaNuevoRegistro=TRUE)
THEN 
    SET new.id_doc_ticket = getNextSeq (
    new.id_cat_zona,
    new.id_cat_empresa,
    new.id_cat_tienda,
    "idTicket"
 );

END IF;
IF new.id_cat_producto>0
THEN
  SET new.id_linea = getNextSeqLine (
    new.id_cat_zona,
    new.id_cat_empresa,
    new.id_cat_tienda,
    new.id_cat_caja,
    new.id_doc_ticket
  );

   END IF ;
  END;
 DELIMITER ;

And the function you call is this:

DELIMITER
CREATE  FUNCTION 'getNextSeq'(idZona BIGINT, idEmpresa BIGINT, idTienda  
BIGINT, sSeqName VARCHAR(50)) RETURNS BIGINT(10) UNSIGNED
BEGIN
DECLARE nLast_val BIGINT;
IF idTienda > 0 THEN 
SET nLast_val =  (SELECT seq_val 
                  FROM _sequence 
                  WHERE seq_name = sSeqName);
ELSE
SET nLast_val =  (SELECT seq_val 
                  FROM _sequence 
                  WHERE seq_name = sSeqName);
END IF;
IF (nLast_val IS NULL OR nLast_val = 0)THEN
    SET nLast_val = 1;
    INSERT INTO _sequence (id_cat_zona,id_cat_empresa,id_cat_tienda,seq_name,seq_val) 
    VALUES (idZona,idEmpresa,idTienda,sSeqName,nLast_Val);
ELSE
    SET nLast_val = nLast_val + 1;
    UPDATE _sequence SET seq_val = nLast_val 
    WHERE seq_name = sSeqName;
END IF;
RETURN nLast_val;
END
DELIMITER ;

As you can see I use it to obtain a consecutive id to insert it in the table before the INSERT but when I do around 100 simultaneous requests when arriving at the INSERTS there are several blockages of the table by which not all the INSERTS they end successfully.

I hope you can help me and see what solution may exist.

mysql java spring mybatis

asked by Jonathan López Ramírez 04.04.2018 в 19:02

source

2 answers



                    
        

         
                            How to implement correctly Comments Social Facebook Plugin?
                                        I have a problem wanting to read a data by id =, it does not show me the email that I keep in that id

score 0 · Answer 1

In MySQL there are no such sequences.

The problem is that your trigger counts the number of records that there are, change your code a bit in the following:

the table would do it this way:

CREATE TABLE '_sequence' (
      'seq_name' varchar(50) NOT NULL,
      'seq_val' bigint(20) NOT NULL
      ADD UNIQUE KEY '_sequence' ('seq_name');

now would change your sequencia function:

DELIMITER 
CREATE  FUNCTION getNextSeq(_seq_name VARCHAR(50)) 
RETURNS BIGINT(20)
BEGIN
DECLARE nLast_val BIGINT;

SET nLast_val = (SELECT _sequence from seq_name = _seq_name);

IF (nLast_val IS NULL OR nLast_val = 0)THEN
    SET nLast_val = 1;
    INSERT INTO _sequence (seq_name, seq_val) values (_seq_name,nLast_val);
ELSE
    UPDATE _sequence  SET seq_val= @nLast_val = seq_val + 1 
    WHERE  seq_name= _seq_name
END IF;
RETURN nLast_val;
END 
DELIMITER ;

Once this is done, I should just call the function getNextSeq ("mytable") and it will return the id of the current table and in the trigger for each table I should have my id

|seq_name | seq_val |
| zona    | 3       |
| empresa | 32      |
| tienda  | 12      |
| tickets | 37      |

score 0 · Answer 2

Hello, I have already managed to make all of the 100 requests finish correctly now the problem lies in adding the AFTER INSERT trigger, which is where many things are done (to other tables) now I think it's because of that trigger that happens again blocking a table in the same way I share the code of that trigger:

Could it be that having all this code in a trigger is too much?

DELIMITER CREATE /*!50017 DEFINER = 'root'@'localhost' / TRIGGER tr_movimientos_A_INSERT AFTER INSERT ON% tr_movimientos FOR EACH ROW BEGIN ### Author: Luis Roberto Muñiz Muñiz ### Creation date: 2015-10-04 ### version 1.10 ### Change history


                                    
                                        answered by 
                                        

                                                                                  04.04.2018 в 23:22