SQL Server locking up

[chrissparkle]

I have two pages on my website which for some reason just completely lock up. One is worse than the other, it's my sign up page. The proc is below:

declare @regionnamt varchar(40)

set @regionnamt = (Select FullRegName from Regions Where RegionID = @Region)


Insert Into Members
(Country, Region, Age, Looking, HAge1, HAge2, LAge1, LAge2, Nickname, DatingSex, FriendshipSex, Gender, DateJoined, Updated, Title, Verify, Online, RegionN)
Values 
(@Country, @Region, @Age, @Looking, @HAge1, @HAge2, @LAge1, @LAge2, @Nickname, @DatingSex, @FriendshipSex, @Gender, getdate(), getdate(), @Title, 1, getdate(), @regionnamt)
		
SELECT @@IDENTITY AS RecentMemberID
DECLARE @find int
SET @find = @@IDENTITY
		
                           Insert Into CheckIt
		(MemberID, Unread, Smiles)
		Values 
		(@find, @UnRead, @Smiles)
   		  Insert Into MainProfile
		(MemberID, Birthday)
		Values 
		(@find, @Birthday)
 		 Insert Into Opinions
		(MemberID)
		Values 
		(@find)
		 Insert Into MembersControl
		(MemberID, FirstName, Surname, EmailAddress, ThePassword, Country, wfrom)
		Values 
		(@find, @Firstname, @Surname, @EmailAddress, @ThePassword, @COuntry, @wfrom)
GO

It is really strange because I can't see anything wrong with the proc. Does SQL not like too many inserts? The tables I'm inserting into are getting read a lot using SQL (with nolock) queries - am I doing something wrong there? Is there another way to write this code so that it doesn't cause problems? It only seems to be happening when the website is really busy.

 

[bborissov]

Change:

SELECT @@IDENTITY AS RecentMemberID
DECLARE @find int
SET @find = @@IDENTITY

to:

DECLARE @find int
SET @find = SCOPE_IDENTITY()
SELECT @find AS RecentMemberID

and try again.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

Borislav is right that you should change to scope_identity. You should never use @@identity as it will create data integrity problems if you ever add a trigger that inserts into another table with an identity column. Sinc eyou can;t predict at design time if that will happen later - it is best to avoid @@identity altogether. That may be casuing your problem if it is tryiong to insert into a table with a Fk relationship and the identity it has pulled is not one inthe main table becasue it grabbed one from the table inserted to on the trigger. Even worse is the case where it does the insert because then you have records that relate to the worng record. For instance suppose you insert a record with an indentity of 100. The trigger inserts into an audit table that has an idnetity of 1000. Then @@identity will return 1000 and the insert to the relted table will fail becasue 1000 doesn't yet exist inthe main table. If the audit table trigger inserts a record with an identity of 12 (say you just started auditing) then that insert will go through and suddenly customer 12 will have customer 100's order. As you can guess this is a nightmare to clean up. Best to avoid it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[chrissparkle]

Thanks - I've made this change. My website is a ColdFusion based site and I've just done a stack trace on my problem pages. I found this:

at java.io.BufferedOutputStream.flush(Unknown Source)
- locked <0x16db3288> (a jrun.servlet.jrpp.JrppBufferedOutputStream)
at jrun.servlet.jrpp.JrppOutputStream.flush(JrppOutputStream.java:45)
at sun.nio.cs.StreamEncoder$CharsetSE.implFlush(Unknown Source)
at sun.nio.cs.StreamEncoder.flush(Unknown Source)
- locked <0x16e70978> (a java.iutputStreamWriter)
at java.iutputStreamWriter.flush(Unknown Source)
at java.io.PrintWriter.flush(Unknown Source)
- locked <0x16e70978> (a java.iutputStreamWriter)

Down the bottom there you will notice the "-locked" - it all looks greek to me. Do those locked messages indicate anything? Or do they confirm the problem with the insert statement?