SQL Server Performance Forum – Threads Archive

Locking

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

---

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

---

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

---

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.

---

]]>