Timeline for Best way to prevent deadlocks and server locking for single row Insert/Update transaction?
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 24, 2018 at 12:40 | vote | accept | espresso_coffee | ||
| Aug 24, 2018 at 12:13 | answer | added | Dan Guzman | timeline score: 2 | |
| Aug 23, 2018 at 12:21 | comment | added | Dan Guzman | Let us continue this discussion in chat. | |
| Aug 23, 2018 at 12:20 | comment | added | espresso_coffee | @DanGuzman I'm not sure that I completely understand your last feedback. This part last one wins indicates to SELECT followed by UPDATE? I'm confused what you try to explain in that sentence? | |
| Aug 23, 2018 at 12:13 | comment | added | Dan Guzman | The single statement approach may be appropriate when PK violations are unlikely and you are ok with handling that on the client side. Just be aware that unless you code for optimistic concurrency (e.g. rowversion), a subsequent UPDATE will overwrite row values when they have been changed by another session (i.e. last one wins when you perform a SELECT followed by UPDATE). | |
| Aug 23, 2018 at 11:03 | comment | added | espresso_coffee | @DanGuzman your first answer indicates that I won't need Begin Tran and I'm assuming that is the case if table has PK correct? In that case won't be possible to insert duplicates. Your second answer, yes I have tried to do this in single transaction with MERGE but after researching around seems that is not a good option. Using single statement is much faster and cause less problems. If you have any suggestions please let me know. | |
| Aug 23, 2018 at 10:07 | comment | added | Dan Guzman | Is your actual use-case a conditional INSERT/UPDATE proc, where you want to insert the row if it doesn't exist and update the existing row if it does? In that case, I would use a single proc with either a MERGE or conditional INSERT/UPDATE in a transaction, and use locking hints to serialize access to the single row. | |
| Aug 23, 2018 at 10:00 | comment | added | Dan Guzman | With a single UPDATE statement, you don't need the BEGIN TRAN at all since the statement will autocommit by default and any errors will be returned to the client. The TRY/CATCH is needed only if you need to handle specific errors (besides client timeouts) in the proc and take appropriate action. I would not expect deadlocks (1205) with single-row insert/updates, the 1222 error will occur only if you set a not-default LOCK_TIMEOUT on the server side, and 1204 should not happen on a healthy server. | |
| Aug 23, 2018 at 1:59 | comment | added | espresso_coffee | In that case all this code in try/catch is not necessary? Basically only thing I need is ROLLBACK TRANSACTION. | |
| Aug 23, 2018 at 1:43 | comment | added | Dan Guzman | You can't catch command timeouts in T-SQL. Command timeouts occur on the client side where the client API cancels the query when it's impatient, waiting up to the specified CommandTimeout (30 seconds by default with most APIs). No subsequent statements, including the CATCH block, will execute. | |
| Aug 22, 2018 at 18:42 | history | asked | espresso_coffee | CC BY-SA 4.0 |