1

I have this 'table A'. It contains two columns; email and email_alias. A sample is below: ===================================== = email | email_alias = ===================================== = email1@abc.com | alias1@abc.com = = email1@abc.com | alias2@abc.com = = email2@abc.com | alias3@abc.com = = email3@abc.com | alias4@abc.com = = email3@abc.com | alias5@abc.com = = email3@abc.com | alias6@abc.com = = email3@abc.com | alias7@abc.com = =====================================

As you can see only the alias need be unique. Anyways, i have a script that will allow a user to update this table. Previously, i would truncate the entire table, and then reinsert the latest entries. Problem: It's possible for a user to see an empty or incomplete list.

My next thought, was to flag all of the old fields "to be deleted", insert the new, and then go back and delete the flagged entries. Problem: It's possible for the user to see duplicate entries.

I could say if email = new.email & email_alias = new.email_alias, then do nothing, else, add record. Problem: Old records will still be present

I would rather not do this, but is my only option to create a temporary table? Remove the records from the old table that don't exist in the temp table, and add records from the temp table that don't exist in the old table?

  • 9
    Isn't this exactly what transactions were designed for? – Philip Kendall May 27 '16 at 17:34
  • actually, you're 100% correct. If I wrap this in a transaction, then other user's won't be able to query the table until the lock is released. Thank you! so just to be clear, i should start transaction, truncate the table, re add the records, and then close the transaction? – Antonio Anonymous May 27 '16 at 18:26
  • What about caches? MySql have no materialized views, but you can implement your own cache that will last (for the concerned user) as long as it takes to update the table. – Laiv May 27 '16 at 21:27
  • lock the table (or use a transaction) and the do the update quickly, if the brief lock is causing pain consider switching to something with a better transaction model. – Jasen May 28 '16 at 7:56
  • If the alias is unique, how can the user see duplicate entries? – JeffO May 28 '16 at 18:00
1

Problem: It's possible for a user to see an empty or incomplete list.

Problem: It's possible for the user to see duplicate entries.

I suspect that you are not using Transactions when making these updates.

With Transactions, users will only see the end result of your [transactional] manipulations. They will never see the intermediate state where duplicates are present (or no rows at all).

Begin transaction 
Insert/ Update/ Delete, as required 
Commit 

Transactional changes should be quick because many DBMSs will block access to the rows or tables being worked on until the Commit (or Rollback) is executed.

0

I've approached this by applying a delta. To do this in the database you could add a flag column that you change each run. The updates can be applied in two passes.

  • Apply the new data flipping the flag on existing records that match, and adding and new records with the new flag value.
  • Delete all records that have the old flag valued.

To limit the number of locked. records you can commit changes every 100 or 100 records.

I try to minimize the change set. If the set is small enough, you can read the primary keys into a list (map) . Then, remove keys from the list that match records from the feed. When you have processed the feed, use the list to remove records deleted from the feed. Using this method, only new (and modified) records need to be modified.

0

I can think of two ways to approach this problem.

If the list is small enough

...just wrap your update in a transaction and set your transaction isolation level so that users querying the table will be blocked until the update is complete.

If the list is very large

...or has complicated relationships (probably does not apply in this specific case), add a layer of indirection, e.g.

  • Modify your user table with a "EmailListID" which is a FK to your email table
  • Modify your email table by adding a column "EmailListID". The primary key of your new table will be
  • When retrieving the list of emails, add a WHERE EmailListID = [User].EmailListID clause
  • When updating the list, follow these steps:
    1. Pick a new EmailListID
    2. Add the new version of the email list to the email table
    3. Update your user table with the new EmailListID
    4. Delete the old list from the table

This allows the old version of the list to be accessible while you are doing your expensive update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.