26

Re: Locks in a business layer

27

Re: Locks in a business layer

Hello, MozgC, you wrote: MC> the Problem can be solved at DB level. For example in transaction with level Repeatable Read... It seems you understood a problem! About the decision on transactions was in the very first message. Yes, it works, but not absolutely And has the minuses.

28

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> At the client one action - to create the new not confirmed order. This order forms and dangles in system while it do not confirm or do not cancel. And while it dangles not confirmed the price in it always should correspond strictly to the price from the reference manual. P_K> it is quite admissible that before creation of the new not confirmed order on the screen the user saw one price, and after creation it became another. I, on a place of the client, did not estimate such humour. It already a question to analysts who do not understand whether that it is necessary to them whether as the software functions. If the price in not confirmed order has to undertake always from the reference manual - and take it always from the reference manual, instead of store in the order. The problem is solved) One of the most simple solutions of a problem of a mismatch in system - not to double the data. And  if needed, but  it is explicit, with control of lifetime and possibility  if needed. As to an initial question, besides integration  that already advised, it is possible  more selectively. In an example with a cinema, serializable it is not too terrible if to restrict in its lines for a specific hall. Or generally to get the separate table specially under  and to capture their hands in .

29

Re: Locks in a business layer

Hello, scf, you wrote: scf> Hello, Poul_Ko, you wrote: P_K>> At the client one action - to create the new not confirmed order. This order forms and dangles in system while it do not confirm or do not cancel. And while it dangles not confirmed the price in it always should correspond strictly to the price from the reference manual. P_K>> it is quite admissible that before creation of the new not confirmed order on the screen the user saw one price, and after creation it became another. scf> I, on a place of the client, such humour did not estimate. It already a question to analysts who do not understand whether that it is necessary to them whether as the software functions. Requirements is that that we have. Though to tell the truth, in the described example I do not see anything strongly contradicting common sense. In real system orders, and something another are certainly twisted not, and there such requirement to behavior looks quite logically. scf> If the price in not confirmed order should undertake always from the reference manual - and take it always from the reference manual, instead of store in the order. The problem is solved) This candidate solution is already transited... Once again I will repeat - in a reality all sounded need to be multiplied by ten and to look at a question more widely. Present that possibility of creation of the order depends on a state of other entities which can change parallely. The example absolutely from a finger - the new order cannot be created, if the client already has two not confirmed orders. But after all the second order can create parallely some users, as a result of orders there are more than two that is violation of requirements. And here already the decision "not to double the data" does not climb in any way.

30

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: S>> Even the modest basis on pair millions orders and process of update of the price becomes very interesting occupation. Especially if the letter with the previous price is already sent the client. P_K> no letters are present, do not invent Well and if appear? We tell, reminders to the client that he forgot to pay the typed basket if the basket is typed, but is not paid in flow of days. Once again, you pursue purely spherical requirements which in real life are not necessary. And thus make decisions which lock or complicate implementation of real requirements. P_K> check speaks that in the order the price old (100500), and in a price - new (100600). What do you answer users when they come running to you with a question "as so?"? Tell "so transactions were added"?" It is unimportant "? I will answer that it is the price at the moment of purchase and that it will be updated at following display of the order to the user. If it would be desirable to have always the actual price - it is necessary to store in the order not Cost, and OrderPriceId + to consider order cost dynamic. Or to store hash o rowversion all dependent fields and to enumerate order cost at its mismatch. Anyway cost of not paid order - a piece a little ephemeral (on determination) and to try to stretch on it any restrictions except eventual consistency - business very ungrateful. P_K> now we change isolation level on SERIALIZABLE. All earns as it is necessary - necessary locks will be done by basis. Than it is bad - the first message see. Even with serializable not the fact without additional . The most simple example - at each currency the table, the price is considered in several currencies while we consider the price in one currency, for the second history record was updated. Well, i.e. We are returned to with what began - at change of the majority of reference manuals it is necessary to enumerate a heap of orders repeatedly. Not the best decision.

