MSSQL Non table locking INSERT mechanism

Scruff

Hi,

Here's the sceanrio:

Have 2 tables (A,B) in a database that have i need to insert data into. Tables are related in so far as one entry in table A can have many corresponding entries in table B.
Table A has an automaically generated primary key for each new record that is entered. (basic single increment int).
Before you can enter record into Table B you need to know the new primarykey value that was generated by the insert into table A.

Thats all fine BUT i need a non locking mechanism to insert and return into Table A so i can get on with inserting into Table B.

Here's an example of what i'm currently using, its a much simpler form of the stored procedure but the mechanism is the same:


DECLARE @ID AS INTEGER

BEGIN TRANSACTION

BEGIN
INSERT INTO A(ColA, ColB, ColC, ColD, ColE) VALUES('A','B','C','D','E')

@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
@ERROR
PRINT 'An Error Occurred inserting data into the Table A, Transaction rolledback'
END
ELSE
SET @ID = SCOPE_IDENTITY()


INSERT INTO B(ColA, ColB, ColC) VALUES(@ID, 'A', 'B')

@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
@ERROR
PRINT 'An Error Occurred Table B, Transaction rolledback'
END
ELSE
END

COMMIT TRANSACTION

As i said, This causes a lock on TableA (and a transaction lock on B as well i think). it totally locks out the TableA and no other query can execute till it has finished and released the table(s).
This is particularly a problem when there are other process trying to query or update the tables as well and there are 100's or such inserts.

Anyone any ideas? Would greatly appreicate any help.
Ta.
Scruff.

bonkey

Originally posted by Scruff

Thats all fine BUT i need a non locking mechanism to insert and return into Table A so i can get on with inserting into Table B.

Dont use a transaction, and if you ever need to "roll back", just do a delete on the relevant row in A instead. If you dont want gaps in your identity values, and expect failures of this nature quite often, then dont use an identity column - there are a heap of other options as well.

it totally locks out the TableA and no other query can execute till it has finished and released the table(s).

OK, but the query that you're running here should be blisteringly fast. Its two inserts in a transaction. That should execute in a matter of milliseconds at most. Is it really affecting your performance that much???

Would greatly appreicate any help.
[/B]

Well, if you ditch the use of an IDENTITY field, and find an alternate mathod of generating your IDs, I think you may resolve the concurrency issue....but like I said...holding a lock on A for as long as it takes to do one insert on B seems like such a tiny overhead....

jc

Talliesin

Originally posted by bonkey
Dont use a transaction, and if you ever need to "roll back", just do a delete on the relevant row in A instead.

I wouldn't avoid transactions unless I had a very good reason. The method of creating rollback you describe is just a poor form of transaction which is going to be less reliable and/or less efficient than what you get for free. It seems particularly optimistic to avoid transactions in code which we know is run by many concurrant connections.

I agree that it seems strange that this is causing much difficulties. Are these inserts complicated (perhaps because of triggers)?

Using a locking hint to ask for row-level locking might sort it all out:

INSERT INTO A with ROWLOCK
(ColA, ColB, ColC, ColD, ColE)
VALUES('A','B','C','D','E')

For instance should prevent locking affecting any other transactions that don't need a lock on that row (in particular concurrant execution of the same procedure).

Locking hints aren't something to get into lightly, though this seems like a case where it would have a place. Transaction isolation levels are also worth a look.

bonkey

Originally posted by Talliesin
I wouldn't avoid transactions unless I had a very good reason.

Well, I would consider locking and blocking as a result of using transactions as a very good reason Besides...I'm not suggesting that transactions be ditched - I'm suggesting that a single transaction spanning both operations may not be the most desirable option here.

Lets not forget that standard INSERT statements run inside an implicit transaction anyway - as do most data-manipulation statements.

The method of creating rollback you describe is just a poor form of transaction which is going to be less reliable and/or less efficient than what you get for free.

Not so...it allows me to discard the lock on my row in table A as soon as the insert on that table is done.

There may be other implications from this, but these only serve to highlight the fact that what I'm describing is significantly different in behaviour.

Using a locking hint to ask for row-level locking might sort it all out:

INSERT INTO A with ROWLOCK
(ColA, ColB, ColC, ColD, ColE)
VALUES('A','B','C','D','E')

For instance should prevent locking affecting any other transactions that don't need a lock on that row (in particular concurrant execution of the same procedure).

I've a sneaking suspicion that ROWLOCK will be somewhat ineffective here, because of the use of an IDENTITY value. My understanding is that while you have an INSERT pending on an Identity-based row, then the "identity generation" mechanism is also locked by your INSERT....which is my guess as to where the concurrency problem is manifesting.

I've no idea where I have that idea from, and I dont have an SQL Server installation handy to check that theory right now, but I vaguely remember it being listed as one of the potential pitfalls with Identity columns.

I still cant figure out the slowness though....triggers, as you say, are a possibility to check. As is index-corruption (as always).

jc

Talliesin

Originally posted by bonkey
My understanding is that while you have an INSERT pending on an Identity-based row, then the "identity generation" mechanism is also locked by your INSERT....which is my guess as to where the concurrency problem is manifesting.

I'm pretty sure that's incorrect, but not certain. It could be worth experimenting with UUIDs which offer the same uniqueness guarantees, but which aren't bound to the data of the table in anyway.

I still cant figure out the slowness though....triggers, as you say, are a possibility to check. As is index-corruption (as always).

Or small fill factors. If this is being inserted into that regularly then a big fill factor would speed those insertions at the cost of slowing reads.

Scruff

Cheers guys, interesting ideas here.

There seems to be some truth to bonkey's idea about the identity mechanism locking as well because i removed the identity generation form the DB side and am now generating it programatically and it seems to have helped solve the locking issue.

I negelected to mention that these stored procedures are being kicked off from javascript ASP pages on an IIS platform and so can be called in an asynchronous maner at a rate of about 20\sec. (Setting the calling mechanism to call them synchrousnously casues no problems) The IIS is the Server version 5.0 so there are no problems with maxing out the number of concurrent connections to IIS. The reason i was suspecting the a locking problem in the first place is that when i ran the equivalent code in Query Analyser i was seeing the locks, however IIS only reports the following

ASP_0147|500_Server_Error 500 Mozilla/4.0+(compatible;+Win32;+WinHttp.WinHttpRequest.5)

After cleaning the code so it doesn't cause locks in QA but i am still getting the above error in the IIS log. So this seems to be now an IIS problem as there is error handling in the ASP pages that will log any error thrown in the code.
The strange thing is that the only way that i can get the ASP to run with 100% success rate is to bypass the use of the 2 stored procedures and just do the INSERT queries on a normal DB connection.

So thanks for all your help but now i gotta ask do any of ye know why IIS might be having such difficulty with ASPs with the stored procedures?
Thanks,
Scruff