76

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> That happens in ? One of transactions will be  with an error, the second - will successfully be completed. S> that happens in optimistic? One of transactions will successfully be completed, the second falls with timestamp does not match or that there is thrown out in Can seem that the choice between pessimistic and optimistic locking is a dichotomy, but it not so. There is also a third way - eventual consistency (in separate places). And in many cases it is comprehensible to business. By the way, at the paper registration it was the core and anything, business worked.

77

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> 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. S> it is enough repeatable read. In this specific case can be yes. But as a whole when there is a type check "something is not present" (EXISTS, COUNT) and it is necessary to provide that during transaction it remained invariable it is necessary to use serializable. Yes, we clarified that for certain such checks can be reduced to checks of values of properties and to use repeatable read, but it demands additional , up to introduction of new in essence useless entities for business. 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 . S> it is not necessary so to do. And other way I do not see. It in an example calculation idle time - to increase one by another, upon the business logic there plays. And it in the code also operates with the loaded entities. That is, recalculation  to look somehow so://to Find orders for recalculation foreach (var order = FindOrdersToRecalc (contractId)) {//to Enumerate each order _orderingService. RecalculateOrder (order);}

78

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> In this specific case can be yes. But as a whole when there is a type check "something is not present" (EXISTS, COUNT) and it is necessary to provide that during transaction it remained invariable it is necessary to use serializable. . Laziness to search for an exhaustive post. Short, : 1. Isolation levels does not happen. Insulation - it or is, or it is not present. For business "insulation is not present" is a death since it means that the data is damaged. 2. That in  name "isolation levels" is a compromise; the method to achieve serializability of transactions is cheaper, than "delivering all ". Thus the programmer makes a DBMS certain promises. Well type requesting repeatable read the programmer as though I speak "I promise that I will not re-read the data twice (or generally to use the data read"in the beginning", is closer to transaction"end"). In reply to what the DBMS speaks to itself " then I will not retain shared lock till the end of transaction". When the programmer requests "repeatable read", it means that "I will not use that fact that a certain data missed" in the beginning "transactions, transactions are closer to" the end "(in particular, I I will not rely on value select count () from... where <condition>)". On what the DBMS speaks to itself "and, well, norms, it is possible to do without key range  on interrogated ranges". 3. Violation of these promises leads to insulation violation. Alas. P_K> Yes, we clarified that for certain such checks can be reduced to checks of values of properties and to use repeatable read, but it demands additional , up to introduction of new in essence useless entities for business. Generally all entities for business are in essence useless. Question in how to solve the task means instead of how to make so that structure of tables it was similar to columns of the form of the typical contract. 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 . S>> it is not necessary so to do. P_K> and other way I do not see. It in an example calculation idle time - to increase one by another, upon the business logic there plays. And it in the code also operates with the loaded entities. That is, recalculation  to look somehow so: P_K> P_K>//to Find orders for recalculation P_K> foreach (var order = FindOrdersToRecalc (contractId)) P_K> {P_K>//to Enumerate each order P_K> _orderingService. RecalculateOrder (order); P_K>} P_K> "Operating by the loaded entities" testifies that you consider a fundamental question about insulation within the limits of any specific architecture. At first it is necessary to understand, how generally it is all looks from the point of view of a DBMS. Because it resolves that as a result "will be saved for descendants". And if programmer Vasja from the account And into the account Makes remittance "in transaction", but thus it is necessary on values of residuals on the accounts read out of this transaction the DBMS and not architecture, and personally Vasja is guilty not.  it is necessary to understand that the DBMS should see the type code begin transaction update accounts set amount=amount - transfer_amount where ID = @source_acc_id update accounts set amount=amount + transfer_amount where ID = @target_acc_id commit transaction And for the type code select @source_acc_amount = amount from accounts where ID = @source_acc_id select @target_acc_amount = amount from accounts where ID = @target_acc_id-. ... 30 seconds of activity in a middl-shooting gallery.... begin transaction update accounts set amount = new_source_amount where ID = @source_acc_id update accounts set amount = new_target_amount where ID = @target_acc_id commit transaction it deliver in the fifth normal form and will have all night long. Well, that is Vasi have also spare variants - type to try to make the mechanisms of insulation outside of a DBMS. But it is a way of the Samurai. In the core - because, as a rule, it comes to mind to those who did not master usage by ready insulation in an industrial DBMS. Chances that the implementation bungled by such person, will be correct -  are small. Therefore Vase it is necessary to concentrate on that SQL the exhaust from that interlayer which is used in its midl-dash, corresponded to the drawing. At it it is some methods: 1. To use stored procedures. For ideas of type "to operate with the loaded entities" to stand on peas for 40 minutes for idea. 2. To leave logic in a middl-shooting gallery, but to equip all data reading which will be used within the limits of business transaction, hand-to-hand locks (for example, through sp_getapplock in case of MS SQL). Or through   favourite ORM, that that  readings 3. To leave logic in a middl-shooting gallery, but to rewrite it on the advanced means like linq [2db]. I.e. to receive the functional analog of item 1, but with normal decomposition and the modern language, instead of a procedural language of the sample of 1993.

79

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> Ohh. Laziness to search for an exhaustive post. Short, : I do not want to offend, but it is all for a long time to me it is known. It agree, in many respects here an accuracy and understanding question. P_K>> yes, we clarified that for certain such checks can be reduced to checks of values of properties and to use repeatable read, but it demands additional , up to introduction of new in essence useless entities for business. S> generally all entities for business are in essence useless. A question in how to solve the task means instead of how to make so that structure of tables it was similar to columns of the form of the typical contract. Under business I meant domain model. It is that model of business which is implemented in a software (I think determination to you so it is known). It turns out that for reduction of used isolation level in certain conditions it is necessary to enter new attributes and-or entities, it is necessary to write the code in a business layer which would operate with them. It the original "indexes" which necessity turns out caused by purely technical needs turn out. Here I about what. S> At first it is necessary to understand, how generally it is all looks from the point of view of a DBMS. In the code described by me and the used approach on a DBMS it lays down somehow so: begin serializable transaction update products set price = 10600 where id = 101;//to update a price select orders where state = ' N ' and productId = 101;//to find orders//an average link counted and saves the orders found earlier update order set cost = 23232 where id = 1111; update order set cost = 65465 where id = 2222;... commit transaction S> at it it is some Methods: S> 1. To use stored procedures. Inside  the same will be executed as a matter of fact SQL, as in a case when logic separately. Only it will faster work, within the limits of one connection etc. But problems  in my opinion remain exactly same. Precisely also, if inside  two times to read the same line at isolation level READ COMMITTED we can have different values and violation of all logic. S> 2. To leave logic in a middl-shooting gallery, but to equip all data reading which will be used within the limits of business transaction, hand-to-hand locks (for example, through sp_getapplock in case of MS SQL). In what it pours out on a DBMS? If I am not mistaken, in something like the such: begin read committed transaction sp_getapplock (' Products', ' Exclusive '); sp_getapplock (' Orders', ' Exclusive '); update products set price = 10600 where id = 101;//to update a price select orders where state = ' N ' and productId = 101; //to find orders//an average link counted and saves the orders found earlier update order set cost = 23232 where id = 1111; update order set cost = 65465 where id = 2222;... sp_releaseapplock (' Orders'); sp_releaseapplock (' Products'); commit transaction S> 3. To leave logic in a middl-shooting gallery, but to rewrite it on the advanced means like linq [2db]. I.e. to receive the functional analog of item 1, but with normal decomposition and the modern language, instead of a procedural language of the sample of 1993. Prompt please as this decision will look from the point of view of a DBMS. Or the link to a similar example give. If there inside there will be something like update order set cost = price * quantity (i.e. logic of calculation) I am afraid that such probably no means always. Not any logic can be expressed through a SQL query.

80

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> That happens in ? One of transactions will be  with an error, the second - will successfully be completed. Unless ? Or it (transaction) simply rises in queue? Or behavior and in wholesale. And a dog. To lock depends on insulation model. Therefore as a dog. The model seemed to me is equivalent to serialization... At me small confusion with model of locks and isolation level - what for it is necessary to be soared over isolation level in case of pessimistic lock?

81

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: S>> At first it is necessary to understand, how generally it is all looks from the point of view of a DBMS. P_K> in the code described by me and the used approach on a DBMS it lays down somehow so: P_K> P_K> begin serializable transaction P_K> update products set price = 10600 where id = 101; - to update price P_K> select orders where state = ' N ' and productId = 101; - to find orders P_K>//an average link counted and saves the orders found earlier Here this moment - it is dangerous. It is necessary to profile an average link that "to count orders" did not lead to a transaction tightening. Even more dangerously that the average link can tighten still any data, and not the fact that in the same transaction. P_K> update order set cost = 23232 where id = 1111; P_K> update order set cost = 65465 where id = 2222; P_K>... P_K> commit transaction P_K> S>> at it it is some methods: S>> 1. To use stored procedures. P_K> Inside  the same will be executed as a matter of fact SQL, as in a case when logic separately. Only it will faster work, within the limits of one connection etc. But problems  in my opinion remain exactly same. Precisely also, if inside  two times to read the same line at isolation level READ COMMITTED we can have different values and violation of all logic. Certainly. It is necessary to do repeatable read. S>> 2. To leave logic in a middl-shooting gallery, but to equip all data reading which will be used within the limits of business transaction, hand-to-hand locks (for example, through sp_getapplock in case of MS SQL). P_K> In what it pours out on a DBMS? If I am not mistaken, in something like the such: P_K> P_K> begin read committed transaction P_K> sp_getapplock (' Products', ' Exclusive '); P_K> sp_getapplock (' Orders', ' Exclusive '); P_K> update products set price = 10600 where id = 101; //to update price P_K> select orders where state = ' N ' and productId = 101;//to find orders P_K>//an average link counted and saves orders P_K found earlier> update order set cost = 23232 where id = 1111; P_K> update order set cost = 65465 where id = 2222; P_K>... P_K> sp_releaseapplock (' Orders'); P_K> sp_releaseapplock (' Products'); P_K> commit transaction P_K> Is not present. - procedure of change of the price begin transaction - read_committed sp_getapplock (' Product_101 ', ' Exclusive '); - captured  _ _ a product. - if open transaction on order adding we rise pending at this time hangs. update products set price = 10600 where id = 101;//to update a price select orders where state = ' N ' and productId = 101;//to find orders - the average link counted and saves the orders found earlier update order set cost = 23232 where id = 1111; update order set cost = 65465 where id = 2222; sp_releaseapplock (' Product_101 '); - Not mandatory - kommit/rollbek transactions itself releases  commit transaction - procedure of adding of the order begin transaction - read_committed sp_getapplock (' Product_101 ', ' Exclusive '); - captured  _ _ a product. - if open transaction on change of the price for a product we rise pending at this time hangs. insert into orders (cost, productId, quantity, state) values (23232, 101, 50, ' N ') sp_releaseapplock (' Product_101 '); - it is not mandatory - kommit/rollbek transactions itself releases  commit transaction S>> 3. To leave logic in a middl-shooting gallery, but to rewrite it on the advanced means like linq [2db]. I.e. to receive the functional analog of item 1, but with normal decomposition and the modern language, instead of a procedural language of the sample of 1993. P_K> prompt please as this decision will look from the point of view of a DBMS. Or the link to a similar example give. P_K> If there inside there will be something like update order set cost = price * quantity (i.e. logic of calculation) I am afraid that such probably no means always. Not any logic can be expressed through a SQL query. Yes, something something like that also will be. I assure you, in business logic of anything it is more difficult than branchings and arithmetics does not meet. Logarithms any to you and Bessel functions there are not present.

82

Re: Locks in a business layer

Hello, Sharov, you wrote: S> Unless ? Or it (transaction) simply rises in queue?  means that in queue there was a cycle. That it to break off, it is necessary to select a victim. Standard strategy - to shoot transaction with a minimum stored price. It provides though any chances of successful end: if so not to do, it can appear that new transactions which by turns become victims permanently get to a funnel, and  does not happen generally. S> Or behavior and in wholesale. And a dog. To lock depends on insulation model. No. What  are superimposed in the course of execution of transactions depends On insulation model. S> therefore as a dog. The model seemed to me is equivalent to serialization... At me small confusion with model of locks and isolation level - what for it is necessary S> to be soared over isolation level in case of pessimistic lock? In order that fair serializable paranoid locks demand overlaying enough. In particular, if any transaction made select with certain predicate P up to the end of transaction we should "freeze" not only change/removal of the records which have got under a predicate, but also an insertion of such records. And it is difficult enough for making precisely, therefore normally the DBMS makes a compromise. We tell, we made select * from people where lastname = ' Ivanov ' and age> 30. In parallel transaction record (' Ivanov ', 29) is interposed. It would Seem, it is possible to continue - it does not get under a predicate so the result of the first transaction will not depend on that,  we the second or not. In DBMS practice are not able to do "lock on a predicate". It would be the expensive. Therefore manage compromises - if there is an index on a surname all operations with a surname Ivanov, irrespective of age will be blocked. If is not present - that generally at reading should be superimposed shared lock on all table. And only if to us carried and there is an index on (lastname, age), we can select so accurately  what to interpose 29-year-old Ivanov it will be possible, and 40-year-old will wait for the termination of the first transaction. Thus, we besides that waste time for capture , we also reduce out of the blue a parallelism level; even independent transactions are forced to wait each other. And here if we do not use the read data "in harm" it is possible and not to retain lock so long - took range lock in the course of reading, and there and then released. Sampling , received thus,  is consistent - for example if our paste operation always interposes namesakes mutually similar reading will always return an even record count.

83

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> S> - procedure of change of price S> begin transaction - read_committed S> sp_getapplock (' Product_101 ', ' Exclusive '); - captured  _ _ a product. S> - if open transaction on order adding we rise pending at this time hangs. S> update products set price = 10600 where id = 101;//to update price S> select orders where state = ' N ' and productId = 101;//to find orders S> - the average link counted and saves orders S found earlier> update order set cost = 23232 where id = 1111; S> update order set cost = 65465 where id = 2222; S> sp_releaseapplock (' Product_101 '); - it is not mandatory - kommit/rollbek transactions itself releases  S> commit transaction S> - procedure of adding of order S> begin transaction - read_committed S> sp_getapplock (' Product_101 ', ' Exclusive '); - captured  _ _ a product. S> - If open transaction on change of the price for a product we rise pending at this time hangs. S> insert into orders (cost, productId, quantity, state) S> values (23232, 101, 50, ' N ') S> sp_releaseapplock (' Product_101 '); - it is not mandatory - kommit/rollbek transactions will be released itself  S> commit transaction S> by Time we read orders and we are going to update them in an amicable way also it is necessary to lock them. That anybody could not change another parallely / to delete the order. Though just  optimistic lock in this case can help. Then updates should in the code above should be supplied with something like ' and version = 234 '. S> I assure you, in business logic of anything it is more difficult than branchings and arithmetics does not meet. Logarithms any to you and Bessel functions there are not present. Speech not about it. At certain complexity (in respect of an amount of branchings) simple implementation of logic in the form of one method becomes impossible. We receive a hell from branchings and any flags... As a result all it  in separate classes (for example, something like strategy), acquires the clear form and . On stored procedures such feint is impossible, I think agree. To reduce all to requests? The interesting idea, well to see real examples of such design to understand applicability boundaries.

84

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Time we read orders and we are going to update them in an amicable way also it is necessary to lock them. That anybody could not change another parallely / to delete the order. In the resulted architecture at any change of the order it is necessary to receive locks on a product - exactly for the same reason. - modification of the order begin tran - read_committed select @old_productId = productId from orders with updlock where id = @order_id - in advance we capture U-blocking to avoid  set @product1 = min (@old_productId, @new_productId) - we prevent , entering strict orderliness in capture  on a product set @product2 = max (@old_productId, @new_productId) sp_getapplock (' product _ ' + product1) sp_getapplock (' product _ ' + product2) update orders set productId = @new_productId, cost = @new_Cost. ., where id = @order_id commit tran - order removal begin tran - read_committed select @productId = productId from orders with updlock where id = @order_id - in advance we capture U-blocking to avoid  sp_getapplock (' product _ ' + productI) delete from orders set productId = @new_productId, cost = @new_Cost... where id = @order_id commit tran P_K> Speech not about it. At certain complexity (in respect of an amount of branchings) simple implementation of logic in the form of one method becomes impossible. We receive a hell from branchings and any flags... As a result all it  in separate classes (for example, something like strategy), acquires the clear form and . On stored procedures such feint is impossible, I think agree. Therefore I also speak to you about Linq. He allows to write the accurate and clear code which will be broadcast in correct SQL without superfluous efforts of you.

85

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> Therefore I also speak to you about Linq. He allows to write the accurate and clear code which will be broadcast in correct SQL without superfluous efforts of you. I understood this thought. Look, after all linq will operate with entities of a layer of data access, instead of entities of business level. We receive moving of a part of logic in DAL. Certainly, if business entities and tables correspond very simply it is possible to write linq on business entities and it is always broadcast in SQL. But unfortunately so happens not always. Therefore while the judgement that the approach interesting is added, but works in rather simple cases.

86

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Look, after all linq will operate with entities of a layer of data access, instead of entities of business level. We receive moving of a part of logic in DAL. P_K> it is finite, if business entities and tables correspond very simply it is possible to write linq on business entities and it is always broadcast in SQL. But unfortunately so happens not always. P_K> Therefore while the judgement that the approach interesting is added, but works in rather simple cases. It would be desirable a convincing example.

87

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> It would be desirable a convincing example. I linq and did not find any advantages, but lacks a coach and the small cart. Yes, in simple cases if unpretentious business logic to push into context classes it is possible quickly  application at all knowing that such a SQL Server. It there inside will work somehow thanking EF, approaches students and developers not knowing SQL. And as soon as complexity of application outgrows certain level instantly  a problem, at first, logic in context-sensitive files certainly you will not add, will be necessary  in between. Learns to write requests on linq is on-essence to learn SQL on-new since requests there are written absolutely in another way syntactically. In simple cases "for students" it rolls since they instead of writing of normal request mold simple loops on cycles, and in what it turns on server side and concepts have no. In advanced queries with , groupings and other restrictions linq start to get out, we tell there there is no operator IN, it is necessary to search often for bypass ways, the compiler refuses to perceive  constructions, we tell at mismatch of data type at  tables  forces to make coercion to the necessary type then joyfully transforms it in CAST in where request killing me an index. To write request certainly it will be possible, only  with normal SQL occupies much less time and it will be frequent where more effectively the Unique lack  that they begin breeds under different requests, at change of structure of basis all of them should be updated that is less convenient since in linq appropriate errors will be highlit by the compiler

88

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> It would be desirable a convincing example. 100 %-s' convincing example I can not result, as it is is specific technology did not use also real experience of its application I have no. But I can describe on what base of representations at me the sounded judgement was added. At first, that business entities not always one-to-one correspond to tables I think you agree. In the elementary cases this consequence deliberate  (we store list Id in the line, not hunting to fence the table;  the dynamic data - we store JSON in line;...) . Operation with such pieces at level linq not to express. Secondly, the behavior often happens dynamic. Let's develop an example with orders. Let all in system at the order can be five methods of its payment. Besides, the order can enter into certain programs of deliveries of two types (and can and not enter). Roughly speaking, it gives us a maximum 35=15 behavior variations on each aspect of the order. , there is a business operation - the change of the order including change of a method of payment and transfer to the program of deliveries. We consider one of aspects - order cost. Cost is defined by (which depends on a method of payment) and a discount (which depends on the program of deliveries). In total request we should receive something like UPDATE order SET... Cost x. Price * quantity * y.discount... FROM Orders INNER JOIN PriceListFromPaymentMethod x ON.... INNER JOIN SupplyPrograms y ON...... It also is one of 15 variations, and only for property Cost. When all on entities all is simple. The method of determination of the price is an abstraction (a certain strategy), a method of determination of a discount - too.  look at essence of the order and calculate value on appropriate algorithm. The first strategy produces "use the price 1000", the second - "use a discount of 10 %". The code fulfilling operation, counted total cost (900), put down it in essence. On other aspects fulfilled the strategy, remaining properties were filled. As a result of property of essence have been changed as it is necessary, we pull DAL, it saved all, beauty. Now as it to turn on linq? I see two variants. Any dynamic requests... Each strategy instead of it will be simple "to take and consider" to add the part of request somewhere. As a result any huge request which  yes, one operation all enumerates will be constructed and updates. But what for this intermediate model? Try to debug it. . It is possible  properties on one - one request updated the order price, another - other property. Whether it will be simple, clear and effective? Too I doubt. What there still problems float in the competitive environment? Theoretically after all we can use in different requests the same data - means it is already necessary repeatable read...

89

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: S>> It would be desirable a convincing example. P_K> 100 %-s' convincing example I can not result, as it is is specific technology did not use also real experience of its application I have no. Not-not-not. You to me not linq an example result, as an example logicians who "badly corresponds to tables". P_K> But I can describe on what base of representations at me the sounded judgement was added. P_K> At first that business entities not always one-to-one correspond to tables I I think you agree. In the elementary cases this consequence deliberate  (we store list Id in the line, not hunting to fence the table;  the dynamic data - we store JSON in line;...) . Whence in business logic undertook JSON? Same only representation - it appears only at interaction with other systems. P_K> Secondly, the behavior often happens dynamic. Let's develop an example with orders. Let all in system at the order can be five methods of its payment. Besides, the order can enter into certain programs of deliveries of two types (and can and not enter). Roughly speaking, it gives us a maximum 35=15 behavior variations on each aspect of the order. , there is a business operation - the change of the order including change of a method of payment and transfer to the program of deliveries. We consider one of aspects - order cost. Cost is defined by (which depends on a method of payment) and a discount (which depends on the program of deliveries). In total request we should receive something like P_K> P_K> UPDATE order SET P_K>... P_K> Cost x. Price * quantity * y.discount, P_K>. . P_K> FROM Orders P_K> INNER JOIN PriceListFromPaymentMethod x ON.... P_K> INNER JOIN SupplyPrograms y ON... P_K>... P_K> P_K> It also is one of 15 variations, and only for property Cost. P_K> When all on entities all is simple. The method of determination of the price is an abstraction (a certain strategy), a method of determination of a discount - too.  look at essence of the order and calculate value on appropriate algorithm. The first strategy produces "use the price 1000", the second - "use a discount of 10 %". The code fulfilling operation, counted total cost (900), put down it in essence. On other aspects fulfilled the strategy, remaining properties were filled. As a result of property of essence have been changed as it is necessary, we pull DAL, it saved all, beauty. Let's write to begin with all it on pure #. Here at us, let us assume, class Order. It has operation CalculateCost (). How it is arranged? We will do 15 successors of class Order c by overloads? Or we will have properties PaymentMethod and SupplyProgram, at which methods GetPrice () and GetDiscount () - virtual? Let's detail. P_K> now as it to turn on linq? I see two variants. P_K> Any dynamic requests... Each strategy instead of it will be simple "to take and consider" to add the part of request somewhere. As a result any huge request which  yes, one operation all enumerates will be constructed and updates. But what for this intermediate model? Try to debug it... Anything difficult. We assume, for example, that PaymentMethod is one of five well-known types, i.e. is covered by listing (and to add a new method without recompilation we we do not plan). We have somewhere at more or less root level a service of obtaining of the price-list: GetPriceList (PaymentMethod paymentMethod). In the Linq-world it returns IQueryable <PriceListItem>. Inside it can be arranged more or less somehow: {switch (paymentMethod) {case PaymentMethod. Cash: return from db. CashPrices select new PriceListItem (itemId, price); case PaymentMethod. Visa: return from db. CCPrices select new PriceListItem (itemId, visaPrice); case PaymentMethod. MasterCard: return from db. CCPrices select new PriceListItem (itemId, masterCardPrice);} } That is at us here both different tables, and different columns in one table. As a result, the order method is arranged somehow approximately so: var totalCost = (from pl in GetPriceList (PaymentMethod) join i in Items on pl.itemId equals i.itemId select i.quantity * pl.price).Sum (); And its calculation instead of tiresome N+1 requests turns in normal join c the aggregate on the DBMS side. To write and debug it is even easier, than the step by step logic in the traditional ERP-appendix with Rich ORM and Lazy Load. Thus productivity will be at least 10 times more. P_K> It is possible  properties on one - one request updated the order price, another - other property. Whether it will be simple, clear and effective? Too I doubt. What there still problems float in the competitive environment? Theoretically after all we can use in different requests the same data - means it is already necessary repeatable read...

90

Re: Locks in a business layer

Hello, Sinclair, you wrote: P_K>> At first that business entities not always one-to-one correspond to tables I I think you agree. In the elementary cases this consequence deliberate  (we store list Id in the line, not hunting to fence the table;  the dynamic data - we store JSON in line;...) . S> Whence in business logic undertook JSON? Same only representation - it appears only at interaction with other systems. But after all linq operates with the objects directly reflecting structure of tables? We assume that at us the price-list for any one case is stored in basis in JSON-line. When I write logic on entities to me  - DAL gets this JSON and translates it in objects with which I already and will operate. And here when I write logic on linq? How I can get from this price-list some the positions necessary to me request? Here I about what. P_K>> Secondly, the behavior often happens dynamic. Let's develop an example with orders. Let all in system at the order can be five methods of its payment. Besides, the order can enter in certain S> Let's write to begin with all it on pure #. S> Here at us, let us assume, class Order. It has operation CalculateCost (). How it is arranged? S> We will do 15 successors of class Order c by overloads? No, all not so. The order does not consider the price S> Or we will have properties PaymentMethod and SupplyProgram, at which methods GetPrice () and GetDiscount () - virtual? Again by. I wrote, strategy will be used. Somehow so: public interface IPricingStrategy {//there Will be an implementation which looks to order and on its properties defines whence to take the price, plus simple implementation for tests decimal GetPrice (Order order);} public interfact IDiscountingStrategy {//there Will be an implementation which looks to order and on its properties defines whence to take a discount, plus simple implementation for tests decimal GetDiscountPercents (Order oreder);} //Recalculation of cost of the order after changes private void RecalcOrderCost (Order order) {var price = _pricingStrategy. GetPrice (order); var discount = _discountingStrategy. GetDiscountPercents (order) / 100M; order. Cost = order. Quantity * price * (1M - discount);} Not test implementations of strategy can be different - somewhere in a type swith or a dial-up if, somewhere refined more-dynamic, but not an essence. S> let's detail. S> S> var totalCost = (from pl in GetPriceList (PaymentMethod) join i in Items on pl.itemId equals i.itemId select i.quantity * pl.price).Sum (); S> S> And its calculation instead of tiresome N+1 requests turns in normal join c the aggregate on the DBMS side. To write and debug it is even easier, than the step by step logic in the traditional ERP-appendix with Rich ORM and Lazy Load. But besides, it will be separate request which returns at once the total. And cost it will be necessary then  in the order - other request. In the same way will be and in my case - strategy implementation runs down in basis, counts cost and gives at once in a type decimal. Instead of in the form of IQueriable <something> that is necessary still then correctly . Besides, strategy can throw an exception, for example, "for the goods there is no price in the price-list" that is a predicted business situation and is processed. And here is how it to clarify for IQueriable? S> Thus productivity will be at least 10 times more. Will be above - most likely. On the order or not - it is not ready to argue. We return to an initial problem. We want to provide a situation that for the period of order update the used price-list could not be changed. That in your case that in we wash behavior with .. Bases equally - there will be a sequence of requests: 1) to find the order 2) to find the prices 3)  the order Neither that nor other approach do not provide that between 2 and 3 2 essences used in a step do not exchange. Therefore there is no sense to continue to argue within the limits of this subject on that how better to select the data - through linq or not.

91

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> Neither that nor other approach do not provide that between 2 and 3 2 essences used in a step do not exchange. Therefore there is no sense to continue to argue within the limits of this subject on that how better to select the data - through linq or not. If to store not current price, and history of change of the prices, and at formation Order to select the last installed price at the moment of formation becomes at any  (linq or not linq) it is difficult . And the lock subject in a business layer disappears.

92

Re: Locks in a business layer

Hello, Poul_Ko, you wrote: P_K> But after all linq operates with the objects directly reflecting structure of tables? Yes. P_K> we Assume that at us the price-list for any one case is stored in basis in JSON-line. When I write logic on entities to me  - DAL gets this JSON and translates it in objects with which I already and will operate. And here when I write logic on linq? How I can get from this price-list some the positions necessary to me request? Here I about what. At first, at least - is not worse, than in "to the logician on entities". Secondly, if the DBMS is able to work with JSON you have a chance to file  so that parsing was fulfilled on the DBMS side. Thirdly, if the DBMS is not able to work with JSON, but all of you equally store the data in it, and thus you should work with separate positions it is time to you to dismiss the architect while it did not tire out you in bankruptcy. P_K> again by. I wrote, strategy will be used. Somehow so: P_K> [c#] P_K> public interface IPricingStrategy {//there Will be an implementation which looks to order and on its properties defines whence to take the price, plus simple implementation for tests P_K> decimal GetPrice (Order order); P_K>} About P_K> public interfact IDiscountingStrategy {//there Will be an implementation which looks to order and on its properties defines whence to take a discount, plus simple implementation for tests P_K> decimal GetDiscountPercents (Order oreder); P_K>} Apprx. these strategy Whence undertake? Result implementation any of these strategy. Meanwhile still not clearly that there will be for the code and why he is difficult for rewriting on linq. P_K> But besides, it will be separate request which returns at once the total. And cost it will be necessary then  in the order - other request. P_K> in the same way will be and in my case - strategy implementation runs down in basis, counts cost and gives at once in a type decimal. It is good, if so. While I will not see the implementation code, I cannot understand that you mean under "runs down in basis". P_K> Instead of in the form of IQueriable <something> that is necessary still then correctly . Besides, strategy can throw an exception, for example, "for the goods there is no price in the price-list" that is a predicted business situation and is processed. And here is how it to clarify for IQueriable? As a whole - in the same way. In an ideal, "strategy" will be simply compiled in SQL which will be fulfilled directly in basis. And instead of return decimal which on the client is necessary there and then to give only it reversely to basis, returns expression which can be used in update order set TotalCost = <>. S>> Thus productivity will be at least 10 times more. P_K> will be above - most likely. On the order or not - it is not ready to argue. Here all is very simple: as a rule, such "small" requests about server side do not stand almost anything. The main cost is roundtrip. When in the order approximately 10 positions, just also it is received about tenfold improving of productivity. P_K> we Return to an initial problem. We want to provide a situation that for the period of order update the used price-list could not be changed. P_K> that in your case that in we wash behavior with .. Bases equally - there will be a sequence of requests: P_K> 1) to find order P_K> 2) to find prices P_K> 3)  order P_K> Neither that nor other approach do not provide that between 2 and 3 2 essences used in a step do not exchange. Therefore there is no sense to continue to argue within the limits of this subject on that how better to select the data - through linq or not. I will try to explain to you once again that the key to success is 1. To use possibilities of a DBMS for insulation of transactions. That is to track that all three points were fulfilled within the limits of one transaction with suitable isolation level. 2. To minimize an amount  between a DBMS and a middl-shooting gallery because they increase runtime of transaction and, accordingly, increase chances to run by waiting or . 3. The traditional answer to these two requirements is a spelling . Unfortunately, at  there are some fundamental problems: 3.1. The bad possibilities on decomposition. Introduction table-valued functions helps a little, but even with them modern SQL on generations lags behind high-grade programming languages. 3.2. Problems on synchronization of versions of the client and the server. It is possible to run easily into a situation when the code version in a middl-shooting gallery does not coincide with the code version in , and there are hardly perceptible glitches. 3.3. Problems with refactoring and debugging. Unlike C#, statically  a SQL text correctness very heavily. 4. The potential decision for these problems is linq - as a method of result SQL of the code from C#-. In such variant your code is always correct, friendly to the DBMS optimizer, and thus the live person can still read and support it.

93

Re: Locks in a business layer

Hello, Sinclair, you wrote: S> Hello, Poul_Ko, you wrote: P_K>> But after all linq operates with the objects directly reflecting structure of tables? S> yes. P_K>> we Assume that at us the price-list for any one case is stored in basis in JSON-line. When I write logic on entities to me  - DAL gets this JSON and translates it in objects with which I already and will operate. And here when I write logic on linq? How I can get from this price-list some the positions necessary to me request? Here I about what. S> Secondly if the DBMS is able to work with JSON you have a chance to file  so that parsing was fulfilled on the DBMS side. The humour estimated S> Thirdly if the DBMS is not able to work with JSON, but all of you equally store the data in it, and thus you should work with separate positions it is time to you to dismiss the architect while it did not tire out you in bankruptcy. Here it agree. But in life not all so is simple. There are cases when at first to work with separate positions it is not necessary, and then suddenly it became necessary in any one rare occurence. Also there are two ways - either to normalize , or to work as with objects after deserialising. In case of architecture "on entities" this choice is, in a case linq - alas. S> Apprx. these strategy Whence undertake? Result implementation any of these strategy. Meanwhile still not clearly that there will be for the code and why he is difficult for rewriting on linq. Implementation will be any such: public class PricingStrategy: IPricingStrategy {public decimal GetPrice (Order order) {if (order. PaymentMethod == PaymentMethods. Method1) {var priceListItem = _method1PriceListDal. FindForProduct (order. ProductId);//Pours out in SELECT... FROM PriceList WHERE ProductId = @p1 if (priceListItem == null) throw new Method1ProductPriceNotFound (order. ProductId); //Or any logic, for example, to take from "" a price return priceListItem. Price;}...} For simplicity here in the order there is no dial-up of the items, always one product. But to expand to a collection it is not difficult - there will be all the same one request (with IN) and will be returned, for example, IDictionary <int, decimal>. P_K>> Instead of in the form of IQueriable <something> that is necessary still then correctly . Besides, strategy can throw an exception, for example, "for the goods there is no price in the price-list" that is a predicted business situation and is processed. And here is how it to clarify for IQueriable? S> As a whole - in the same way. In an ideal, "strategy" will be simply compiled in SQL which will be fulfilled directly in basis. And instead of return decimal which on the client is necessary there and then to give only it reversely to basis, returns expression which can be used in update order set TotalCost = <>. Here you for some reason lowered this very important specification in the previous post, it in a root changes sense. But at first about other. Than yet usage IQueriable in business the code is not pleasant:" Not very skilled "developers can make easily with it something that the generator of requests does not digest (.Select (x => new SomeClass x. Prop1 x. Prop2)). That is, in itself IQueriable/IEnumerable does not speak that it is connected to basis, and it needs to be held always in a head. It is more question of discipline etc., but the it is less than chances to make a mistake, the better. When operation with basis lies strictly in a separate layer it is possible not skilled not to start up there. And when it is exposed outside here so implicitly, it becomes already dangerous. S> I will try to explain to you once again that the key to success is S> 1. To use possibilities of a DBMS for insulation of transactions. That is to track that all three points were fulfilled within the limits of one transaction with suitable isolation level. It already is now in system, on the used approach does not depend. S> 2. To minimize an amount  between a DBMS and a middl-shooting gallery because they increase runtime of transaction and, accordingly, increase chances to run by waiting or . Here it completely agree. The described approach on linq really allows to transfer any operations to a DB, making one request instead of several. S> 3. The traditional answer to these two requirements is a spelling . Unfortunately, at  there are some fundamental problems:  do not solve the first problem, we already clarified it earlier. In the remaining it agree - become outdated  technology, approaches for absolutely simple tasks. S> 4. The potential decision for these problems is linq - as a method of result SQL of the code from C#-. In such variant your code is always correct, friendly to the DBMS optimizer, and thus the live person can still read and support it. Thus the code becomes artful, for the operating account any postponed requests, and DBMS restrictions filter into it. For any tasks it is insignificant, for any - will be a problem. If it is interesting to you - we can continue further arguing "how to make  on linq2db", I still see some problems. If to operate with book terms it seems to me that linq2db is ideally suited for a pattern transaction script, but here with difficult domain model it not . For now just in case I will designate: I consider that besides the candidate solutions sounded by you ( and saving of requests) has the right to life a variant "manual  of locks" which simply eliminates possibility of waitings and  at DB level. Yes, waiting will be, is simple at other level, and yes not always the system will work as much as possible effectively. But the decision looks the most simple and clear, and it in my judgement is very important in enterprise-working out.