A long running UPDATE which can temporarily lock all of the data that is being updated. Basically a lock is when the relevant data is frozen while the transaction executes. This can happen at the field or row or table level.
The solution is to wait for completion, but your query could take 7 million years to complete so… you might not have the patience. You could also just exhaust the compute/memory resources of the machine.
This feels bad when you expected it to be a simple transaction or when you only expected the update to apply to a small subset of data… it’s possible that you’re using a suboptimal query strategy (e.g. many JOINs, lack of indices, not using WITH) or that you’re running your UPDATE on a huge number of records instead of the three you expected to change.
And/or
A deadlock, meaning the same data is being operated on at the same time, but the operations can’t execute because there is a competing/circular lock.
The use of BEGIN means that the transaction has started. You usually use COMMIT to actually finish and complete the transaction. If you’ve got another query operating on the same data happening during this time, even if it’s data that is incidental and only used to make the JOIN work, there can be “overlap” which makes the transaction hang, because the DB engine can’t work out which lock to release first.
Someone explain the joke to me please!
I’m going to try. Could be:
The solution is to wait for completion, but your query could take 7 million years to complete so… you might not have the patience. You could also just exhaust the compute/memory resources of the machine.
This feels bad when you expected it to be a simple transaction or when you only expected the update to apply to a small subset of data… it’s possible that you’re using a suboptimal query strategy (e.g. many JOINs, lack of indices, not using WITH) or that you’re running your UPDATE on a huge number of records instead of the three you expected to change.
And/or
The use of BEGIN means that the transaction has started. You usually use COMMIT to actually finish and complete the transaction. If you’ve got another query operating on the same data happening during this time, even if it’s data that is incidental and only used to make the JOIN work, there can be “overlap” which makes the transaction hang, because the DB engine can’t work out which lock to release first.
Also see ACID compliance for further reading (https://en.wikipedia.org/wiki/ACID)