Timeline for Best way to prevent deadlocks and server locking for single row Insert/Update transaction?
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 27, 2018 at 12:35 | comment | added | espresso_coffee | Dan I have tested your code and there is one piece that is confusing. I have tried to test concurrency and that worked as you explained. Then I have tried to update @Code column value that is PK in the table. I was getting error message Building with code 43 was modified or deleted by another user. Seems that I will need to pass current value of my PK as well as new update value if I want to update Primary Key? Is this correct or there is another way to update PK? Also can I update record just on RowVersion? | |
| Aug 27, 2018 at 9:51 | comment | added | Dan Guzman | The error number of the RAISERROR in my example is 50000. This is the default for user-defined messages. | |
| Aug 24, 2018 at 20:49 | comment | added | espresso_coffee | Dan I was trying to find the error code for concurrency in SQL. Do you know what is the code? Instead of having SQL catching that I would use Server side language and if SQL returns code for concurrency I can display custom message to the user. | |
| Aug 24, 2018 at 16:36 | comment | added | Dan Guzman | @espresso_coffee, the rowversion column is needed for the optimistic concurrency check, The IDENTITY column value will remain unchanged after the initial insert whereas the rowversion will change every time the row is updated. | |
| Aug 24, 2018 at 15:10 | comment | added | espresso_coffee | If I already have auto-incremented column do I still need row-version or not? | |
| Aug 24, 2018 at 15:09 | comment | added | Dan Guzman | The first statement in the script adds a new column named RowVersion that is a rowversion data type. The undeylying value is maintained by SQL Server and automatically changes whenever the row is updated. | |
| Aug 24, 2018 at 13:22 | comment | added | espresso_coffee | I'm getting an SQL error in Management Studio that RowVersion is invalid column name. Have I missed something or? I see you added RowVersion column. What kind of column is that? Auto increment or? | |
| Aug 24, 2018 at 13:12 | comment | added | Dan Guzman | @espresso_coffee, no RAISERROR needed in the insert. The only "expected" error would be the PK violation due to concurrency and that could be handled on the app side with a user-friendly message. | |
| Aug 24, 2018 at 12:40 | vote | accept | espresso_coffee | ||
| Aug 24, 2018 at 12:40 | comment | added | espresso_coffee | Do we need RAISERROR in proc for Insert ? Other than that I think this should work for me. Thanks for your time to explain everything in details. | |
| Aug 24, 2018 at 12:36 | comment | added | Dan Guzman | An explict transaction in a proc is appropriate only when there multiple statements in the proc. In that case, the pattern I suggest is a TRY block with BEGIN TRAN followed by the DML statements, and a final COMMIT. The CATCH block would be IF @@TRANCOUNT > 0 ROLLBACK;THROW;. As in my example, no TRY/CATCH is needed because either unexpected errors as well as the RAISERRROR will be returned to the client app. | |
| Aug 24, 2018 at 12:32 | comment | added | espresso_coffee | So if I want to handle this situation with K violation maybe I just leave the code without try/catch block and my server side language should handle that error? Previously discussed that means that you example above does not need try/catch block as well. Also is it good idea to keep try / catch block and insert error in table? | |
| Aug 24, 2018 at 12:30 | comment | added | Dan Guzman | There is no value with TRY/CATCH for the PK violation since the error will be returned to the client by default without additional T-SQL code, where it can be handled in the app as desired. A CATCH block is only needed when you need to perform additional actions, like rollback an explict transaction. Autocommit simply means that each statement is automatically committed when successful and automatically rolled back after errors. | |
| Aug 24, 2018 at 12:28 | comment | added | espresso_coffee | Dan, for Insert transaction should I use try/catch to identify PK violation or there is something better than that? Also I'm not familiar with auto commit, is that same as begin trans or something different? Can you please provide example for that situation. Also does that mean that code above does not need try/catch block? | |
| Aug 24, 2018 at 12:13 | history | answered | Dan Guzman | CC BY-SA 4.0 |