DB Table: id int(6) message char(5)
I must give a record (message) towards the DB table. Just in case of duplicate message(this message already is available with various id) I wish to remove (or inactivate in some way) the two from the messages and obtain their ID's in reply.
Can you really perform with just one query? Any performance tips ?...
P.S. I personally use PostgreSQL.
The primary my problem I concerned about, is really a want to use locks when carrying out this with several queries...
If you want to bother with securing do that.
UPDATE table SET status='INACTIVE' WHERE id = 'key'
If the works, there is a replica.
- Place the extra inactive record. Do other things you would like together with your replicates.
If the fails, there is no duplicate.
- Place the brand new active record.
This grabs a unique lock immediately. The options aren't as nice.
Begin with an Place and appearance for replicates does not seize a lock before you start upgrading. It isn't obvious if this sounds like an issue or otherwise.
Begin with a Choose will have to give a LOCK TABLE to make sure the choose held the row found so it may be up-to-date. If no row is located, the place works fine.
For those who have multiple concurrent authors and 2 authors could attempt access simultaneously, you might not have the ability to tolerate row-level securing.
Think about this.
Process A does a LOCK ROW along with a Choose but finds no row.
Process B does a LOCK ROW along with a Choose but finds no row.
Process A does an Place along with a COMMIT.
Process B does an Place along with a COMMIT. You have duplicate active records.
Multiple concurrent place/update transactions is only going to use table-level securing. Yes, it is a potential slow-lower. Three rules: (1) Keep the transactions as short as you possibly can, (2) release the locks as rapidly as you possibly can, (3) handle deadlocks by retrying.
You can write a process with each of individuals instructions inside it, however it may be preferable to make use of an place trigger to check on for replicates (or perhaps a nightly job, when not time-sensitive).
It's a little hard to understand your exact requirement. Allow me to rephrase it two ways:
You would like both records with same messages within the table (with various IDs), and wish to be aware of IDs for many further processing (marking them as inactive, etc.). With this, You can write a process using the separate queries. I do not think you are able to accomplish this with one query.
You don't want either from the records within the table (i acquired this from 'i wish to delete'). With this, you just see if the content already is available after which remove the row whether it does, else place it. I do not think that as well could be accomplished with one query.
If performance is really a constraint throughout place, you can place with no inspections after which periodically, sanitize the database.