|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
concurrent transactionsI 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 Show quote "Trapulo" <nonscrive***@qui.it> wrote in message news:%232IPzov6FHA.1724@TK2MSFTNGP10.phx.gbl... >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 > > "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message Hello, and thanks for your reply.news:urduB026FHA.1276@TK2MSFTNGP09.phx.gbl... > There is not a unique answer. It depends on your business requirements, I'm on SQL Server 2000, and I need a pessimistic approach because data fieldtype > of concurrency chosen (optimistic or pessimistic), database (Oracle, Ms Sql > Server, .). 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 . That's good. So I don't need a serializable transaction?> > 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 I'was trying to use ado.net's transaction, so I defined a Repeteable Read> 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. 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. Show quote "Trapulo" wrote: > "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message > news:urduB026FHA.1276@TK2MSFTNGP09.phx.gbl... > > 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 > > > 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 Show quote "MDFS" <M***@discussions.microsoft.com> wrote in message news:A11E50D0-333F-4E60-9ADC-946E6AFDA807@microsoft.com... > 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. > > "Trapulo" wrote: > > > "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message > > news:urduB026FHA.1276@TK2MSFTNGP09.phx.gbl... > > > > 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 > > > > > > 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 Show quote "Trapulo" <nonscrive***@qui.it> wrote in message news:eoZPlT46FHA.1276@TK2MSFTNGP09.phx.gbl... > "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message > news:urduB026FHA.1276@TK2MSFTNGP09.phx.gbl... > > 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 > > 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. Show quote "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message news:utY1Ivv7FHA.3984@TK2MSFTNGP11.phx.gbl... > 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" <nonscrive***@qui.it> wrote in message > news:eoZPlT46FHA.1276@TK2MSFTNGP09.phx.gbl... >> "Dumitru Sbenghe" <sben***@yahoo.com> wrote in message >> news:urduB026FHA.1276@TK2MSFTNGP09.phx.gbl... >> >> 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 >> >> > >  
Other interesting topics
|
|||||||||||||||||||||||
