|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Application - document lockingI have problem with locking concept in my application (ADO.NET , C#). Application has several documents which looks like Order. My question is what and how should I lock those documents. 1.Lock document as a whole (Order + OrderLines) so another user can't work on this document (pessimistic lock)? 1.1. How to implement this on SQL server ? 2.Should I use optimistic locking on Order and OrderLines ? 3.If I use optimistic locking on OrderLines should I each time update timestamp of Order and by each insert, update and read of OrderLine check whether somebody has changed this Order and/or OrderLine which is both possible ? Any thoughts are appreciated!
Show quote
"Marek" <ma***@home.puton.cz> wrote in message my advice: forget about #1. what #1 really means? either selecting the data news:%23OCPpamIGHA.3192@TK2MSFTNGP10.phx.gbl... > Hi ! > > > > I have problem with locking concept in my application (ADO.NET , C#). > > Application has several documents which looks like Order. > > My question is what and how should I lock those documents. > > > > 1.Lock document as a whole (Order + OrderLines) so another user can't work > on this document (pessimistic lock)? > > 1.1. How to implement this on SQL server ? with (updlock,holdlock) or something similar, and keeping the transaction open for indefinite amount of time (no, no, no, and no), or setting some flag, marking that the document is being edited by a client, which is somewhat better, but still leaves you with the housekeeping problems that have to be dealt with from the application (and relying on application to take care of things is generally not a good idea). > i would. and i do.> 2.Should I use optimistic locking on Order and OrderLines ? > a timestamp column will be updated automatically each time data in the row > 3.If I use optimistic locking on OrderLines should I each time update > timestamp of Order > > and by each insert, update and read of OrderLine check whether somebody > > has changed this Order and/or OrderLine which is both possible ? is updated, you just have to check whether it's value is same as what it was when the row was read. it's just my opinion, of course.. dean > a timestamp column will be updated automatically each time data in the row Tnx Dean, but I believe that you answer is over simplified> is updated, you just have to check whether it's value is same as what it > was when the row was read. > > it's just my opinion, of course.. > > dean and does not address all the issues ... What should happened when orderline is updated ? should order also be touched (so timestamp can change) Is this sequence right when updating Orderline ? 1.Get TimeStamp of Order 2. Get TimeStamp of OrderLine 3. User changes orderline 4. Check if Order was changed meanwhile 4. 1 If yes there is a conflict since somebody changed order while I was updating OrderLine 5. Check if OrderLine was changed 5.1 If yes there is a conflict since somebody changed OrderLine line while I was updating OrderLine 6. Update OrderLine 7. Touch Order or there is not need to check for Order Timestamp when updating OrderLine in this case If you acquire locks in the correct order and hold them until the end of the
transaction, no other user will be able to change any rows that you have locked until the transaction is either committed or rolled back. ML --- http://milambda.blogspot.com/ hi, marek
yes, it was somewhat simplified, i agree. the actual implementation depends on the actual business requirements (eg, is it ok that two people work with different lineitems of the same document?), so this sequence could be right - provided that everything from (and including) #4 is isolated inside a transaction. dean Show quote "Marek" <ma***@home.puton.cz> wrote in message news:uQlLHnnIGHA.3060@TK2MSFTNGP10.phx.gbl... > >> a timestamp column will be updated automatically each time data in the >> row >> is updated, you just have to check whether it's value is same as what it >> was when the row was read. >> >> it's just my opinion, of course.. >> >> dean > Tnx Dean, but I believe that you answer is over simplified > and does not address all the issues ... > > What should happened when orderline is updated ? > should order also be touched (so timestamp can change) > > Is this sequence right when updating Orderline ? > > 1.Get TimeStamp of Order > 2. Get TimeStamp of OrderLine > 3. User changes orderline > 4. Check if Order was changed meanwhile > 4. 1 If yes there is a conflict since somebody changed order while I was > updating OrderLine > 5. Check if OrderLine was changed > 5.1 If yes there is a conflict since somebody changed OrderLine line while > I was updating OrderLine > 6. Update OrderLine > 7. Touch Order > > or there is not need to check for Order Timestamp > when updating OrderLine in this case > > > > Well it's interesting how would you solve the problem
when two people must not work with 2 different Orderlines of the same Order ? Should we use pessimistic locking than ? Another question that arises when considering scenario below is how many roundtrips to SQL requires such a solution ? Steps 1,2 - one roundtrip Steps 4,5 - one roundtrip Steps 6,7 - one roundtrip Can this be smaller number than 3 ? Show quote > yes, it was somewhat simplified, i agree. the actual implementation > depends on the actual business requirements (eg, is it ok that two people > work with different lineitems of the same document?), so this sequence > could be right - provided that everything from (and including) #4 is > isolated inside a transaction. > >> Is this sequence right when updating Orderline ? >> >> 1.Get TimeStamp of Order >> 2. Get TimeStamp of OrderLine >> 3. User changes orderline >> 4. Check if Order was changed meanwhile >> 4. 1 If yes there is a conflict since somebody changed order while I was >> updating OrderLine >> 5. Check if OrderLine was changed >> 5.1 If yes there is a conflict since somebody changed OrderLine line >> while I was updating OrderLine >> 6. Update OrderLine >> 7. Touch Order >> >> or there is not need to check for Order Timestamp >> when updating OrderLine in this case >> not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a stored
procedure, inside a transaction. dean Show quote "Marek" <ma***@home.puton.cz> wrote in message news:OIqObNoIGHA.524@TK2MSFTNGP09.phx.gbl... > > Well it's interesting how would you solve the problem > when two people must not work with 2 different Orderlines > of the same Order ? Should we use pessimistic locking than ? > > Another question that arises when considering scenario below is > how many roundtrips to SQL requires such a solution ? > > Steps 1,2 - one roundtrip > Steps 4,5 - one roundtrip > Steps 6,7 - one roundtrip > > Can this be smaller number than 3 ? > > > >> yes, it was somewhat simplified, i agree. the actual implementation >> depends on the actual business requirements (eg, is it ok that two people >> work with different lineitems of the same document?), so this sequence >> could be right - provided that everything from (and including) #4 is >> isolated inside a transaction. >> >>> Is this sequence right when updating Orderline ? >>> >>> 1.Get TimeStamp of Order >>> 2. Get TimeStamp of OrderLine >>> 3. User changes orderline >>> 4. Check if Order was changed meanwhile >>> 4. 1 If yes there is a conflict since somebody changed order while I was >>> updating OrderLine >>> 5. Check if OrderLine was changed >>> 5.1 If yes there is a conflict since somebody changed OrderLine line >>> while I was updating OrderLine >>> 6. Update OrderLine >>> 7. Touch Order >>> >>> or there is not need to check for Order Timestamp >>> when updating OrderLine in this case >>> > > Tnx. Dean
That looks like a reasonable solution with minimum overhead on SQL server side. And what would be theoretical scenario: 1. when two people must not work with 2 different Orderlines of the same order 2. two people must work not with the same Order at all ? Does this require pessimistic lock and an open connection during order/orderline update or is there better simpler solution ? I have two solutions on my mind: 1. Lock the whole Order and child Orderlines with hold lock so nobody can update those records. 2. Create LockingTable (RecordId, UserId) and hold connection open on this record in LockingTable so if program crashes SQL server will release lock. Before reading record With 'SELECT (NOLOCK)' read wheather record is locked and if it is tell the user who locks the record. But both solutions require open connection ? I'm I missing something ? Show quote "Dean" <dvitner@nospam.gmail.com> wrote in message news:uxNdmVoIGHA.1836@TK2MSFTNGP11.phx.gbl... > not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a > stored procedure, inside a transaction. > > dean > > "Marek" <ma***@home.puton.cz> wrote in message > news:OIqObNoIGHA.524@TK2MSFTNGP09.phx.gbl... >> >> Well it's interesting how would you solve the problem >> when two people must not work with 2 different Orderlines >> of the same Order ? Should we use pessimistic locking than ? >> >> Another question that arises when considering scenario below is >> how many roundtrips to SQL requires such a solution ? >> >> Steps 1,2 - one roundtrip >> Steps 4,5 - one roundtrip >> Steps 6,7 - one roundtrip >> >> Can this be smaller number than 3 ? >> >> >> >>> yes, it was somewhat simplified, i agree. the actual implementation >>> depends on the actual business requirements (eg, is it ok that two >>> people work with different lineitems of the same document?), so this >>> sequence could be right - provided that everything from (and including) >>> #4 is isolated inside a transaction. >>> >>>> Is this sequence right when updating Orderline ? >>>> >>>> 1.Get TimeStamp of Order >>>> 2. Get TimeStamp of OrderLine >>>> 3. User changes orderline >>>> 4. Check if Order was changed meanwhile >>>> 4. 1 If yes there is a conflict since somebody changed order while I >>>> was updating OrderLine >>>> 5. Check if OrderLine was changed >>>> 5.1 If yes there is a conflict since somebody changed OrderLine line >>>> while I was updating OrderLine >>>> 6. Update OrderLine >>>> 7. Touch Order >>>> >>>> or there is not need to check for Order Timestamp >>>> when updating OrderLine in this case >>>> >> >> > > hi again,
as i see it, this whole optimistic vs pessimistic thing is really a business problem. in most cases, unless you're dealing with some very chaotic organization, it is well known who can do what with what data. it is not unusual to have an owner for the document, and only that person is permitted to make updates to the specific document. this type of scenario (involving update conflicts) is rather unlikely in the real world, imo. sometimes it's practically impossible, sometimes it's desirable, even required (why not let several people do the data entry on an inventory list?). to answer the question 'how to deal with it?' is really up to your customers. ask them, is it possible at all? how often could it happen? how severe will the consequences be? most of them, in my experience, could live with an update conflict here and there - as long as you can tell them what happened, and who was competing with them. auditing is here much more important than locking. technically speaking, would you really want to let the user opet a transaction, select the data and place and hold update lock on the data, then keep the data locked for nobody knows how long (he might go to lunch, or go home or whatever - you have no control over it), and eventually at some point in time decide to end the transaction, with or without any change? first of all, transactions should be short-lived - started as late as possible, and ended as soon as possible. they eat up resorces on server, and keep others from accessing data. i can't think of a real-world situation that would justify such a scenario. you have come to a rather good practical solution yourself (in your previous post) - why not use it? dean Show quote "Marek" <ma***@home.puton.cz> wrote in message news:eXoRwooIGHA.3144@TK2MSFTNGP11.phx.gbl... > Tnx. Dean > That looks like a reasonable solution > with minimum overhead on SQL server side. > > And what would be theoretical scenario: > 1. when two people must not work with 2 different Orderlines of the same > order > 2. two people must work not with the same Order at all ? > > Does this require pessimistic lock and an open connection during > order/orderline update or is there better simpler solution ? > > I have two solutions on my mind: > 1. Lock the whole Order and child Orderlines with hold lock so nobody > can update those records. > > 2. Create LockingTable (RecordId, UserId) and hold connection open > on this record in LockingTable so if program crashes > SQL server will release lock. Before reading record With 'SELECT (NOLOCK)' > read > wheather record is locked and if it is tell the user who locks the record. > But both solutions require open connection ? > I'm I missing something ? > > > > > > "Dean" <dvitner@nospam.gmail.com> wrote in message > news:uxNdmVoIGHA.1836@TK2MSFTNGP11.phx.gbl... >> not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a >> stored procedure, inside a transaction. >> >> dean >> >> "Marek" <ma***@home.puton.cz> wrote in message >> news:OIqObNoIGHA.524@TK2MSFTNGP09.phx.gbl... >>> >>> Well it's interesting how would you solve the problem >>> when two people must not work with 2 different Orderlines >>> of the same Order ? Should we use pessimistic locking than ? >>> >>> Another question that arises when considering scenario below is >>> how many roundtrips to SQL requires such a solution ? >>> >>> Steps 1,2 - one roundtrip >>> Steps 4,5 - one roundtrip >>> Steps 6,7 - one roundtrip >>> >>> Can this be smaller number than 3 ? >>> >>> >>> >>>> yes, it was somewhat simplified, i agree. the actual implementation >>>> depends on the actual business requirements (eg, is it ok that two >>>> people work with different lineitems of the same document?), so this >>>> sequence could be right - provided that everything from (and including) >>>> #4 is isolated inside a transaction. >>>> >>>>> Is this sequence right when updating Orderline ? >>>>> >>>>> 1.Get TimeStamp of Order >>>>> 2. Get TimeStamp of OrderLine >>>>> 3. User changes orderline >>>>> 4. Check if Order was changed meanwhile >>>>> 4. 1 If yes there is a conflict since somebody changed order while I >>>>> was updating OrderLine >>>>> 5. Check if OrderLine was changed >>>>> 5.1 If yes there is a conflict since somebody changed OrderLine line >>>>> while I was updating OrderLine >>>>> 6. Update OrderLine >>>>> 7. Touch Order >>>>> >>>>> or there is not need to check for Order Timestamp >>>>> when updating OrderLine in this case >>>>> >>> >>> >> >> > > Thanks Dean !
I don't have such situation , but I was just thinking aloud. Currently in my firm there is an old proprietary system which uses pessimistic locking and it works fine, so I was thinking how this can be implemented on SQL server. Since the whole architecture of ADO.NET does not encourage pessimistic locking and there is no business need to support it I will definitely use solution to that we came up in previous posts. Thanky you again for a thoughtful posts that have broadened my "data access" sights ! Show quote "Dean" <dvitner@nospam.gmail.com> wrote in message news:unvH%23hqIGHA.2708@tk2msftngp13.phx.gbl... > hi again, > > as i see it, this whole optimistic vs pessimistic thing is really a > business problem. in most cases, unless you're dealing with some very > chaotic organization, it is well known who can do what with what data. it > is not unusual to have an owner for the document, and only that person is > permitted to make updates to the specific document. this type of scenario > (involving update conflicts) is rather unlikely in the real world, imo. > sometimes it's practically impossible, sometimes it's desirable, even > required (why not let several people do the data entry on an inventory > list?). to answer the question 'how to deal with it?' is really up to your > customers. ask them, is it possible at all? how often could it happen? how > severe will the consequences be? most of them, in my experience, could > live with an update conflict here and there - as long as you can tell them > what happened, and who was competing with them. auditing is here much more > important than locking. > > technically speaking, would you really want to let the user opet a > transaction, select the data and place and hold update lock on the data, > then keep the data locked for nobody knows how long (he might go to lunch, > or go home or whatever - you have no control over it), and eventually at > some point in time decide to end the transaction, with or without any > change? first of all, transactions should be short-lived - started as late > as possible, and ended as soon as possible. they eat up resorces on > server, and keep others from accessing data. i can't think of a real-world > situation that would justify such a scenario. > > you have come to a rather good practical solution yourself (in your > previous post) - why not use it? > > dean > > "Marek" <ma***@home.puton.cz> wrote in message > news:eXoRwooIGHA.3144@TK2MSFTNGP11.phx.gbl... >> Tnx. Dean >> That looks like a reasonable solution >> with minimum overhead on SQL server side. >> >> And what would be theoretical scenario: >> 1. when two people must not work with 2 different Orderlines of the same >> order >> 2. two people must work not with the same Order at all ? >> >> Does this require pessimistic lock and an open connection during >> order/orderline update or is there better simpler solution ? >> >> I have two solutions on my mind: >> 1. Lock the whole Order and child Orderlines with hold lock so nobody >> can update those records. >> >> 2. Create LockingTable (RecordId, UserId) and hold connection open >> on this record in LockingTable so if program crashes >> SQL server will release lock. Before reading record With 'SELECT >> (NOLOCK)' read >> wheather record is locked and if it is tell the user who locks the >> record. >> But both solutions require open connection ? >> I'm I missing something ? >> >> >> >> >> >> "Dean" <dvitner@nospam.gmail.com> wrote in message >> news:uxNdmVoIGHA.1836@TK2MSFTNGP11.phx.gbl... >>> not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a >>> stored procedure, inside a transaction. >>> >>> dean >>> >>> "Marek" <ma***@home.puton.cz> wrote in message >>> news:OIqObNoIGHA.524@TK2MSFTNGP09.phx.gbl... >>>> >>>> Well it's interesting how would you solve the problem >>>> when two people must not work with 2 different Orderlines >>>> of the same Order ? Should we use pessimistic locking than ? >>>> >>>> Another question that arises when considering scenario below is >>>> how many roundtrips to SQL requires such a solution ? >>>> >>>> Steps 1,2 - one roundtrip >>>> Steps 4,5 - one roundtrip >>>> Steps 6,7 - one roundtrip >>>> >>>> Can this be smaller number than 3 ? >>>> >>>> >>>> >>>>> yes, it was somewhat simplified, i agree. the actual implementation >>>>> depends on the actual business requirements (eg, is it ok that two >>>>> people work with different lineitems of the same document?), so this >>>>> sequence could be right - provided that everything from (and >>>>> including) #4 is isolated inside a transaction. >>>>> >>>>>> Is this sequence right when updating Orderline ? >>>>>> >>>>>> 1.Get TimeStamp of Order >>>>>> 2. Get TimeStamp of OrderLine >>>>>> 3. User changes orderline >>>>>> 4. Check if Order was changed meanwhile >>>>>> 4. 1 If yes there is a conflict since somebody changed order while I >>>>>> was updating OrderLine >>>>>> 5. Check if OrderLine was changed >>>>>> 5.1 If yes there is a conflict since somebody changed OrderLine line >>>>>> while I was updating OrderLine >>>>>> 6. Update OrderLine >>>>>> 7. Touch Order >>>>>> >>>>>> or there is not need to check for Order Timestamp >>>>>> when updating OrderLine in this case >>>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics