What might fail using the following transaction if performed by concurrent customers within the default isolation degree of READ COMMITTED?
BEGIN TRANSACTION SELECT * FROM t WHERE pid = 10 and r between 40 and 60 -- ... this returns tid = 1, 3, 5 -- ... process returned data ... DELETE FROM t WHERE tid in (1, 3, 5) INSERT INTO t (tid, pid, r) VALUES (77, 10, 35) INSERT INTO t (tid, pid, r) VALUES (78, 10, 37) INSERT INTO t (tid, pid, r) VALUES (79, 10, 39) COMMIT
You can getting serious performance problems from dead locks
The Choose will get yourself a shared lock on the page and so the Remove would make an effort to upgrade individuals locks to exclusive locks.
If another user was performing exactly the same query, it could obtain shared locks on a single page simultaneously another user does. When one attempts to upgrade for an exclusive lock, it'll watch for other shared locks to become launched. Another may also be awaiting all shared locks to become launched. Both may have a shared lock and awaiting another to produce that shared lock so itself can acquire a unique lock. Other queries will stack up attempting to perform the same, and shortly the deadlocks will start to be detected and also the queries will start to get cancelled and folded back. With respect to the frequency from the queries the dead lock recognition from the DB engine might not be killing off queries as quickly as brand new ones are arriving, meaning no queries will succeed.
You should add something similar to an indication within the choose to request that the exclusive lock be acquired in the get-go. Or you might slowly move the choose outdoors from the transaction and employ concurrency conflict recognition inside your other statement's where criteria.
Thew whole factor is strange in my experience. What's the reason for the choose? It achieves nothing. Write a remove to choose the records you would like. An amount strike us a problem of concurrent customers is they is going to be attempting to place exactly the same records because you hard-coded the values and therefore would most likely encounter the initial constraints that you simply most likely dress in tid or even the tid, pid combo.
Honestly what exactly are you attempting to accomplish here? This appears like an advertisement hoc query which was intended for one-time usage that you're attempting to run multiple occasions. It will always be an awful idea to hard-code such as this.
You should mention if you are using oracle or postgres. Also, you need to clearly specify your securing and never depend on default behavior. They might change along with other databases or database versions.
You do not make use of a lock for that Choose, so everyone can get exactly the same results, everyone might find records tid 1, 3 and 5. Everyone will process this info and everyone will attempt to remove this info. And that is not likely to work, the remove operation will set a lock. Just one transaction can lock this info, other transactions need to watch for commit from the first transaction. This transaction will place the brand new records and commit, others will remove nothing (aren't able to find records, not a problem) and place the brand new records too. This info have a similar data, is the fact that an issue?
Maybe you'll need a
SELECT ... FROM ... FOR UPDATE; to lock the records you need to process.