Lock table until transaction finish.

HOw do I write a statement to lock a table until my transactions are finish?

e.g...

declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'

'Lock Syantax here

insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value

'Lock Syantax end

The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".

I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.

Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....

err..btw..this is abit out of the topic...

I was wondering,which is much faster and less taxing on cpu processing?

select ident_current('tablename') vs select max(tableidentitycolumn)

as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.

can someone actually really test it out at their environment ?

I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.

Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.

blindman