December 16, 2009 at 3:45 am
hi.
i have a table Jobs(ID, ProjectID, JobID,...).
the jobID is the number of the job in a specific project
example
ID ProjectID JobID
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3
what i want is when inserting a new job i get the maximum jobid in a specific project
and add 1 to it then insert the job but i want to make sure that now two jobs take the same ID in a specific project.
i write the following code
begin tran
select @jobid=COALESCE(max(jobid),0)+1 from tbljobs with(tablockx,holdlock) where projectid=@projectid
INSERT INTO [DMS].[dbo].[tblJobs](ProjectID,JobID,...) values(@ProjectID,@JobID,...)
commit
it's working fine but this method lock the hole table and prevent other from reading, updating and inserting record and this will affect the performance of the application.
can anyone tell me how to lock the record of a specific project from reading until the end of the transaction but the record of other project can be read
December 16, 2009 at 4:53 am
Why aren’t you using an identity column? This will solve your problem and you won’t have to generate the primary key by yourself. If you can’t use an identity column, then there are other options. You can create another table and get the next ID from the new table. You can use the new table in 2 ways. The first way is to create it with an identity column only, then insert a record into it and get the value of @@identity and use it in as the key in your table. Here is a code that shows how to do it:
--This part should be run only once
create table GetID (i int identity (1,1))
go
--This is the part that you can use in your code
declare @i int
--begin tran
insert into GetID default values
--rollback tran
select @i = @@identity
INSERT INTO [DMS].[dbo].[tblJobs](ProjectID,JobID,...) values(@ProjectID, @i,...)
Noticed that I also wrote and remarked beginning and roll backing a transaction. You can decide if you don’t want to insert the record to the GetID table. If you don’t want you can unremark those lines and no record will be inserted (of course you will still get a different value into @i each time).
If you don’t want to use an identity column, then you can create a table with one column only that will hold the last value that was inserted into the table. Then you can create a procedure that updates this value and adds 1 to it. After that it selects the new value and returns it to the client. See an example in the code bellow
create table GetID (i int)
insert into GetID (i) values (0)
go
alter procedure GetNewID (@id int output)
as
create tran
update GetID SET i = i + 1
select @id = i from GetID
commit tran
go
--This part should be inserted into your code
declare @i int
exec GetNewID @i output
INSERT INTO [DMS].[dbo].[tblJobs](ProjectID,JobID,...) values(@ProjectID, @i,...)
Another alternative is to use application lock on the code lines that insert the data into the table. This won’t lock the table, but it will make sure that at any given time this code will be executed by one client only.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply