Gustav Brock
Gustav at cactus.dk
Sat Jun 29 16:10:46 CDT 2013
Hi Francisco Server info is: Microsoft SQL Server Enterprise Edition Microsoft Windows NT 5.2 (3790) NT INTEL X86 10.0.4000.0 Client info is: Access 2010, 32-bit, latest SPs and updates Windows 7, 64-bit, latest SPs and updates I know you can call a BeginTrans and - by error - leave it uncommitted, but we have refrained from using transactions because the app doesn't perform large scale operations on the tables; it performs mostly lookup and record-by-record updates and additions. We can, however, have concurrency issues, but it is my understanding that in such cases the autocommit will fail and the changes will be rolled back automatically. /gustav >>> fhtapia at gmail.com 29-06-13 18:47 >>> Gustav, Yes you can kick off a Begin Trans w/o ever writing in a rollback or commit in your execution, if you perform the action using the query analyzer (management studio interface), the ide will catch that and give you an error (normally). otherwise, a lost thread can lock the table indefinitely. a solution would be to kick off a kill of the session id on the server. this usually auto-kicks in a rollback. the other method is to send in a commit or rollback command using the same session from your application. I haven't used ODBC in a really long time, but a time out on this protocol should be respected by the server and auto-kickin a rollback, since you are using Access that would be my expectation. What is your environment? OS, application, server edition version etc..? -Francisco -------------------------- You should follow me on twitter here <http://twitter.com/seecoolguy> Blogs: SqlThis! <http://bit.ly/sqlthis> | XCodeThis!<http://bit.ly/xcodethis> -------------------------- Save on your mobile wireless here <https://z22hi4113e2.ting.com/> <http://db.tt/JeXURAx> On Sat, Jun 29, 2013 at 1:57 AM, Gustav Brock <Gustav at cactus.dk> wrote: > Hi all > > A table didn't respond at the client's SQL Server. > The dba told that it was locked by a uncomitted transaction, and a restart > of the SQL Server was needed. That happened and the table was accessible > again. > > This table, however, is only used by an Access app via ODBC, and nowhere > is BeginTrans used for the table. As to my understanding, SQL Server > performs AutoCommit if not told otherwise. > > So, questions: > > 1. How can a BeginTrans be applied without a RollBack/CommitTrans? > 2. Is it really necessary to restart the SQL Server to unlock the table? > > Any other suggestions what to look for or how to prevent this situation? > > /gustav