| Subject | Re: [firebird-support] Read uncommitted |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-08-17T02:57:05Z |
At 01:57 PM 17/08/2010, you wrote:
1. A new record version is created in the database, where the StockQuantity is now 30;
2. Any *other* transactions are now prevented from writing their own new versions of the original row.
Conflicting transactions can be set to WAIT until the first transaction has committed (thus making the new version become the latest committed version) or to NO WAIT, causing the lock conflict to be reported immediately to any other transactions that request an update to the same record. Either way, your application has to handle (intercept) the potential change, sooner or later. This will be very annoying for your user if s/he has spent time entering data.
The "dummy update" that Alan suggested is a way for the user application to know *early* (before the user has done any work) about other transactions that have updates pending on the record they want to change. The dummy update of the targeted record will be attempted: if no other transaction already has a real or dummy update pending for that record, the requestor gains a "pessimistic lock" on it....if another transaction *does* have a real or dummy update pending on the record, then the requestor will not be able to secure this "pessimistic lock". You use your skills and the features of your application programming language to manage this in ways that are transparent to the user.
One transaction can never "see" the changes that another has waiting (what you refer to as "read uncommitted"). This is commonly known as "dirty read" and it is not supported (nor supportable) by ACID-compliant database engines.
./heLen
>I think you are supposing there is one register for each piece of product A.I'm sure he does not suppose that!
>If there is a stock of 100 pieces of product A, there are NO 100 registers in a table to lock or unlock. There is only one register with a field STOCK with the value 100.Exactly so....
>So I can´t do what you suggest.Yes, you can, and it is what you should do. It seems you do not understand the purpose of transactions, particularly their role in ensuring database consistency. Perhaps you also do not understand that *updating* a row in a table causes the entire ROW to be replaced by a new VERSION of that row. So, for example, as soon as the request to reduce the StockQuantity field of row X by 70 units is passed to the DB engine, two things (at least two!) happen:
1. A new record version is created in the database, where the StockQuantity is now 30;
2. Any *other* transactions are now prevented from writing their own new versions of the original row.
Conflicting transactions can be set to WAIT until the first transaction has committed (thus making the new version become the latest committed version) or to NO WAIT, causing the lock conflict to be reported immediately to any other transactions that request an update to the same record. Either way, your application has to handle (intercept) the potential change, sooner or later. This will be very annoying for your user if s/he has spent time entering data.
The "dummy update" that Alan suggested is a way for the user application to know *early* (before the user has done any work) about other transactions that have updates pending on the record they want to change. The dummy update of the targeted record will be attempted: if no other transaction already has a real or dummy update pending for that record, the requestor gains a "pessimistic lock" on it....if another transaction *does* have a real or dummy update pending on the record, then the requestor will not be able to secure this "pessimistic lock". You use your skills and the features of your application programming language to manage this in ways that are transparent to the user.
One transaction can never "see" the changes that another has waiting (what you refer to as "read uncommitted"). This is commonly known as "dirty read" and it is not supported (nor supportable) by ACID-compliant database engines.
>Even there are products which unity of measurement is not "pieces". For example, there may be 127,69 meters of stock of some product and perhaps in an order the seller 1 wants to use 74,52 meters.Irrelevant, totally. Whether you sell a commodity by the metre, or as a set, by a pack of 20, or as an individual piece, your stock of it is still recorded in units.
./heLen