2

I have recently implemented SQL rowversion to prevent concurrency issues in my system. I use rowversion in where clause when updating single rows in the tables. So far I have tested and seems like a good solution. Now I'm looking for an easy way to implement this feature in my system. Here is SP that runs when user wants to update the record:

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @OriginalRowVersion ROWVERSION
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN
        UPDATE dbo.Building
        SET Status = @Status,
            Name = @Name,
            Code = @Code,
            ActionDt = CURRENT_TIMESTAMP
        WHERE RowVersion = @OriginalRowVersion
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('Buildingwith code %s was modified or deleted by another user.', 16, 1, @Code);
        END;
    END;

If I want to execute SP above I would need to pass required parameters. This is how I call SP in SQL Management Studio:

EXEC UpdateBuilding
    @Status = 1,
    @Name = "Rockefeller Center",
    @Code = 436,
    @OriginalRowVersion = 0x0000000000006955;

Now I started looking how to implement this in my system where I use ColdFusion to communicate with Datatbase. Here is example on how this procedure will be executed with CF 2016:

<cfstoredproc procedure="UpdateBuilding" datasource="#dsn#">
    <cfprocparam dbvarname="@Status" value="#trim(arguments.status)#" cfsqltype="cf_sql_bit" />
    <cfprocparam dbvarname="@Code" value="#trim(arguments.code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.code))#" />
    <cfprocparam dbvarname="@Name" value="#trim(arguments.name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.name))#" />
    <cfprocresult name="Result"/>
</cfstoredproc>

You can see that all values are passed with arguments that user submits in the form. However, updating based on PK value (Code column in my case) was pretty simple. Now I have binary value and that makes everything more complicated. First I use JSON to send the data to client side. Sending rowversion in JSON object would require converting that value to binary and then converting back when user submits the form. I'm wondering if there is better way to achieve this? Ideally I would not even send rowversion value to the user side. I woul keep that on the back end and once user submits the form pull row version value based on PK then call stored procedure. If anyone knows good way to handle this kind of situations please let me know. I have not used rowversion before and this is new to me.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • Why would you use `rowversion`? Declare a primary key for the table (such as an `identity()` column) and use that. – Gordon Linoff Aug 27 '18 at 13:56
  • @GordonLinoff Here is good example why: https://dba.stackexchange.com/questions/215628/best-way-to-prevent-deadlocks-and-server-locking-for-single-row-insert-update-tr/215779?noredirect=1#comment423687_215779 Using `rowversion` I would be able to prevent concurrency. Users will be notified if someone else is updating the same record. – espresso_coffee Aug 27 '18 at 13:58
  • . . I think you are missing the point. A primary key *is* being used to lookup the row. `rowversion` is being used to be sure that the row has not changed. – Gordon Linoff Aug 28 '18 at 01:06
  • So how I will use rowversion? Can you explain please. – espresso_coffee Aug 28 '18 at 01:10
  • . . I mean, I supposed you could look for a particular row with a given version. But I think of it more as a way to see if a given raw at time *t* has been changed at time *t'*. – Gordon Linoff Aug 28 '18 at 01:16
  • I *think* he's saying use both, `@Code` to lookup the record and `@rowversion` to see if it's changed since the user retrieved it. – SOS Aug 28 '18 at 01:23
  • Would that be where code = 'x' and rowversion ='xx' something between those lines? – espresso_coffee Aug 28 '18 at 01:27
  • That's my impression, yes. *"Ideally I would not even send rowversion value to the user side"* Then you wouldn't know if the row changed. Looking it up after the fact wouldn't help. Dealing with binary isn't as hard as you're thinking. After you retrieve the value, convert it to a string (hex or base64) for sending back to the client side: `binaryEncode(queryName.theBinaryColumn, 'hex')`. When the client side sends it back, decode it back into binary ``. – SOS Aug 28 '18 at 01:40
  • Just saw there's already an example of the where clause in [your other thread](https://dba.stackexchange.com/questions/215628/best-way-to-prevent-deadlocks-and-server-locking-for-single-row-insert-update-tr/215779?noredirect=1#comment423687_215779). *"if I want to update Primary Key"* Why do you want to update it? Usually PK's are immutable. If updating it is a requirement, then it may not be the best candidate for a PK. – SOS Aug 28 '18 at 04:09

0 Answers0