On a side note, When is “sppc_data_uniqueid” used vs “sppc_data_uniqueid_withouttran”.Our application uses only the “sppc_data_uniqueid_withouttran”. This SP does a SELECT with “UPDLOCK, ROWLOCK)” and in the same storage procedure tries INSERT/UPDATE a record. Since a transaction is not used around these two statements, under some special conditions the locking that we observer can happen. Should the process be using “sppc_data_uniqueid”, which puts a transaction around the other SP? Were there any issues while using the one with transaction? Please provide some history regarding the usage of these two SPs?
Posted: 2 years ago
Posted: 21 Jan 2020 12:06 EST
Jim Kelly (JimKelly_GCS)
Senior Principal Engineer, Technical Support, Runtime Engine
My reading of this is that the caller should use a transaction.
You are correct that without the transaction boundary the statements should be atomic and the purpose of the select with the update rowlock is defeated. In that instance it is possible for 'atomic' transactions to cause a deadlock. Using the outer SP should avoid this...