Insert/Delete performance

Question

DB Table: 
id       int(6)
message  char(5)

I have to add a record (message) to the DB table. In case of duplicate message(this message already exists with different id) I want to delete (or inactivate somehow) the both of the messages and get their ID's in reply.

Is it possible to perform with only one query? Any performance tips ?...

P.S. I use PostgreSQL.

The main my problem I worried about, is a need to use locks when performing this with two or more queries...

Many thanks!

Answer 1

You could write a procedure with both of those commands in it, but it may make more sense to use an insert trigger to check for duplicates (or a nightly job, if it's not time-sensitive).

Answer 2

It is a little difficult to understand your exact requirement. Let me rephrase it two ways:

1. You want both the entries with same messages in the table (with different IDs), and want to know the IDs for some further processing (marking them as inactive, etc.). For this, You could write a procedure with the separate queries. I don't think you can achieve this with one query.

2. You do not want either of the entries in the table (i got this from 'i want to delete'). For this, you only have to check if the message already exists and then delete the row if it does, else insert it. I don't think this too can be achieved with one query.

If performance is a constraint during insert, you could insert without any checks and then periodically, sanitize the database.

Answer 3

If you really want to worry about locking do this.

1. UPDATE table SET status='INACTIVE' WHERE id = 'key';

   If this succeeds, there was a duplicate.

   • INSERT the additional inactive record. Do whatever else you want with your duplicates.

   If this fails, there was no duplicate.

   • INSERT the new active record.

2. Commit.

This seizes an exclusive lock right away. The alternatives aren't quite as nice.

• Start with an INSERT and check for duplicates doesn't seize a lock until you start updating. It's not clear if this is a problem or not.

• Start with a SELECT would need to add a LOCK TABLE to assure that the select held the row found so it could be updated. If no row is found, the insert will work fine.

If you have multiple concurrent writers and two writers could attempt access at the same time, you may not be able to tolerate row-level locking.

Consider this.

1. Process A does a LOCK ROW and a SELECT but finds no row.

2. Process B does a LOCK ROW and a SELECT but finds no row.

3. Process A does an INSERT and a COMMIT.

4. Process B does an INSERT and a COMMIT. You now have duplicate active records.

Multiple concurrent insert/update transactions will only work with table-level locking. Yes, it's a potential slow-down. Three rules: (1) Keep your transactions as short as possible, (2) release the locks as quickly as possible, (3) handle deadlocks by retrying.