51

Re: Locks in a business layer

Hello, IB, 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. IB> if complexity grows from operation at storage level, something means you do not so, should be equal on the contrary. I meant when locks  transactions at basis level they, locks, quickly get out of hand. We admit all it is comprehensible works on such locks. Here there is a new feature, in the list of checks reading of new essence (so also lock) is added. One more feature, then one more is then added. And it not in one business action, and in some. As a result of check lock conditionally half-bases and "greetings, !" Gets out of hand - because writers of the business code do not know that there the basis blocks. It is an individual question for which likely it is necessary competent DBA. 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. IB> 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. Here I will dare to disagree. Let our business code finds any essence in serializable-transaction from basis (some lines from pairs of tables). What will the basis blocks? Only the read lines? Any pages (i.e. in advance unpredictable dial-up of entities)? All table (all entities)? Here I cannot answer this question, it it is necessary to be dug in very deeply in basis. That is for me automatic locks of storage - a thing unpredictable so such decision adequate you will not name. If locks not automatic, predicted unique argument against basis I see a productivity question. What for to run in basis if it is possible  in storage? Scalability (some app-servers) on is required. IB> 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. Interesting, did not know, thanks.

52

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Or not that meant? It if all  the data is saved with the order. If is not present - undertake id records of history and are substituted in the order. I.e. the order is saved strictly in that state in what it was confirmed with the user. P_K> 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. Here it does not work. It is impossible "to cancel the clashing order" simple command of the left foot of the developer. Or such biz-process is justified by real needs of business and described in , or the one who was engaged in requirement analysis faked And if , in 99 % of cases there will be or the implicit consent with the updated offer (as contracts change the majority of providers), or the inference  (explicit acknowledgement of changes of the order). 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. P_K> that such "a mismatch window"? Roughly speaking - expected time between "was obtained by the data" and "saved reversely". I.e. to check, at saving failure (something exchanged) to show updated given to the user and so on the new. P_K> 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. Well, earlier about "it is shown to the user" to speech did not go, it was a question about locks. In a case "it is shown to the user" locks do not roll for the clear reasons, only optimistic concurrency.

53

Re: Locks in a business layer

Hello, Sinix, you wrote: S> Hello, Poul_Ko, you wrote: P_K>> Or not that meant? S> it if all  the data is saved with the order. If is not present - undertake id records of history and are substituted in the order. I.e. the order is saved strictly in that state in what it was confirmed with the user. And what for? I in that plan that it am not required to business. Business should see - the order is confirmed, the order under contract , means treaty provisions and the order do not contradict each other. A point. Any versions and stories of records it is not necessary for them. P_K>> 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> here it does not work. It is impossible "to cancel the clashing order" simple command of the left foot of the developer. Or such biz-process is justified by real needs of business and described in , or the one who was engaged in requirement analysis faked And here the developer? The order is cancelled by the user. I write, users 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. P_K>> that such "a mismatch window"? S> Roughly speaking - expected time between "was obtained by the data" and "saved reversely". I.e. to check, at saving failure (something exchanged) to show updated given to the user and so on the new. And how to catch here it is saving failure ("something exchanged") in the multi-threaded environment without locks? P_K>> 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. S> well, earlier about "it is shown to the user" to speech did not go, it was a question about locks. In what a problem with ? We change the contract, for check we search for all orders on it. They at us are, are already loaded, found clashing - everything, the paragraph, changes are not accepted, business operation is completed, its result - "was not possible + the list of orders".  and actions of the user on correction go the turn, as normal operations, out of business operation of change of the contract. About it it has been written.

54

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: S>> It if all  the data is saved with the order. If is not present - undertake id records of history and are substituted in the order. I.e. the order is saved strictly in that state in what it was confirmed with the user. P_K> and what for? I in that plan that it am not required to business. Business should see - the order is confirmed, the order under contract , means treaty provisions and the order do not contradict each other. A point. Any versions and stories of records it is not necessary for them. It is required. For example, for retail trade in sm of item 1 of item 500  the Russian Federation 1. The buyer is obliged to pay the goods for the price declared by the seller at the moment of the inference of the contract of retail purchase and sale if other is not provided by the law, other legal acts or does not follow from an obligation being. I.e. we store strictly that the user confirmed, and, , at saving we do check and we ask to confirm the new price. Otherwise in any way. S>> Here it does not work. It is impossible "to cancel the clashing order" simple command of the left foot of the developer. Or such biz-process is justified by real needs of business and described in , or the one who was engaged in requirement analysis faked P_K> And here the developer? The order is cancelled by the user. I write, users should make it explicitly!  also I am sorry. Incorrectly understood S>> Roughly speaking - expected time between "was obtained by the data" and "saved reversely". I.e. to check, at saving failure (something exchanged) to show updated given to the user and so on the new. P_K> and how to catch here it is saving failure ("something exchanged") in the multi-threaded environment without locks? Through optimistic concurrency - update transits only if the order state (for example, ) did not exchange. S>> Well, earlier about "it is shown to the user" to speech did not go, it was a question about locks. P_K> in what a problem with ? It is impossible to retain lock for the period of show. The user braked for 5 minutes - all other bound operations rose for the same 5 minutes.

