SQL Server Performance

Locking

Discussion in 'SQL Server DTS-Related Questions' started by jeyakumars, Feb 2, 2007.

  1. jeyakumars New Member

    Hi all,
    I am migrating the database from msaccess to sqlserver 2000.
    Frot end is msaccess forms.when concurrent users tried to edit the record,
    MSaccess locked the second user.(its wont allow to edit).

    After migrating to sqlserver ,locking has not happening.i am using the link tables to
    connect sqlserver.

    can u pls help me out, how to handle this situation ?


    Thanks in advance

    S.Jeyakumar
  2. ghemant Moderator

    Hi,
    Have you used Commit / Rollback Transactions statements properly!!! Have you check if their's long running query / connection might cause deadlock.... Run sp_who2 from your Query Analyzer and check if their's spid who has value <>0 in blocked column if it is their again run dbcc inputbuffer(spid) and it will return you a SP / T-SQL who is blocking. YOu may also run dbcc opentran to check if their is a long running transaction.

    Regards

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  3. jeyakumars New Member

    Before Migrating
    Ms Access forms itself validating, whether other user is accessing the record or not.
    After migrating to sqlserver,its not happening. i need to validate before edit the record..

  4. Adriaan New Member

    If the old system with the MS Access backend had a locking feature, then the locking was controlled by the Jet database engine.

    Once you move to a SQL Server back-end, the SQL Server database engine takes care of the locking, and will probably disregard any locking mechanisms that were setup for Jet.

    I've only seen Access systems with "optimistic locking" - i.e. no locking - so I don't know how you would set Access up to lock records.

Share This Page