31

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> the Example absolutely from a finger - the new order cannot be created, if the client already has two not confirmed orders. But after all the second order can create parallely some users, as a result of orders there are more than two that is violation of requirements. And here already the decision "not to double the data" does not climb in any way. In the given specific example if 99.5 % of users cannot create the third order, and at 0.5 % the third order appears, all endure it. On determination multitask both asynchronous and the modern systems knowingly prefer the real world eventual consistency. Still an example from the same opera - from us often demand 100 % safety of the gated in data. And when in such system many forces and means are already invested, it appears that in case of need for the last days girls can hammer the data from primary documents. - to draw order cost in  small letters "preliminary cost, final will be defined at the moment of order acknowledgement". To make  orders going bad during, say, days. Probably, all your problems simply because of insufficient qualification or weakness of the architect. It is possible any example from real life?

32

Re: Locks in a business layer

Hello, Sinix, you wrote: P_K>> Check speaks that in the order the price old (100500), and in a price - new (100600). What do you answer users when they come running to you with a question "as so?"? Tell "so transactions were added"?" It is unimportant "? S> I Will answer that it is the price at the moment of purchase and that it will be updated at following display of the order to the user. That is your candidate solution - start up it is stored incorrectly, we will enumerate on everyone . S> If it would be desirable to have always the actual price - it is necessary to store in the order not Cost, and OrderPriceId + to consider order cost dynamic. Answered the same sentence in an adjacent branch  scf. Look at a question more widely. Creation of the order can depend on the changing data, here recalculation does not dare. S> or to store hash o rowversion all dependent fields and to enumerate order cost at its mismatch. Anyway cost of not paid order - a piece a little ephemeral (on determination) and to try to stretch on it any restrictions except eventual consistency - business very ungrateful. Looks terribly and difficult P_K>> Now we change isolation level on SERIALIZABLE. All earns as it is necessary - necessary locks will be done by basis. Than it is bad - the first message see. S> even with serializable not the fact without additional . The most simple example - at each currency the table, the price is considered in several currencies while we consider the price in one currency, for the second history record was updated. Through locks I hope that it is possible to solve all Before order creation we lock  all changes at the prices and currencies, upon termination of - lock we release. If someone wants to change an exchange rate - it makes it strictly after order creation, and all orders will be enumerated, new orders cannot be created at this time - thanks to the same lock. Like all it is beautiful. S> well, i.e. we are returned to with what began - at change of the majority of reference manuals it is necessary to enumerate a heap of orders repeatedly. Not the best decision. It is a lack of the given example. Consider that a heap small and recalculation is not something heavy.

33

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> About the decision on transactions was in the very first message. Yes, it works, but not absolutely And has the minuses. For this problem of other decision is not present. Simply with transactions it is necessary to work more accurately. At first, Serializable it is not mandatory, it normally does not reach. And secondly, the volume of changes in transaction should be minimized, and all will be predicted, without everyones .

34

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Now we change isolation level on SERIALIZABLE. All earns as it is necessary - necessary locks will be done by basis. Than it is bad - the first message see. It is not necessary Serializable, it is enough to add  UPDLOCK in first SELECT. That is the decision, in that type in which you want, consists in accurater operation with locks and requests at sequel level.

35

Re: Locks in a business layer

Hello, scf, you wrote: scf> It is possible any example from real life? Let's look at something more approximate to a reality. Let the same orders can be paid for the contract the legal person. The link to the contract is underlined in the order. The in itself contract is difficult essence, in it difficult restrictions on those goods which can be paid are put. For example, these goods can, this cannot, and here that is in number of no more 10 under the given contract (totally under the made orders). The contract has the action period, and the orders confirmed during this period can be paid for it only. A certain analog of the insurance policy in real life. The business requirement: at the moment of order acknowledgement to check up all conditions on the contract (period of validity, restrictions on the goods) if something contradicts - the order does not prove to be true. All properties of the contract - period of validity, restrictions - can freely be edited. It is impossible to change properties so that as a result there will be the confirmed orders contradicting properties. On not confirmed  - they simply then do not prove to be true. So, we write the code for a method of acknowledgement of the order. First of all we find essence of the contract, the second - we check its period of validity and restrictions. For check of restrictions a-lja "no more than 10 under the given contract" it is necessary to subtract still all other orders under this contract on the data the goods and  an amount to understand how many from 10 still it is possible. Nobody will deny that during all these checks other operation changing something from already checked up can be fulfilled. For example, changing validity dates so that the processed order does not get to them. In this case we receive the confirmed order breaking the treaty provisions. We will not write in small print as here offered, "Treaty provisions are checked up inaccurately, recheck once again!" And to recheck conditions at order display is too delirium, I think all agree. Here to you a real situation. Improbable, yes, but real. Probably yes, "all worry", but unfortunately to solve it not to developers, and extreme there will be they. After all the requirement is, and formally a software it did not fulfill.

36

Re: Locks in a business layer

Hello, IB, you wrote: IB> For this problem of other decision is not present. IB> simply with transactions it is necessary to work more accurately. At first, Serializable it is not mandatory, it normally does not reach. And secondly, the volume of changes in transaction should be minimized, and all will be predicted, without everyones . It is necessary, it agree. But complexity grows as a snow clod and it is all quickly quits from under control, when it at basis level. Similar that the architectural decision doing as a matter of fact the same locks that is required the DBMS, but in business layer terms is able. I hope that as a result of lock become more predicted and visible.

37

Re: Locks in a business layer

38

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> That is your candidate solution - start up it is stored incorrectly, we will enumerate on everyone . , I right at the beginning resulted the variant - we store that price which was confirmed with the buyer while the buyer did not confirm - at all we do not store. P_K> looks terribly and difficult Yes, certainly. And the task uneasy: you store as a matter of fact the promise (promise) and try to store together with it actual cost. A dirty trick that this actual cost concerns some in advance unknown moment in the future. To some stage (the contract inference) to consider the sale price generally washed off is not present. Everything that it is possible - to show preliminary cost which can change in the course of final design of the order (for example, a choice of one of a dial-up of bonuses or adding of an individual discount from the manager). P_K> Through locks I hope that it is possible to decide all Yes, of course, but with risk that all buries in this lock. Here as always - either we search for the compromise, or we brake S>> Well, i.e. we are returned to with what began - at change of the majority of reference manuals it is necessary to enumerate a heap of orders repeatedly. Not the best decision. P_K> it is a lack of the given example. Consider that a heap small and recalculation is not something heavy. Well, then I would make lock at application level to begin with. The decision oak, but allows though approximately to estimate overall performance at worst.

39

Re: Locks in a business layer

40

Re: Locks in a business layer

Hello, Sinix, you wrote: S> Yes, certainly. And the task uneasy: you store as a matter of fact the promise (promise)... The Example has been strongly simplified. The example about acknowledgement of the order and the contract the Author see: Poul_Ko Date: 28.09 10:59. Here already there is no "promise" - we or consider the order corresponding to the contract and we confirm, or is not present. P_K>> Through locks I hope that it is possible to decide all S> Yes, of course, but with risk that all buries in this lock. Here as always - either we search for the compromise, or we brake Well here and we search

41

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> the Version will be the same if changing transaction yet , it goes parallely. And who guarantees that after such check but before  nobody changes the contract? , conditions of application of transactions should be such: * for contract acknowledgement - the version did not change. * for contract change - the contract remained in the status  and the version did not change. In this case changing transaction  to come to an end with an error if the contract in the status "is confirmed" or has been changed by parallel transaction. Or it is possible to increase the version at contract acknowledgement then both operations are admissible if the version from the operation beginning did not change.

42

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> the Example has been strongly simplified. The example about acknowledgement of the order and the contract the Author see: Poul_Ko Date: 28.09 10:59. Here already there is no "promise" - we or consider the order corresponding to the contract and we confirm, or is not present. In this case all is elementary. By preparation of the order for acknowledgement by that or otherwise we do  the given dictionaries, it and we store at acknowledgement. In 99 % of cases from the point of view of business a situation "the price exchanged 20 minutes prior to the contract inference" nothing the price differs from "exchanged in 20 minutes after the inference". In those rare situations when it not so, is produced  before the inference of the contract which reduces a window of a mismatch to 2. 3 minutes. In absolutely desperate cases price correction (or contract cancellation) through separate business operation.

