Does mysql's stored procedure automatically start a transaction?

problem description

Does the stored procedure of

mysql automatically start a transaction

the environmental background of the problems and what methods you have tried

the problem is that there is a piece of code that opens the transaction, and the whole request opens the transaction. A stored procedure is called in the transaction, that is, some data is updated, and the begin,end is finished if the open transaction is not shown in the stored procedure.
this transaction contains an updated piece of data. After execution, there is another operation in the code to update the same data that has just been updated in the stored procedure. Here, a lock wait timeout occurs.

what result do you expect? What is the error message actually seen?

what I want to ask is, if a stored procedure is called to execute, if there is no transaction opened in the stored procedure, will it automatically open a new transaction? And there is no automatic submission.
causes the lock not to be obtained when manipulating the same data later.

by looking at the locks and code tests of MySQL, I found that the stored procedure did open a new transaction and hung there all the time without committing after execution.

Mysql php
Mar.28,2021

when you decide to use a stored procedure, the whole transaction is done on the MySQL side.

the main point of optimization for transaction contention is to reduce transaction lock time.

if you choose to use stored procedures, you don't have to open the transaction in the code. Deep optimization means that the transaction SQL executes on the MySQL side (stored procedure)

the following is the stored procedure for the landing of a similar second kill transaction 

-- 
DELIMITER $$ -- ;  $$
--  in    out 
-- ROW_COUNT sqldeleteinsertupdate
-- row_count 0  >0 <0 SQLsql
CREATE PROCEDURE `seckill`.`execute_seckill`
  (in v_seckill_id bigint,in v_phone bigint,
    in v_kill_time TIMESTAMP ,out r_result int)
  BEGIN
    DECLARE insert_count int DEFAULT 0;
    START TRANSACTION ;
    insert ignore into success_killed
      (seckill_id,user_phone,create_time)
      VALUES (v_seckill_id,v_phone,v_kill_time)
    select ROW_COUNT() into insert_count;
    if (insert_count = 0) THEN
      ROLLBACK;
      set r_result = -1;
    ElSEIF(insert_count < 0) THEN
      ROLLBACK;
      set r_result = -2;
    ELSE
      UPDATE seckill
      set number = number - 1
      where seckill_id = v_seckill_id
        and end_time > v_kill_time
        and start_time < v_kill_time
        and number > 0;
        SELECT row_count() into insert_count;
        if (insert_count = 0) THEN
          ROLLBACK;
          set r_result = 0;
        ElSEIF(insert_count < 0) THEN
          ROLLBACK;
          set r_result = -2;
        ELSE
          COMMIT;
          SET r_result = 1;
        END if;
    END if;
  END;
$$
-- 

DELIMITER ;

set @r_result=-3;
-- 
call execute_seckill(1003,18820116735,now(),@r_result);

-- 
select @r_result;

this belongs to the stage of concurrency optimization. Don't rely too much on stored procedures, which are generally used for simple logic

Previous: Tengine compilation error missing terminating "charracter

Next: Error while using uglifyjs to compress css

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu