February 4, 2003 at 9:42 am
I have a Stored Proc which handles a login, I'm select the MAX ID and returning that if its a new record or Inserting the new row if they dont exist.
Currently I'm getting deadlock problems. The SP has the following IF statement within it:
IF EXISTS(SELECT ID,Email,PW FROM tbl_TEST WHERE Email = @strEmail AND PW = @strPassword)
BEGIN
UPDATE tbl_TEST SET ApplicationType = @strApplicationType, TableType= @strTableType WHERE Email = @strEmail AND PW = @strPassword
SELECT * FROM tbl_TEST WHERE Email = @strEmail AND PW = @strPassword
SET @intReturnVal = 1
RETURN
END
ELSE
BEGIN
SELECT @intMAXID = MAX(ID) + 1 FROM tbl_TEST
INSERT INTO tbl_TEST (
ID,
PW,
)
VALUES(
@intMAXID,
@strPassword,
@strEmail
)
SET @intReturnVal = 0
SET @intNewMaxID = @intMAXID
RETURN
END
The SQL queries all talk to the same table and it is in a flat table. Can you suggest how I update this code to aviod getting Deadlocks?? (Proper use of TRANS ??)
February 4, 2003 at 10:03 am
You say the sp has the following if statement - What's the rest of the sp like?
John Zacharkan
John Zacharkan
February 4, 2003 at 10:15 am
Thats about it...just some @@ERROR handling and DECLARE at the beginning pulling in the parameters.
February 4, 2003 at 12:01 pm
I don't explitly use transacations for situations like this.
I traditionally use an Identity column instead of the Max variable, ie.
insert into table ...
set @ThisId = @@IDENTITY
-- This is what I normally do for an insert/update sproc:
Alter Procedure TOPEducation_Save
( @TOPEducationId int = null
,@TalentId int
, @DegreeType varchar(50)
, @AreaOfStudy varchar(50)
, @School varchar(50)
, @GraduationDate varchar(20)
, @UpdatedBy varchar(50)
)
As
BEGIN
set nocount on
declare @ThisId int
declare @ThisDate datetime
set @ThisDate = getdate()
set @ThisId = @TOPEducationId
if @ThisId is null
begin
insert into TOPEducation
( TalentId
, DegreeType
, AreaOfStudy
, School
, GraduationDate
, CreatedBy
, CreatedDate
, UpdatedBy
, UpdatedDate
)
values
( @TalentId
, @DegreeType
, @AreaOfStudy
, @School
, @GraduationDate
, @UpdatedBy
, @ThisDate
, @UpdatedBy
, @ThisDate
)
set @ThisId = @@Identity
end
else
begin
update TOPEducation
set DegreeType = @DegreeType
, AreaOfStudy = @AreaOfStudy
, School = @School
, GraduationDate = @GraduationDate
, UpdatedBy = @UpdatedBy
, UpdatedDate = @ThisDate
where TOPEducationId = @TOPEducationId
end
set nocount off
exec TOPEducation_Get @ThisId, null
END
John Zacharkan
John Zacharkan
February 4, 2003 at 1:25 pm
Rather than Exists and select a few columns just do a Select '1'
Secondly why a flat file, how many records in the table?
February 5, 2003 at 3:10 am
zach_john,
I need to retunr the MAXID + 1 to the .asp page as it is used within another web application. I cant use @@IDENTITY as, the ID field within this flat table is just an int data type and the table does not have a field that increments. I'm tryting to work with a table that has been poorly desgined and thats why i have to use MAXID + 1.
But when I have inserts and updates running aginst this table I occasionaly get deadlocks. I was going to wrap the SQL queries within TRANSACTIONS as well. I've had a look at BOL and other articles around Deadlocks, and they all say its the order in whiich you run your transactions and the design of the table.
I need soem sort of a fix in this particular case?
-------------
5409045121009,
How would I use Select '1' ??
The table has about 5,000 Records within it and it is growing.
February 5, 2003 at 9:50 am
Instead of checking first than updating - why not update check for error not found and then insert.
Also, Insert the row immediately, instead of first selecting, then inserting. The way it's written you would need begin tran , updlock, commit tran. This way is much more efficent.
INSERT INTO tbl_TEST
SELECT @intMAXID = MAX(ID) + 1 FROM tbl_TEST
,
@strPassword,
Email,
@strEmail
Good look
John Zacharkan
John Zacharkan
February 11, 2003 at 9:02 am
1) What is meant by SELECT 1 is: IF EXISTS (SELECT 1 FROM... WHERE...)
2) Why can't you do something like this:
UPDATE tbl_TEST SET ... WHERE Email=... AND PW=...
IF @@rowcount = 0
BEGIN
INSERT INTO tbl_TEST (ID,PW,EM)
SELECT MAX(ID)+1, @PW, @em FROM tbl_TEST
END
My preference for this type of situation would be to use an IDENTITY column
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply