MySQL table(s) locking
Posted: Mon Aug 16, 2010 9:49 am
I have an application that is designed to build repair documents for the aviation industry. These documents serve as templates for work order's (which is the system I am building now). When you create a work order, you use a repair document as a template and once cloned, you tweak the W/O as required to repair the given part.
Obviously, data validity is critically important, so I am wondering what the best approach would be to prevent the W/O system from readin/cloning the repair document table(s) while they are being updated by their own management system?
Basically, our QA department uses another application to build and maintain these repair document templates. Then guys on the floor use a W/O management system to query these templates, which are cloned and then modified locally within the W/O system.
My fear is that, an engineer will approve changes to the repair document in the middle of a clone (unlikely but possible) by the W/O system. These documents are structly controlled, so if the W/O begins cloning a repair document and half way through the repair document is updated, it's revision number is bumped up and it's data/entities potentially changed. The resulting W/O cannot have half the old revision and half the new revision, for obvious reasons.
I need to essentially lock about 6 tables from reading, writing, etc. Until the repair document system has finished it's transaction.
Table locking and transactions do not seem to prevent reading, so I am cuious as to whether this can be changed or whether a custom solution is in order, such as keeping value in SHMOP and polling the value before executing a clone.
What is your experience and/or solutiuon in this situation?
Cheers,
Alex
Obviously, data validity is critically important, so I am wondering what the best approach would be to prevent the W/O system from readin/cloning the repair document table(s) while they are being updated by their own management system?
Basically, our QA department uses another application to build and maintain these repair document templates. Then guys on the floor use a W/O management system to query these templates, which are cloned and then modified locally within the W/O system.
My fear is that, an engineer will approve changes to the repair document in the middle of a clone (unlikely but possible) by the W/O system. These documents are structly controlled, so if the W/O begins cloning a repair document and half way through the repair document is updated, it's revision number is bumped up and it's data/entities potentially changed. The resulting W/O cannot have half the old revision and half the new revision, for obvious reasons.
I need to essentially lock about 6 tables from reading, writing, etc. Until the repair document system has finished it's transaction.
Table locking and transactions do not seem to prevent reading, so I am cuious as to whether this can be changed or whether a custom solution is in order, such as keeping value in SHMOP and polling the value before executing a clone.
What is your experience and/or solutiuon in this situation?
Cheers,
Alex