How do I insert records into 2 SQL tables sharing a dependency when I don't commit until the end?

Question

I keep finding myself in this situtation:

I have an ASP.NEt 2.0 app. I have to insert into 2 tables in SQL. There is a dependency between the tables. I insert a record the first using a transaction. Then I move onto the 2nd table. But, because the first isn't committed yet the second one throws a error.

Answer 1

Why don't you create a STORED PROCEDURE? In this way, with A SINGLE transaction you invoke the STORED PROCEDURE, that makes first the INSERT into the parent table, and then (inside the same transaction) inserts into the child table.

CREATE OR REPLACE PROCEDURE GOOFY
IS
BEGIN
INSERT INTO MY_PARENT_TABLE VALUES (1111, 'ALPHA');
INSERT INTO MY_CHILD_TABLE VALUES (1111, 'BETA');
END;
/

All you have to do is to invoke GOOFY from ASP.NET 2.0: the two INSERTS belong to the same transaction, and you can decide to COMMIT or ROLLBACK.

Answer 2

You must not be using the same connection when you modify each table. Try using the same connection and a single transaction around all of your changes to both tables. When in a transaction you see your changes and no one else will see them (unless they force dirty reads) until you COMMIT them.