Unable to update table

[semsql]

Hi everyone,

Pertinent info:
SQL Server 2005
Visual Studio 2005

I am having a major problem with my C# application when inserting about 250 records into a table. The entire table is locked and no other user is able to update a record. Any idea on how I can prevent this from happening? I have tried several recommendations, but nothing seems to work.

Thanks in advance,
Ray.

[LizR]

then somethigns locking the table not just a record - what code do you have?

[dickersonka]

the insert should take no time at all, are you sure its a locking problem?

[semsql]

Thanks your responses.

I know what's causing the problem, but I am not sure how to work around it. My application has a feature that inserts about 250 records into a table. The same table is also used by a web application as well. The problem is that my application holds the lock for too long, and it locks the entire table. I read about isolation level, but the DBA wants to use the READ COMMITTED level; which is very safe for handling concurrency. I would like the table to be unlocked when I am inserting those records, in order for other users to be able to update a record from that table. During the insert process, other users can select or insert, but not Update. The 'Select' is possible because I use "WITH(Nolock)" isolation hint.

Everything works fine, accept other users can't perform an update if this feature of the application is being used to insert those records.

Here is the most significant part of the insert code:

...
tSQL="INSERT INTO "+ScicConstants.TableNames[Caller.TableId]+" ("+tSQL+") VALUES ("+tValues+")";

MainSqlCommand = new SqlCommand(tSQL, dalConnectionObj.GetConnection());
MainSqlCommand.CommandTimeout = maxTimeout;

if (dalConnectionObj.isBeginTrans)
{
MainSqlCommand.Transaction = dalConnectionObj.ConnTransaction;
}

int result = MainSqlCommand.ExecuteNonQuery();
....

I really appreciate your help,
Ray.

[dickersonka]

are you holding the lock until all the inserts complete? or doing it on a per insert basis?

also is either trying to do a tablock or dblock?

[LizR]

Do you need to know if any of the inserts failed?

Eg if you submitted them as a single block of multiple inserts would it be an issue?

[semsql]

are you holding the lock until all the inserts complete? or doing it on a per insert basis?

also is either trying to do a tablock or dblock?

Yes. I am using a transaction. All records are inserted, then committed at once. This process requires all or nothing to be processed. If one record fails, everything should be aborted. As mentioned in one of the responses, using a single insert string/block would prevent me from determine which record has failed. I am not sure about tablock/dblock, but it seems the whole table is locked during insert operation. Now, if I use a single insert block for all 250 records, it'll be very difficult to debug if a record fails. I won't know which recod didn't make it.

I am new to C#, JDBC/Oracle I just have to set the cursor type. I can't seem to find anything on setting cursor type in C#/MS Server 2005.

Thanks for your help,
Ray.

[semsql]

Do you need to know if any of the inserts failed?

Eg if you submitted them as a single block of multiple inserts would it be an issue?

Definitely. I thought about this workaround, but I just keep thinking there must be a way to unlock the table. It just doesn't make sense to lock the entire table in order to insert a record; well, at least not by default. I am going to keep searching on this, but I might just have to go with a block insert.

Thanks for your input,
Ray.

[dickersonka]

the tablock i referred to is a table lock, the dblock is the entire database lock, if the other select or your insert is trying to do this, then until one releases that lock, the other can't function, you might want to check with the other guy to see what kind of lock he has going

[semsql]

the tablock i referred to is a table lock, the dblock is the entire database lock, if the other select or your insert is trying to do this, then until one releases that lock, the other can't function, you might want to check with the other guy to see what kind of lock he has going

Thanks for the info, I appreciate it. I think I am going to use a block insert for now.