transactions with select statements

Hello everyone

I need to have a transaction with a select statement like so

trans = cn.beginTransaction

Insert into order table an order record

select unique order Id from order table

insert record 1 into orderdetails using the orderid

insert record 2 into orderdetails using the orderid

commit trans

I am of course experiencing record locking after my first insert. I cannot select records from the order table even though I need the inserted OrderId in order to insert into the orderitemst table.

I have tried switching the isolation level to readuncomitted on my transaction but sp_who2 still shows a block even with the readUncomitted Isolation level.

A foreign key on the orderitems table prevents me from inserting orderitems without a parent order id to prevent orphans. Is this a common transactional issue

Any help is greatly appreciated.

Answer this question

transactions with select statements

Eduardo D

hi,

FergusLogic wrote:

Hello everyone

I need to have a transaction with a select statement like so

trans = cn.beginTransaction

Insert into order table an order record

select unique order Id from order table

insert record 1 into orderdetails using the orderid

insert record 2 into orderdetails using the orderid

commit trans

I am of course experiencing record locking after my first insert. I cannot select records from the order table even though I need the inserted OrderId in order to insert into the orderitemst table.

if your "unique id" must be retrieved after insertion, that usually means it's a system generated value for your surrogate artificial key..

if this is the case, it will often be an "identity" value...

so you can just ask the instance for the last generated identity within the scope of the current batch (see in BOL the differences between SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY , http://msdn2.microsoft.com/en-us/library/ms190315.aspx ) using the appropriate function (SCOPE_IDENTITY is preferred ) and so you have the refereced value to be used for the successive insertions of related rows, like

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.masterT (
	Id int NOT NULL IDENTITY PRIMARY KEY,
	Data varchar(10) NOT NULL DEFAULT 'dataM'
	);
CREATE TABLE dbo.detailT (
	Id int NOT NULL IDENTITY PRIMARY KEY,
	IdMasterT int NOT NULL 
		CONSTRAINT fk_masterT$has$detailT
			FOREIGN KEY
			REFERENCES dbo.masterT (Id),
	Data varchar(10) NOT NULL DEFAULT 'dataR'
	);
GO
BEGIN TRAN
PRINT 'inserting master row';
DECLARE @id int;
DECLARE @loop int;
INSERT INTO dbo.masterT (Data) VALUES ( DEFAULT );
SELECT @id = SCOPE_IDENTITY()

SET @loop = 1
PRINT 'inserting detail rows';
WHILE @loop < 11 BEGIN
	INSERT INTO dbo.detailT VALUES ( @id, DEFAULT );
	SET @loop = @loop + 1;
END;
COMMIT
GO
SELECT m.Id, m.Data,
	d.Id, d.IdMasterT, d.Data
	FROM dbo.masterT m
		JOIN dbo.detailT d ON d.IdMasterT = m.Id;
GO
DROP TABLE dbo.detailT, dbo.masterT;
--<----------
inserting master row
inserting detail rows
Id     Data    Id     IdMasterT  Data
----------- ---------- ----------- ----------- ----------
1      dataM   1      1      dataR
1      dataM   2      1      dataR
1      dataM   3      1      dataR
1      dataM   4      1      dataR
1      dataM   5      1      dataR
1      dataM   6      1      dataR
1      dataM   7      1      dataR
1      dataM   8      1      dataR
1      dataM   9      1      dataR
1      dataM   10     1      dataR

and you resolved the referential problem..

regards

transactions with select statements

Answer this question