43

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Hello, scf, you wrote: scf>> It is possible any example from real life? P_K> let's look at something more approximate to a reality. P_K> let the same orders can be paid for the contract the legal person. The link to the contract is underlined in the order. The in itself contract is difficult essence, in it difficult restrictions on those goods which can be paid are put. For example, these goods can, this cannot, and here that is in number of no more 10 under the given contract (totally under the made orders). The contract has the action period, and the orders confirmed during this period can be paid for it only. A certain analog of the insurance policy in real life. P_K> the Business requirement: at the moment of order acknowledgement to check up all conditions on the contract (period of validity, restrictions on the goods) if something contradicts - the order does not prove to be true. P_K> All properties of the contract - period of validity, restrictions - can freely be edited. It is impossible to change properties so that as a result there will be the confirmed orders contradicting properties. On not confirmed  - they simply then do not prove to be true. P_K> so, we write the code for a method of acknowledgement of the order. P_K> first of all we find essence of the contract, the second - we check its period of validity and restrictions. For check of restrictions a-lja "no more than 10 under the given contract" it is necessary to subtract still all other orders under this contract on the data the goods and  an amount to understand how many from 10 still it is possible. P_K> Nobody will deny that during all these checks other operation changing something from already checked up can be fulfilled. For example, changing validity dates so that the processed order does not get to them. In this case we receive the confirmed order breaking the treaty provisions. P_K> we will not write in small print as here offered, "Treaty provisions are checked up inaccurately, recheck once again!" And to recheck conditions at order display is too delirium, I think all agree. P_K> here to you a real situation. Improbable, yes, but real. Probably yes, "all worry", but unfortunately to solve it not to developers, and extreme there will be they. After all the requirement is, and formally a software it did not fulfill. It seems that for a reliable solution of a problem it is enough to get rid from COUNT () in an order confirmation code. For example, to store the bought limits directly in the contract. Then suffices READ_COMMITED. One more variant - to conduct  contracts. At modification there is a separate version (by the way, editing of the active contract looks strange, normally to it do ), and orders refer to that version of the contract from which they have been created. Generally all it looks as one more argument in favor of storage of difficult business objects in basis in the form of JSON. Besides flexibility, and fast vyborok/updatings, we receive still atomic operations over a business object with minimum kol-vom .

44

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> What other decisions can advise? P_K> to write the lock manager? There are examples? Yes, if it is really justified. Examples are. A platform 1 - there the lock manager, in addition to DBMS locks. He allows to block an application entity and to manage one lock instead of set of lines in several tables. The amount of locks in basis thus decreases. Besides, such useful things, as lock of set of lines on a condition are possible. For example (if to take one of your examples), at us statuses of orders are stored in the table. And we can block one lock all not confirmed orders of one client. Or even groups of clients. If all it to lock in basis, it will be slowly and heavily for basis. But it is necessary to pay for it expensive price. All changes business of objects should go through this  locks. For example, we cannot fulfill difficult multiline UPDATE or DELETE as our lock manager cannot check up, whether the lines affected by it concern the blocked objects. Because of it productivity on record strongly suffers. Well and correctly to implement such manager uneasy. For example, a classical problem: when to release locks zavisshego / the fallen off client?

45

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> It is necessary, it agree. But complexity grows as a snow clod and it is all quickly quits from under control, when it at basis level. If complexity grows from operation at storage level, something means you do not so, should be equal on the contrary. P_K> it is similar that the architectural decision doing as a matter of fact the same locks that is required the DBMS, but in business layer terms is able. I hope that as a result of lock become more predicted and visible. On the contrary, in this case all generally quits from under control, or will be strictly one-continuous and not scaled. The storage is a unique place where it is possible more or less adequately  problems with  the data. Basically, in each DB there is a possibility to use the mechanism of locks for needs of an application-oriented layer. For a sequel it is pair of procedures sp_getapplock/sp_releaseapplock, but it is equal about the same, simply allows to synchronize also entities which directly are not stored in a DB.

46

Re: Locks in a business layer

Hello, wildwind, you wrote: W> Yes if it is really justified. Examples are. A platform 1 - there the lock manager, in addition to DBMS locks. In 1 it not from good life. And 1 it is difficult to name the sample of the correct architecture. W> he allows to block an application entity and to manage one lock instead of set of lines in several tables. The amount of locks in basis thus decreases. Besides, such useful things, as lock of set of lines on a condition are possible. For example (if to take one of your examples), at us statuses of orders are stored in the table. And we can block one lock all not confirmed orders of one client. Or even groups of clients. If all it to lock in basis, it will be slowly and heavily for basis. For all it it is not necessary to write the lock manager. In normal bases it is normally given allowing to get the mechanism access to a lock manager of basis and to use its services for lock of application entities. To use it if there will be such need much more correctly, than to fence own manager. W> but it is necessary to pay for it expensive price. All changes business of objects should go through this  locks. For example, we cannot fulfill difficult multiline UPDATE or DELETE as our lock manager cannot check up, whether the lines affected by it concern the blocked objects. Because of it productivity on record strongly suffers. Separate plus of a lock manager which is built in a DB consists that DB transactions know about it.