55

Re: Locks in a business layer

Hello, Sinix, you wrote: S> It is required. For example, for retail trade in sm of item 1 of item 500  the Russian Federation it is not applicable. Here a situation another, I described requirements, let's the not add S>>> Roughly speaking - expected time between "was obtained by the data" and "saved reversely". I.e. to check, at saving failure (something exchanged) to show updated given to the user and so on the new. P_K>> and how to catch here it is saving failure ("something exchanged") in the multi-threaded environment without locks? S> through optimistic concurrency - update transits only if the order state (for example, ) did not exchange. And the state of the order also does not exchange. The state of the bound contract exchanges. Or suggest to change at contract change versions of all orders connected to it which even have been not confirmed? It will be somehow strange... S>>> Well, earlier about "it is shown to the user" to speech did not go, it was a question about locks. P_K>> In what a problem with ? S> It is impossible to retain lock for the period of show. The user braked for 5 minutes - all other bound operations rose for the same 5 minutes. Locks will not be. The list of orders is a result of operation, instead of its part. For a while  it is locked nothing, and time of it is not present. It is simple a window "Ups! We can not change validity date - on it there are the confirmed orders getting to an interval when the contract ceases to operate. These are orders NoNo 1, 2 and 5.", and one button "Close".

56

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: S>> It is required. For example, for retail trade in sm of item 1 of item 500  Russian Federation P_K> it is not applicable. Here a situation another, I described requirements, let's the not add If business works with purchases sooner or later the similar requirement precisely appears. Similar rules of trade are practically in all countries. P_K> or suggest to change at contract change versions of all orders connected to it which even have been not confirmed? It will be somehow strange... . Or any other method which allows to define the conflict at saving. Will notify differently the client on treaty provision changes it is problematic.

57

Re: Locks in a business layer

58

Re: Locks in a business layer

Hello, IB, you wrote: IB> 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. Yes, if application is rigidly anchored to one DBMS. No if support of several DBMS, including what do not give such mechanism is necessary. In a case 1 it so. 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. IB> Separate plus of a lock manager which is built in a DB consists that DB transactions know about it. Yes, but it does not help with the above described scenario.

59

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Thanks, interesting. P_K> if I correctly understood, a decision essence in an exception of a source of a problem - multi-threaded execution. All operations are fulfilled sequentially, round it the certain infrastructure raising productivity is wound. P_K> I Am afraid that for the project in its current state passage to such model demands too many forces and time. It is possible to pass stage by stage. To begin with that simply to enter event queue, then to it to write, without changing functionality, then to learn to lose events and stage by stage to translate the project to new rails.

60

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Application is ? Yes P_K> "contract Acknowledgement" - are not present such in business process, order acknowledgement meant? Sorry, everywhere instead of the contract the order meant. P_K> P_K> if (GetActualContractVersion (contract. Id) == contract. Version) then CommitTransaction () else RollbackTransaction (); P_K> P_K> on idea all last line should fulfill  directly at basis level then the decision will be working, agrees. But how it such to make? Yes, it is equal I and meant (as already explained more low). With order versions it is not necessary to lock all basis for check of its status, there is enough that from the moment of the beginning of check the order version did not change, then change of the status of the order on "is confirmed" it is possible to make with table lock of orders (or even the specific order in the table) in  or in the code. The profit that this operation short also does not affect/not locks other tables.

61

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> It is admissible all it is comprehensible works on such locks. Here there is a new feature, in the list of checks reading of new essence (so also lock) is added. One more feature, then one more is then added. And it not in one business action, and in some. As a result of check lock conditionally half-bases and "greetings, !" No if with basis to work normally. Here in the same way as in the application-oriented code if to write without looking back at that that is already made, and as as works, ghost effects sooner or later get. And if you understand as all it is arranged, and all is rather purely designed, no problems with changes are present. P_K> Gets out of hand - because writers of the business code do not know that there the basis blocks. It is an individual question for which likely it is necessary competent DBA. Not DBA, and the developer who knows what to do with basis... Here all the same it is strange, why it is considered that the application-oriented code it is necessary  and to write accurately, and the basis correctly saves in the magic image all. P_K> here I will dare to disagree. Let our business code finds any essence in serializable-transaction from basis (some lines from pairs of tables). What will the basis blocks? Only the read lines? Any pages (i.e. In advance unpredictable dial-up of entities)? All table (all entities)? Here I cannot answer this question, it it is necessary to be dug in very deeply in basis. Exactly. The problem not that the basis is unpredictable, and that you do not want to study this question and to understand as the basis works to understand as in what case will be locked. P_K> if locks not automatic, predicted unique argument against basis I see a productivity question. What for to run in basis if it is possible  in storage? Scalability (some app-servers) on is required. If it is not required to scalability the productivity question should not excite. The simple decision to a forehead - drive all yours  through one global , should be norms, cheap but good. To write the lock manager, especially without having  as it works in bases... Yes to understand as a DB works easier.

62

Re: Locks in a business layer

Hello, wildwind, you wrote: W> Yes if application is rigidly anchored to one DBMS. Not rigidly to one DBMS, and there is no requirement to work simultaneously with several DBMS. And applications without such requirements of 99.9 % W> Are not present, if support of several DBMS, including what do not give such mechanism is necessary. In a case 1 it so. I also meant it, when said that at 1 it not from good life. But writing of own lock manager the task completely not trivial.  not 1 writes. W> yes, but it does not help with the above described scenario. Should help, as all is used the same lock manager he just knows, what lines concern the blocked objects. Differently, your requirement about change of business objects through a lock manager is fulfilled automatically.

63

Re: Locks in a business layer

Hello, IB, you wrote: IB> Hello, Poul_Ko, you wrote: IB> to Write the lock manager, especially without having  as it works in bases... Yes is easier IB> to understand as a DB works. Basically it not so is added, almost for all such model suffices://lock Obtaining public void Lock (Object data, Write/Read mode, Object owner)//removal of all locks of the given owner public void UnLock (Object owner); Responsibility for execution of locks lies on  the code, i.e. always before to touch object, we receive on it lock. And here the transaction manager it already more challenging task.

64

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> All kind P_K> Now I will sound a problem, for certain I not the first who face it also decisions for a long time already are known, but is visible they to me did not come across or were not remembered. P_K> it is a question of the task of support of an invariance of a state of business objects till the end of business operation in the multiple user environment. P_K> we present that there is any operation, on business to rules it can be successfully fulfilled only at a certain state of any objects. How we implement it? In the code at first we read these objects, we check their state and if it corresponds to business rules we fulfill operation. In the multiple user environment here there can be problems: the state of checked objects can change between reading of objects and operation performance that as a matter of fact it leads to performance of operation with violation of business rules. Gravity of such violation can vary is is sometimes admissible, and sometimes is not present (on one place is sold two tickets). P_K> For avoidance of such situation it is logical to fulfill lock of the read objects till the end of operation so that anybody another could not change their state. P_K> Here there is no speech about check a state of the changeablest object - the decision through "optimistic lock" is known. I speak about stability of a state of other logically bound entities, which do not change operation. P_K> the first decision which basically already works, this usage of serializable-transactions on basis. Here as a matter of fact all locks are fulfilled by basis. Scales and volume of locks in basis as a whole  that somehow it is necessary to affect them densely to sit down structure of basis and requests. P_K> this decision has still lacks: Appear  in process of growth of loading and an amount of the involved tables, does not envelop the data which are not read from basis (are in  - reference manuals). P_K> What other decisions can advise? Philosophically - no other decisions are present. You give an example competing changes. All methods of the decision of this problem are known for a long time. Implementation details differ only. For example, "" which you so are afraid are, actually, detection of the conflict of changes. Its display in the world of pessimistic locks. Whatever method you selected, at you the situation "oh is all the same possible, meanwhile, as we showed you the price, and your pushing button Save, there were changes. Will repeat?" . In pessimistic locking it will be just deadlock. In Optimistic locking it will be "a discrepancy ". Your mention about optimistic locking is connected not to problems OL as that, and with its standard incorrect implementation. Fair implementation OL should  not only records, but also readings. For the obvious reasons. But it - is very expensive and inconvenient. Therefore instead of fair OL normally apply the poor. And it works only because is applied, as a rule, to systems where there is an unidirectional dependence between the data. That is if we have a clashing change it in competing transaction is obliged to be applied and to our object. Well, type at modification of the price-list, in the same transaction all not confirmed orders are corrected also. And at attempt to make the order confirmed, we fly on discrepancies of versions of the order. The most constructive method - to transfer it is as much as possible logic in basis, and to lean against its transactions. That time of "intersections" of operations with each other was less. This decision is frequent the logic in  "associates with a word combination". It is optional, it is possible also modern means to use, but the interval between data reading and their usage should be as it is possible more shortly. In  it reduces an amount of rollbacks, in  reduces down time on waiting of locks and frequency .

65

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> What there will be your candidate solutions? It is classical race condition which it is impossible  without provision one-continuous write-access to all data model. It can be made by level transactions serializable, architecture LMAX,  with lock of all"and other methods. In a case of multi-threaded access, we can be played by probabilities and delay period of update of the prices in orders, anything more.

66

Re: Locks in a business layer

Hello, IB, you wrote: IB> For this problem of other decision is not present. Is, for transactions - it is one of tools of exclusive access to . Above already mentioned LMAX - one more tool.

67

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Or to transfer locks from basis on level above - to a business layer. Here on purpose  such decision I also started the given subject. Here it is that case when treatment is worse than illness. Normally to implement  in business a layer - rather nontrivial problem, safely multiply approximate estimates on 10 and add 100 % probability to have non-reproducible bugs months.

68

Re: Locks in a business layer

Hello, itslave, you wrote: P_K>> Or to transfer locks from basis on level above - to a business layer. Here on purpose  such decision I also started the given subject. I> here it is that case when treatment is worse than illness. Normally to implement  in business a layer - rather nontrivial problem, safely multiply approximate estimates on 10 and add 100 % probability to have non-reproducible bugs months. Yes here at all of us it is simple - any not-read the app-layer method always turns around in transaction, and either is entirely fulfilled, or is not present. In any partial rollbacks or  we do not admit. In other words, the bizens-layer at all does not know about transactions and not  them, they outside. Under the agreement any exception leads to transaction rollback.

69

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Yes here at all of us it is simple - any not-read the app-layer method always turns around in transaction, and either is entirely fulfilled, or is not present. In any partial rollbacks or  it is not admitted. P_K> in other words, the bizens-layer at all does not know about transactions and not  them, they outside. Under the agreement any exception leads to transaction rollback. On everyone  to lock all? Rest against performance quickly.

70

Re: Locks in a business layer

Hello, itslave, you wrote: P_K>> Yes here at all of us it is simple - any not-read the app-layer method always turns around in transaction, and either is entirely fulfilled, or is not present. In any partial rollbacks or  it is not admitted. P_K>> in other words, the bizens-layer at all does not know about transactions and not  them, they outside. Under the agreement any exception leads to transaction rollback. I> on everyone  to lock all? Rest against performance quickly. It becomes for elementary quality support. In an ideal we see usage of transactions only for elementary quality and insulation (READ COMMITTED), and locks through something another, for example, the mechanism in a business layer.

71

Re: Locks in a business layer

Hello, IB, you wrote: W>> Yes, but it does not help with the above described scenario. IB> should help, as all is used the same lock manager he just knows, what lines concern the blocked objects. Differently, your requirement about change of business objects through a lock manager is fulfilled automatically. It is possible more in detail? As far as I know, the locks received sp_getapplock, are not connected in any way to any objects in a DB. From semantics it is completely defined by the application code. But can, I do not know something...

72

Re: Locks in a business layer

Hello, wildwind, you wrote: W> It is possible more in detail? As far as I know, the locks received sp_getapplock, are not connected in any way to any objects in a DB. From semantics it is completely defined by the application code. But can, I do not know something... Itself did not use, but from the documentation understood that lock always is anchored to something: either to transaction, or to session.

73

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Itself did not use, but from the documentation understood that lock always is anchored to something: either to transaction, or to session. Speech not about it. Esteem all ours with IB a discussion branch.

74

Re: Locks in a business layer

75

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. Enough repeatable read. P_K> it will be finite in the right part actually not such update - there will be at first SELECT (search of all not confirmed orders), and then a series of updates - on one on each order. But it is basic changes nothing, even on the contrary, raises probability such logical . It is not necessary so to do.