Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: Lock table until transaction finish.

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


    e.g...

    Code:
    
    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?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Well

    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.)

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Lock table until transaction finish.

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    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 Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Aug 2003
    Posts
    27
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.

    Sigh.

  7. #7
    Join Date
    Aug 2003
    Posts
    27
    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...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •