January 28, 2004 at 3:06 am
Hi,
I have Windows 2000 Advanced Server with SP4 and SQL Server 2000 Enterprise Edition with SP3a. I have SQL Cluster (Active-Passive) environment. This server box is used for JDE software.
I recently had a very strange problem that through the JDE application, developers tried to delete some record from a table but it hang up all the system so they ended the application from Windows Task Manager. But the intersting thing is that the lock from SQL Server didn't get released for that deletion. When I see in the Current Activities it shows 'Transaction in progress' and in another column it shows 'rolling back'.
When I tried to kill this process then it doesn't return any error but does not kill that process. I tried to kill that process through Query Analyzer and ther it shows 'estimated time: 16 seconds'.
I left it as it is for a day but nothing happened. I was unable to do any thing on that particular table(select, insert, update, delete, etc) but rest is working very fine without any problem. Then I had to restart the services of SQL Server for that and then everything was fine.
So my questions are:
1) I am curious about this lock...what could be the possible reason for that? Why that lock could not be killed.
2) How can I get all the locks released on a particular table (users are working on rest of the tables)?
3) How can I get all the locks released on a particular Database(users are working on rest of the databases)?
January 28, 2004 at 7:54 am
if user cancels the transaction, then SQL Server will roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.
Don't simply restart SQL Server, Sometime, SQL Server has to take much longer time to recovery due to the rollback and You might in the situation none of your users are able to access SQL Server untill the recovery completes.
January 28, 2004 at 8:10 am
Hi,
Thanks for your reply.
Actually there were only 1600 records in that table and only 15 small columns were there in that table. And SQL Server as such was not hang. only that particular table is not accessible in any ways. Other tables in that database and other databases were working fine without any problem. And even after 24 hours nothing happened with that table means still in the same state. So I could not find any other way than that but still looking for the reason for that. I was not able to even kill that particular process.
And by the way, my rest 2 questions are still there:
2) How can I get all the locks released on a particular table (users are working on rest of the tables)?
3) How can I get all the locks released on a particular Database(users are working on rest of the databases)?
January 28, 2004 at 8:54 am
SQL Server enforces locking automatically by itself. You can't do anything about to release locks.
A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct SQL Server to the type of locks to be used.
The setting of SET TRANSACTION ISOLATION LEVEL controls the default transaction locking behavior for all SELECT statements issued by a connection and allows programmers to trade off increased risk of certain integrity problems with support for greater concurrent access to data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply