We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've read that transactions on multi-user JET databases are supposed to be set to optimistic locking, which concerns me a little, and I don't know how to lock specific records using JET's SQL syntax.
I have however verified that when I do an update to a record within a transaction on one connection in SQuirrel, if you try to access that record in the other connection the changes are not seen by the other connection until after commit. Also updates in the other connection to those records will fail.
On this database I've been tasked with merging a new duplicate record into the old record. The new record has alot of related child tables, the foreign keys of which, I need to switch to the original record to merge the two.
As far as I can tell they are all 1-M relationships.
So far what I have done is, begin a transaction and then follow this procedure for each of the related child tables:
I get a
count(*) of the number of child records from the new duplicate record and the number of child records from the original record, resulting in a table like this:
| ID |the_count | | primary key old record |numChildRecsOld | | primary key of new record |numChildRecsNew |
And then what I do is sum and store the two counts: numChildRecsOld + numChildRecsNew = numChildRecsOldThatShouldExistJustBeforeCommit
Next I would UPDATE the foreign key in the child records that point to the new record id, so that they point to the old original record ID.
And I would be sure to store the value of numChildRecsOldThatShouldExistJustBeforeCommit for comparison later in the transaction when I re-run the count query to ensure that no new child records are associated with the old or new parent record have been added while our thread was busy updating the other tables.
So after all of the updates occur within our transaction, we have to go back and check that all the sums are as expected before commiting:
| ID |the_count | | primary key old record |numChildRecsOld should = numChildRecsOldThatShouldExistJustBeforeCommit | | - AND - | primary key of new record |numChildRecsNew should = 0 |
Or...we have to rollback the entire transaction and start over completely...if the number of records for the old query (numChildRecsOld) does not match numChildRecsOldThatShouldExistJustBeforeCommit then we've lost or gained a record somewhere.
And if the numChildResNew is greater than 0, then someone has inserted another child record related the duplicate and we've failed to merge it with the original record.
In either case the transaction needs to be rolled back, and we need to restart the entire query.
Is this logical thinking? I'm I overly concerned about this? Will the transaction keep other people from adding records? Or should I just send out an email to everyone in the department to get off the database until I run my transaction? (poor man's exclusive lock as it were)