concurrent transactions

  • Thread starter Thread starter Trapulo
  • Start date Start date
T

Trapulo

I need to define a transaction that:
1 - read a value from a row (a number)
2 - updates this value (decrease or increase it)

I've a lot of concurrent thread that run this transaction. What is the right
ADO transaction settings/pattern to have this goal with best scalability?

thanks
 
There is not a unique answer. It depends on your business requirements, type
of concurrency chosen (optimistic or pessimistic), database (Oracle, Ms Sql
Server, .). But from you description .



If you have a lot of threads which run this transaction, I don't see any
reason to use optimistic concurrency.



In a pessimistic concurrency scheme for Sql Server, for what you do an
isolation level of Read Committed is enough; just read the row and place an
update lock on the row (UPDLOCK locking hint in Sql Server) and call the
update. Or you can use a Repeatable Read isolation level and don't bother
about locking hints. The first is more scalable.



Dumitru
 
Hello, and thanks for your reply.
There is not a unique answer. It depends on your business requirements, type
of concurrency chosen (optimistic or pessimistic), database (Oracle, Ms Sql
Server, .).
Click to expand...

I'm on SQL Server 2000, and I need a pessimistic approach because data field
I may update is very important (it's customer's available credit and I need
to decrement or increment when I provide a service).
But from you description .

If you have a lot of threads which run this transaction, I don't see any
reason to use optimistic concurrency.

In a pessimistic concurrency scheme for Sql Server, for what you do an
isolation level of Read Committed is enough;
Click to expand...

That's good. So I don't need a serializable transaction?
just read the row and place an
update lock on the row (UPDLOCK locking hint in Sql Server) and call the
update. Or you can use a Repeatable Read isolation level and don't bother
about locking hints. The first is more scalable.
Click to expand...

I'was trying to use ado.net's transaction, so I defined a Repeteable Read
transaction, started it, readed the value, updated the row, and commit the
transaction. This seems ok, and I think I've data integrity and my field is
updated without error. Using this I noticed that, with only 5 concurrent
request (I tried to run 5 threads to see how the solution works), I've a lot
of "deadlock" reported by sql server. Why this? My transaction only read a
row, update it, insert a new row on an other table, commit transaction. I
can understand that second and other transactions wait for the first has
committed, I can understand some timeout report (but not with only 5
concurrent and this very quick transaction), but I cannot understand why a
deadlock.
Is there any way I can work without deadlock?

thanks
 
Trapulo
depending on your application you could try a query in the form:

Update TableName with rowlock set FieldValue=FieldValue +@Increment

As far as I'm aware the operation cannot create a deadlock. Additionally I
don't know if the with rowlock option is actually required.... Would the
operation be automic by default?

Regards
Myles.
 
I think that an Update is atomic by design. However I need to check the
value I will update before to update it, so I need a transaction that groups
more that one operation..

Regards
 
The deadlock is normal with any isolation level (it was my mistake in the
first message when I told you that you don't need a locking hint for
repeatable read); you need the UPDLOCK hint for all three isolation levels
(RC, RR, Serial .).



If your flow is like this



Ti_Select: SELECT x FROM Table1 WHERE col =value

Ti_Update: UPDATE Table1 SET x=val WHERE col=value



And you execute this in parallel transactions, the following interleave will
produce a deadlock



T1_Select

T2_Select

T1_Update -> this will just block because of T2

T2_Update -> this will just block because of T1 => so one of the two
transactions will be chosen as a deadlock victim



Because both T1_Select and T2_Select will put a shared lock on the selected
records, so the T1 update will block because of T2 shared lock and T2 update
will block because of T1 shared lock.



Because that you need a locking hint when executing the select: SELECT x
FROM Table1 WITH (UPDLOCK) WHERE col =value. In this way, only one select
can acquire the lock for update, the others will wait.



T1_Select with UPDLOCK

T2_Select with UPDLOCK -> this will just block waiting for acquiring the
lock

T1_Update

T1_Commit

T2_Select with UPDLOCK ->will unblock



You execute this with RC isolation level and will be fine.



Dumitru
 
Thank you for your great explanation!

In fact, I just solved adding an " WITH (XLOCK)" to my SELECT, working on
MDFS' post content. Now I will change this with an UPDLOCK, that I think is
less blocking, as you suggest.
 
Back
Top