June 9, 2009 at 6:08 pm
Hi guys,
I juat want to create an exclusive lock on a table for a period of time, so nobody can not read,write or in in general do nothing against this table:
Create Exclusive lock
----------
On this section,this table is untouchable for the other people
My Operation
----------
Release exclusive lock
June 9, 2009 at 6:19 pm
Use locking hint (TABLOCKX) in "My Operation" and it will do exactly what you want.
_____________
Code for TallyGenerator
June 9, 2009 at 7:13 pm
here's an example of Sergiy's suggestion:
begin tran
select top 1 * from mytable with (tablockx) --exclusive table lock this table to me and my transaction
--do more stuff
commit tran --commit the tran, releasing the locks
Lowell
June 10, 2009 at 9:10 am
Guys,
Thanks a lot for your replys.I have done the same thing ,but this does not work.
Here is senario,in my "operation section" I am giving block of ID's to different Objects.Yesterday this code has give overlaped ID's to two different objects.Here is the code
Begin Tran
select top 1 * from IdentityTable with (TABLOCKX)--IdentityTable is used for creating ID's
set @CurrentId=(SELECT IDENT_CURRENT('IdentityTable'))+10
DBCC CHECKIDENT ('IdentityTable', RESEED, @CurrentId)
if @@Error 0 beginif @@trancount 0 rollbackreturn -101end
Select @LastID = @CurrentId
Select @FirstID = @LastID - @NumberOfIDsInBlock + 1
Insert Into ManagingID ([MachineName],[ComponentName],[ObjectType],[NumberofIDsRequested],[FirstID],[LastID]) values(@MachineName, @ComponentName, @ObjectType, 10, @FirstID, @LastID)
if @@Error 0 beginif @@trancount 0 rollbackreturn -102end
Commit
As I said, yesterday we had two objects with the same block of ID's in ManagingID table
June 10, 2009 at 9:38 am
why are you selecting @CurrentId from an identity value manually? INSERT into the table and get the Scope_Identity() its the right way to do it. it looks like you are trying to use a table to get an ID, instead of using the identity() property the normal way. that method can handle as many as 400 identity() insert operations a second from what I've read here.
i would not select the currrent value and then reseed it in order to try and maintain a current "ID" for some other process; it looks like you are trying to emulate getting an oracle sequence number or something.
i would insert into your table and use the OUTPUT clause to get the multiple id's returned instead.
Also, am i missing something, but where is @NumberOfIDsInBlock getting it's value? you are doing integer math, unless the @NumberOfIDsInBlock is assigned elsewhere, wouldnt
@LastID - @NumberOfIDsInBlock + 1 be NULL?
Lowell
June 10, 2009 at 10:26 am
Sorry I forgot to say that @NumberOfIDsInBlock is input parameter, and I had considered 10 in my example(I forgot to use 10 instead of @NumberOfIDsInBlock in my sample).My main question is that,Why should I have 2 objects with the same block of ID's,because as you can see,nobody can access identitytable unless the previous process is done(I have exclusively locked that table).
June 10, 2009 at 11:08 am
the issue is the way exclusive locks work.
a lock prevents the SELECT,UPDATE/INSERT/DELETE of the table.
it does not prevent the selecting of the current ident value, or reviewing of any other metadata related to the table.
proof:
open two windows in tempdb database
in window one:
create table IdentityTable (ID int identity(1,1) not null primary key)
insert into IdentityTable default values
insert into IdentityTable default values
insert into IdentityTable default values
Begin Tran
select top 1 * from IdentityTable with (TABLOCKX)--IdentityTable is used for creating ID's
in window two:
declare @CurrentId int
set @CurrentId=(SELECT IDENT_CURRENT('IdentityTable'))+10
select @CurrentId
window two returns a value instantly, every time, of the current value.
that function IDENT_CURRENT (and any other server function) are never affected by any locking mechanism.
If you changed to actually SELECT max(ID) From IdentityTable,
as well as making sure your process inserted as many records as exist in @NumberOfIDsInBlock, that would lock the other window, as well as guaranteeding that the ID is not duplicated.
Lowell
June 10, 2009 at 12:27 pm
Yes,It is correct,
but before using IDENT_CURRENT,I have exclusively lock the table,So if there is already an exclusive lock on that table,the second process will wait,until the exclusive lock has been freed.
June 10, 2009 at 12:30 pm
Is there any other way to get the current Id of an identity field ,without using IDENT_CURRENT function?
June 10, 2009 at 3:05 pm
The best way is to have IDENTITY column on Managing table itself and not only get rid of lots of useless coding but allow multi-row inserts on this table as well.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply