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.
"Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> "Trapulo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>
>> 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, .).
>>
>> 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;
>>
>> 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.
>>
>> 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
>>
>>
>
>
|
Similar Threads
