How to avoid Inserts Simultaneos

Asked

Viewed 49 times

2

In a sales system I realize a INSERT registering the cover of the order (order without the items) soon after the cover registration SELECT MAX to return the last id inserted to use this id as Foreign key when inserting items in the items table.

INSERT INTO CAPAPEDIDO VALUES (123,GETDATE());

SELECT MAX(CodPedido) AS LastID FROM CAPAPEDIDO;

INSERT INTO PEDIDO_ITEM(CodPedido,CodProduto,Qtd,VrUnit,TotItem) VALUES ($lastId ,$codProduto,$qtdProduto,$valorProduto,$totItem);

this is exemplified the process I use. the doubt is if there are several requests happening simultaneously there would be conflict in Lastid no 2 SELECT MAX? the items could be registered in another order cover? thinking of more than 3,000 orders at the same time.

what would be the solution?

help! thanks

php sql-server

  • See if this helps https://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted

1 answer

3


Paul, what you question can really occur and is due to competition of proceedings.

In SQL Server there are some solutions; one of them involves the use of the function SCOPE_IDENTITY:

-- código #1
declare @LastID __;
BEGIN TRANSACTION;

INSERT into CAPAPEDIDO (col1, col2) values (123, current_timestamp);
set @LastID= scope_identity();

INSERT into PEDIDO_ITEM (CodPedido, CodProduto, Qtd, VrUnit, TotItem) 
     values (@LastId, $codProduto, $qtdProduto, $valorProduto, $totItem); 

COMMIT;

Browser other questions tagged php sql-server

You are not signed in. Login or sign up in order to post.