47

Re: Locks in a business layer

Hello, XuMuK, you wrote: P_K>> the Version will be the same if changing transaction yet , it goes parallely. And who guarantees that after such check but before  nobody changes the contract? XMK> Kmk, conditions of application of transactions should be such: XMK> * for contract acknowledgement - the version did not change. Something I lost thought... What do you understand under "conditions of application of transactions"? The transaction beginning I know,  and  I know. Application is ?" Contract acknowledgement "- are not present such in business process, order acknowledgement meant? How it will look? Give in the pseudocode: BeginTransaction (); var order = FindOrder (); var contract = FindContractForOrder (order); VerifyContractConditions (contract, order); order. Status = Confirmed; UpdateOrder (order); if (GetActualContractVersion (contract. Id) == contract. Version) then CommitTransaction () else RollbackTransaction (); On idea all last line should fulfill  directly at basis level then the decision will be working, agrees. But how it such to make? If version check and  is two separate requests to basis it solves nothing, in between the contract can exchange. XMK> * for contract change - the contract remained in the status  and the version did not change. XMK> In this case changing transaction  to come to an end with an error if the contract in the status "is confirmed" or has been changed by parallel transaction. Or it is possible to increase the version at contract acknowledgement then both operations are admissible if the version from the operation beginning did not change. Stop, at the contract is not present the status, the status at the order.

48

Re: Locks in a business layer

Hello, Sinix, you wrote: P_K>> the Example has been strongly simplified. The example about acknowledgement of the order and the contract the Author see: Poul_Ko Date: 28.09 10:59. Here already there is no "promise" - we or consider the order corresponding to the contract and we confirm, or is not present. S> In this case all is elementary. By preparation of the order for acknowledgement by that or otherwise we do  the given dictionaries, it and we store at acknowledgement. It basically and happens, if I correctly understand your thought. All necessary records are found in an acknowledgement method for check, they lie in storage, it and is certain , on them the logic works. The logic fulfilled, the order is confirmed, saved in basis, transaction is completed,  died. Or not that meant? S> in 99 % of cases from the point of view of business a situation "the price exchanged 20 minutes prior to the contract inference" nothing the price differs from "exchanged in 20 minutes after the inference". Under requirements is not present. Conditions change before acknowledgement - the changed conditions will be checked up at acknowledgement, the order because of it can not prove to be true - demands the additional coordination in connection with treaty provision change. Conditions change after acknowledgement - if they contradict already confirmed orders the software rejects changes, users should eliminate at first a problem, for example, to cancel clashing orders. But they should make it explicitly. S> in those rare situations when it not so, is produced  before the inference of the contract which reduces a window of a mismatch to 2. 3 minutes. What is "a mismatch window"? S> In absolutely desperate cases price correction (or contract cancellation) through separate business operation. It also is separate business operation, with the rules. At correction of treaty provisions there is a check of all confirmed orders under the given contract if there are orders which go to a section with new conditions corrections do not happen, the list is shown to the user and business operation is completed. The user should correct orders and try to change once again treaty provisions.

49

Re: Locks in a business layer

50

Re: Locks in a business layer

Hello, scf, you wrote: scf> It seems that for a reliable solution of a problem it is enough to get rid from COUNT () in an order confirmation code. For example, to store the bought limits directly in the contract. Then suffices READ_COMMITED. Yes, to it we can reduce dependences to two entities - the order and the contract. The problem with an amount becomes a problem of the same order, as well as a problem with action date. Yes, to solve it it is possible through READ_COMMITED. Basically it can be the first step to a solution of a problem - to lower isolation levels with SERIALIZABLE to READ_COMMITED, at first fulfilling changeover of all similar conditions (on kol-in, existence) on value check. It is not assured that such changeover always will be possible, but probably it is necessary to try. Is interesting to learn the forecast under such decision - it becomes how much easier? Whether it becomes strong less ? Hardly someone tells... Well and besides it does not solve a problem dataful, received not from a DB. scf> One more variant - to conduct  contracts. At modification there is a separate version (by the way, editing of the active contract looks strange, normally to it do ), and orders refer to that version of the contract from which they have been created. Such decision should be accepted business, and it is not required to it.