StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I slapped together the following script to proof this trick I used in years past. If you use it, you'll need to modify it to suit your purposes. Comments follow:</p> <pre><code>/* CREATE TABLE Item ( Title varchar(255) not null ,Teaser varchar(255) not null ,ContentId varchar(30) not null ,RowLocked bit not null ) UPDATE item set RowLocked = 1 where ContentId = 'Test01' */ DECLARE @Check varchar(30) ,@pContentID varchar(30) ,@pTitle varchar(255) ,@pTeaser varchar(255) set @pContentID = 'Test01' set @pTitle = 'TestingTitle' set @pTeaser = 'TestingTeasier' set @check = null UPDATE dbo.Item set @Check = ContentId ,Title = @pTitle ,Teaser = @pTeaser where ContentID = @pContentID and RowLocked = 0 print isnull(@check, '<check is null>') IF @Check is null INSERT dbo.Item (ContentID, Title, Teaser, RowLocked) values (@pContentID, @pTitle, @pTeaser, 0) select * from Item </code></pre> <p>The trick here is that you can set values in local variables within an Update statement. Above, the "flag" value gets set only if the update works (that is, the update criteria are met); otherwise, it won't get changed (here, left at null), you can check for that, and process accordingly.</p> <p>As for the transaction and making it serializable, I'd like to know more about what must be encapsulated within the transaction before suggesting how to proceed.</p> <p>-- Addenda, follow-up from second comment below -----------</p> <p>Mr. Saffron's ideas are a thorough and solid way of implementing this routine since your primary keys are defined outside and passed into the database (i.e. you're not using identity columns--fine by me, they are often overused).</p> <p>I did some more testing (added a primary key constraint on column ContentId, wrap the UPDATE and INSERT in a transaction, add the serializable hint to the update) and yes, that should do everything you want it to. The failed update slaps a range lock on that part of the index, and that will block any simultaneous attempts to insert that new value in the column. Of course, if N requests are submitted simultaneously, the "first" will create the row, and it will be immediately updated by the second, third, etc.--unless you set the "lock" somewhere along the line. Good trick!</p> <p>(Note that without the index on the key column, you'd lock the entire table. Also, the range lock may lock the rows on "either side" of the new value--or maybe they won't, I didn't test that one out. Shouldn't matter, since the duration of the operation should [?] be in single-digit milliseconds.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload