TABLOCKX problem

[Daniel Bello Urizarri]

Hello:

Im developing some application that is connected to a Sql Server and I need
to read a value and increment it in a multiuser envirnment. I'm trying to
lock the table at the moment of reading the data, but setting a breakpoint
after the select command is executed, the table is readable from another
connection. The blocking starts after the update query and until the
transaction is commited.

Is there some Ado.NET standard way to do what im trying? (I perform some
test with BeginTransaction too. Same results)

What Im i doing wrong?

Thanks
Daniel



Mi code is:

SqlTransaction transaction = null;

try
{
transaction = connection.BeginTransaction ();
SqlCommand cmd = transaction.Connection.CreateCommand ();
cmd.Transaction= transaction;
cmd.CommandText= "Select * From IndexControl TABLOCKX";

//Efectuando la lectura con el suspuesto bloqueo

DataTable table= new DataTable ();
SqlDataAdapter adapter = new SqlDataAdapter ( cmd );
adapter.Fill ( table );
int index = Convert.ToInt32 ( table.Rows [0]["Index"] );

// .. .. .. .. .. otras operaciones..... .. . ... .

index++;
cmd.CommandText= "update IndexControl set Index=" + index;
cmd.ExecuteNonQuery ();

transaction.Commit ();
}
catch
{
if ( transaction != null ) transaction.RollBack ();
}

 

[Sahil Malik]

Daniel,

Locking in a multi user environment is not advised. Especially when you are
doing disconnected stuff.

A good way to do this is read the snapshot, set constraint on dataset, set
seed and autoincrement values to -1, and during update, do a select
@@identity to get the last value of primary key generated by Sql Server -
which will always be generated on the sql server and then refresh that back
to the client using a dataset.merge.

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[David Browne]

Hello:

Im developing some application that is connected to a Sql Server and I
need
to read a value and increment it in a multiuser envirnment. I'm trying to
lock the table at the moment of reading the data, but setting a breakpoint
after the select command is executed, the table is readable from another
connection. The blocking starts after the update query and until the
transaction is commited.

Is there some Ado.NET standard way to do what im trying? (I perform some
test with BeginTransaction too. Same results)

What Im i doing wrong?

You must either set the transaction isolation level to at least repeatable
read or add the HOLDLOCK hint.

But this solution has scalability problems. It would be better to find a
solution based on Sql Server's fast and concurrent Identity columns.

David

 

[Daniel Bello Urizarri]

Hi Sahil:

The idea is very good, but not applicable in my case.

The value that I have to increment depends of some intervals, so the
autoincrement is not a solution.

Have you any idea about why the tablockx does not work?

Daniel

 

[Daniel Bello Urizarri]

Hi David Browne

You must either set the transaction isolation level to at least repeatable
read or add the HOLDLOCK hint.

I try your suggestions but the behavior is the same... The select operations
over the table are blocked only after the update command is performed.

This is the same behavior I receive when using the BeginTransaction without
parameters and the select query without any locking modifier.

Have more ideas?

 

[Sahil Malik]

Daniel,

You could still implement the your key logic in the scenario I mentioned in
the update... however .. another way to skin this cat is to set a
ReadUncommitted Isolation level. This is easily done by setting the
IsolationLevel on the transaction object. Or try putting on NOLOCK on your
selects.

However, how do you ensure data integrity in a concurrent environment by
doing that?

Another way to put my answer - TabLocX puts the table in a lock until your
statement ends. That is not advisable in a multiuser disconnected
environment. So just don't use it :-/

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Mary Chipman]

Why do you need to lock the whole table, why not just the rows that
will be affected? You can do this easily and efficiently by calling a
stored procedure to do the work. Use an explicit transaction (BEGIN
TRAN[SACTION], COMMIT TRAN, ROLLBACK TRAN). SQL Server will hold the
necessary locks until the transaction is either completed or rolled
back. See the "Explicit Transactions" topic in SQL Books Online.

--Mary

 

[Daniel Bello Urizarri]

Hi Sahil:

Yes, I understand what you are saying about the locking impact in the
concurrency.

The table im trying to lock is only used in the data insertions that will be
done in user time, so the locking is no so critical.

But it does not work for me. The ReadUncommited and the NOLOCK option does
not work either.

Im trying to ensure the data integrity by doing select-analyze-update
operations as a critical section. But until now I can not avoid that two
users perform the same select, so both will perform the same opdate and one
of them will recieve an error.

Daniel

 

[Grzegorz Danowski]

Daniel,

Locking in a multi user environment is not advised. Especially when you
are
doing disconnected stuff.

A good way to do this is read the snapshot, set constraint on dataset, set
seed and autoincrement values to -1, and during update, do a select
@@identity to get the last value of primary key generated by Sql Server -
which will always be generated on the sql server and then refresh that
back
to the client using a dataset.merge.

(...)

In what way do you suggest referesh data back? If you mean using select in
Insert Command (and then merge new data with old), I'm not sure that it is
sufficient.
If new record will have id = -1 in dataset, and then it will be saved to
database and in db it will have id = 1435 and then Insert Command will
receive from db record 1435. I suppose that merge cause duplication new
record in ds. One with id = -1 and second with id = 1435. But I'm not sure.
I suggest that better way is use output parameter (in Insert Command), for
example:
http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

Regards,
Grzegorz

 

[Sahil Malik]

Daniel,

What is your transaction?

<Transaction>Select + Analyze + Update</Transaction>? OR
Select + Analyze + <Transaction>Update</Transaction>

Also what is the anticipated duration for the transaction?

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Daniel Bello Urizarri]

My transaction is

<Transaction>Select + Analyze + Update</Transaction>

the table has single a row that is used to synchronize indices
The time of the transaction must be of miliseconds.

Daniel

 

[Daniel Bello Urizarri]

Hi to everyone:

At last i found the problem.

I was using the syntax:
"Select * from table TABLOCK"

I must use the following syntax:
"Select * from table WITH (TABLOCK)"

In the second case it works as I spected.

Thanks to all for your help

Daniel

 

[Sahil Malik]

Ok so what's happening is (unless I still haven't understood your problem,
and I am sorry, I know that can be frustrating)

Transaction 1 is half way,
Transaction 2 gets blocked because of that.

If that is so, then by using TablocX you've asked SQL Server to do that. You
either have to live with it, or think of another strategy.

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Daniel Bello Urizarri]

Hello Salink:

If transaction 1 is half way, transaction two have to wait. It is like in
the consumer-producer problem, the operation of read an decrement the count
must be done as a single operation.

As I say, it will only be used on insertions, that will be performed using
forms. (user time)

Thank for your interest and ideas.