Geek asks and answers

How to find out if mysql insert is successful

I am learning MySQL transactions. I have been looking for answers to this, but they all seem to use PHP to do what I want to do. Here is an example of what I'm trying to do:

  • Start transaction
  • Update table
  • Paste into table2
  • If Insert is successful as well. then insert into table 3 and lock. b. else rollback the transaction.

I do not understand how to determine programmatically whether the insert in step 3 was successful. Of course, I can query the table and see, but I thought there was some way to use the return value, but it seems to work only when I use PHP to complete the transaction.

This is the code block for what I'm trying - it doesn't work:

begin;
start transaction;
-- attempt to reduce inventory
update store_inventory set item_qty = item_qty - 2 where id = 1; 
update store_inventory set item_qty = item_qty -1 where id = 5;

-- insert the order record and check whether it succeded
insert into store_orders (purchaser_name, purchase_date) 
values ('test user', now());
    -- if successful, do final insert and commit
if Row_Count() > 0 Then     
insert into store_inventory (order_id, inventory_id, item_qty)
values (1, 1, 2),
        (1, 2, 1);
commit;
else    -- otherwise rollback
rollback;
end if;

end;
+5
source share
4 answers

- - .

start transaction commit - . :

start transaction;
-- attempt to reduce inventory
update store_inventory set item_qty = item_qty - 2 where id = 1; 
update store_inventory set item_qty = item_qty -1 where id = 5;

-- insert the order record
insert into store_orders (purchaser_name, purchase_date) 
values ('test user', now());
insert into store_inventory (order_id, inventory_id, item_qty)
values (1, 1, 2),
        (1, 2, 1);
commit;

, .

create table foo(id int primary key);
insert into foo values (1);

, :

start transaction;
insert into foo values(2);
insert into foo values(1);
insert into foo values(3);
commit;

1, , 1 , . select * from foo;, , 2. , , , 2, ( ). , . , , 3 . , . , - , , . , , - / , , - , .

+2

, , , . .

DELIMITER $$
CREATE PROCEDURE `sample`(name VARCHAR(100))
BEGIN
    START TRANSACTION; -- Begin a transaction
    INSERT INTO `users` (`name`) VALUES name;
    IF ROW_COUNT() > 0 THEN -- ROW_COUNT() returns the number of rows updated/inserted/deleted
        COMMIT; -- Finalize the transaction
    ELSE
        ROLLBACK; -- Revert all changes made before the transaction began
    END IF
END$$
DELIMITER ;

- ( , ), InnoDB , MyISAM .

+1

, . , .
.

0

If you use java and JPA, you can use the annotation @TransactionManagement(TransactionManagementType.CONTAINER)in the bean that you use to insert. this ensures that in the event of a transaction failure, the container will undo all changes. you can google ejb 3.0 learn more about transaction management

0
source

More articles:


All Articles