SQL table locked infinity when access us from c# code

Question

SQL table locked infinity when access us from c# code

rafnunes

SSC Enthusiast

Points: 150
More actions
April 20, 2016 at 4:41 am

#400046

I have a code that execute this steps in some table:
1 - Update 5000 rows with will be to extract, is a bit column with value NULL 0 or 1
2 - Select 5000 rows
3 - Do some work in C#
4 - Update 5000 rows with will be end of extract, is a bit column with value NULL 0 or 1
The code work and can extract all days like 100000 rows.
Some times for some reason the table is locked because can no more update anything, and i can see this:
a - From MSSMS i can update, select or delete this table
b - Write some code to acess again this table and not work again
c - Restart SQL service and continued not work
d - Rename the table, creat other table with the some data and the application run again with sucess.
e - And the more strange is change the connection string to my renamed table in my application c# and continued can't update anything.
Someone can tell me why this is happen or anyone already pass for this issue's.
Thanks for any help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

rafnunes (4/20/2016)
I have a code that execute this steps in some table:
1 - Update 5000 rows with will be to extract, is a bit column with value NULL 0 or 1
2 - Select 5000 rows
3 - Do some work in C#
4 - Update 5000 rows with will be end of extract, is a bit column with value NULL 0 or 1
The code work and can extract all days like 100000 rows.
Some times for some reason the table is locked because can no more update anything, and i can see this:
a - From MSSMS i can update, select or delete this table
b - Write some code to acess again this table and not work again
c - Restart SQL service and continued not work
d - Rename the table, creat other table with the some data and the application run again with sucess.
e - And the more strange is change the connection string to my renamed table in my application c# and continued can't update anything.
Someone can tell me why this is happen or anyone already pass for this issue's.
Thanks for any help.

Welcome to the forum. It's difficult to answer your question because we don't know how your code is doing things. It's very easy to create locks on data in SQL Server and I expect that the behaviour you are seeing is a consequence of the way you are doing things. I suggest that you do some additional reading around C# and SQL Server locking and then revisit your code.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

rafnunes SSC Enthusiast Points: 150 More actions · Answer 2

thanks for your answer.

But correct if I'm wrong, after reset SQL server Service and application the lock should not disappear?

It's possible to know if a table is locked, or the state?

Thanks again.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

rafnunes (4/20/2016)
thanks for your answer.
But correct if I'm wrong, after reset SQL server Service and application the lock should not disappear?
It's possible to know if a table is locked, or the state?
Thanks again.

After restarting the SQL Server service, which is a rather brutal technique for a production server, the locks should disappear.

To start investigating locks and who is locking, I suggest that you read up on sys.dm_tran_locks and sp_who2.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.