help creating sequence value using sql transaction
-
Hi,
I have written the following StoredProcedure
create Procedure spCreateQuestion(
@QuestionName varchar(30)
)
as
SET NOCOUNT ON
declare @newAnswerId int
declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions
--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers
--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid
UPDATE Questions
set
where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table..
Can some one please have a look at it and suggest me how do we go about it..
I can't Create the tables with identity property for id columns.
thanks
-
you may want to check out IDENTITY and scope_identity
if you can live with gaps in your numberings.
overlooked the last part of your question.
An alternative is having a parameter-table and proc set to manage sequences.
I hope you have provided propre indexing ?
Normaly , nested transaction don't lock eachother !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks alzdba,
I learned one point that nested transactions don't lock each other..but i see people starting different transactions and committing in different places..
coming to my query i used the following statement to get the last created sequence.Will it gives me the correct sequenceId(i mean last created id)
SELECT @newAnswerId=MAX(AnswerId) from Answers
i did not follow you on this statement
parameter-table and proc set to manage sequences
you mean to create temp table for this..
thanks
-
... parameter-table and proc set to manage sequences ...
This way you could emulate identity
This is just a simple representation :
create table t_mySequenceParameters (
TableName varchar(128) not null primary key
, ParameterValue int not null default 0
)
create proc spcPicNewSequence
@TableName varchar(128)
, @NewSequence int OUTPUT
as
begin
set nocount on
declare @error int
declare @rowcount int
begin tran
update t_mySequenceParameters
set ParameterValue = ParameterValue + 1
where TableName = @TableName
select @error = @@error , @rowcount = @@rowcount
if @error = 0
begin
if @rowcount = 1
begin
select @NewSequence = ParameterValue
from t_mySequenceParameters
where TableName = @TableName
end
end
return 0
commit tran
else
begin
rollback tran
return (@error)
end
This way, you may want to execute the spc in its own transaction, otherwize, you will endup with locking anyway.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
How about this
create Procedure spCreateQuestion(@QuestionName varchar(30))
as
SET NOCOUNT ON
declare @newAnswerId int
declare @newQuestionId int
-- Start Transaction to lock rows
BEGIN TRANSACTION
-- Insert row into Questions and Answers tables with zero ID's
-- This will prevent anyone else from doing this
INSERT INTO Questions (QuestionId,[Name],AnswerId)
VALUES (0,RTRIM(@QuestionName),0)
INSERT INTO Answers(AnswerId) VALUES (0)
-- Get Next AnswerId
SELECT @newAnswerId = ISNULL(MAX(AnswerId),0)+1
FROM Answers
-- Update Answers table with new ID
UPDATE Answers
SET AnswerId = @newAnswerId
WHERE AnswerId = 0
-- Get Next QuestionId
SELECT @newQuestionId = ISNULL(MAX(QuestionId),0)+1
FROM Questions
-- Update Questions table with new ID and AnswerID
UPDATE Questions
SET QuestionId = @newQuestionId,
AnswerID = @newAnswerId
WHERE QuestionId = 0
-- Release Transaction and locks
COMMIT TRANSACTION
Far away is close at hand in the images of elsewhere.
Anon. -
thanks for the procedure alzdba,
But from where you are getting the ParameterValue
-
Thanks David,
never thought about this way..
But when i executed your procedure it is holding the lock for one table only..
I have tested this in Query analyzer removing the COMMIT TRANSACTION statement and went back to my EnterPrise manager and tried to run the data in my table but it has locked only one table .
what am i missing there..
-
I had no problems testing this in QA and both my tables were locked.
Did you use just the tsql, you cannot have a procedure with a begin tran without corresponding commit/rollback.
Far away is close at hand in the images of elsewhere.
Anon. -
i did tested this in my QA using Tsql, for some reason it is locking only the Questions table
but not Answers
Is there anything to do with indexes or primary keys on those table as i have no
primary key in both the table.Just created index on both of them.
-
When I tested the code, my tables did not have primary keys or indexes and EM was locked out of both tables and EM showed the tables as locked under Locks / process ID in the Object column.
The only thing about transactions are they are for a connection so you must have separate connections to get the locking to work.
Far away is close at hand in the images of elsewhere.
Anon. -
David,thanks for taking time to solve my problem..
What i did not understand is i have run the query in two Query analyzers.
I executed my query in first QA and it holded the lock correctly and did not allowed to execute from Second QA.But this is not the same when i tried to access data through EnterPrise Manager.Enterprise manager holds the lock for only one table.
And also why will my original SP will fail to lock the table
I think this is also doing the same..
create Procedure spCreateQuestion(
@QuestionName varchar(30)
)
as
SET NOCOUNT ON
declare @newAnswerId int
declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION
--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions
--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers
--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid
UPDATE Questions
set
where QuestionId=@newQuestionId
COMMIT TRANSACTION
-
Well thats EM for you
Yes your's should do the same, however, I wonder if there is a (very) small chance that two processes could generate the same id, assuming that sql will have to do the select to get the id and then do the insert, I don't know if it is all done in one go or not, maybe profiler would tell you.
alzdba's solution of using a control record to keep count of ID's is one way to overcome this. I use this method, albeit in a different form, for non IDENTITY ID columns that I want to control.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply