{"id":2135,"date":"2018-02-27T16:30:58","date_gmt":"2018-02-27T14:30:58","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=2135"},"modified":"2018-07-12T23:26:06","modified_gmt":"2018-07-12T21:26:06","slug":"homebuilt-sequential-columns","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2018\/02\/27\/homebuilt-sequential-columns\/","title":{"rendered":"Homebuilt sequential columns"},"content":{"rendered":"

I gave my introductory session on transactions at all three of the South African SQL Saturdays in 2016, as well as at SQL Saturday Oregon in October 2017, and something that came up in most of them was the \u2018manual sequence\u2019, the idea of using a column in a table to store a max value and using that in place of an identity column or sequence object.<\/p>\n

To be clear, I don\u2019t think this is a good idea. The identity column works well if a sequential series of numbers are needed. If the number sequence needs to that spans tables, then the sequence object is a good replacement.<\/p>\n

But, there will always be some requirements that insist on gap-less sequences, or insist on not using identity (probably for \u2018compatibility\u2019 reasons), so let\u2019s see how to do it properly.<\/p>\n

To start, the common attempt (taken from a random Stack Overflow answer)<\/p>\n

DECLARE @next INT\r\nSET @next = (SELECT (MAX(id) + 1) FROM Table1)\r\n\r\nINSERT INTO Table1\r\nVALUES (@next)<\/pre>\n
or, a slightly different form<\/p>\n
DECLARE @next INT\r\nSELECT @next = SequenceNumber + 1 FROM Table1\r\n\r\nUPDATE Table1\r\nSET SequenceNumber = @Next;\r\n\r\n-- Then use @Next in another table for an insert<\/pre>\n
This doesn\u2019t work. Oh, to be sure it\u2019ll work in testing, but once we get some heavy concurrent access, its flaws become apparent.<\/p>\n
To test the first one, I\u2019m going to use a table that just has an ID (supposed to be unique) and a second column to record which session_id did the insert<\/p>\n
CREATE TABLE TestSequence (\r\nManualID INT NOT NULL,\r\nSessionID INT\r\n)<\/pre>\n
And then run this 100 times from 10 different sessions<\/p>\n
DECLARE @next INT\r\nSET @next = (SELECT (MAX(ManualID) + 1) FROM TestSequence)\r\n\r\nINSERT INTO TestSequence\r\nVALUES (@next, @@SPID)<\/pre>\n
\"Duplicates\"<\/a><\/p>\n
And it doesn\u2019t work because the select statement takes a shared lock. Shared locks are shared, and so multiple sessions can read the same max value from the table, then write back that same value+1 to the table, either generating duplicate rows or primary key\/unique constraint violations (hopefully the latter)<\/p>\n
So how do we fix it?<\/p>\n
One option is to wrap the two statement in a transaction and add the UPDLOCK hint to the select. This ensures that no one else will be able to read the same max value from the table, but depending on indexes it could also cause some blocking and resultant slow queries.<\/p>\n
Another way is to make the insert (or update) and the select a single atomic operation, by returning the inserted (or updated) value from the insert (or update) statement. We can use the OUTPUT clause for this.<\/p>\n
Option one would have code similar to this:<\/p>\n
BEGIN TRANSACTION\r\n\r\nDECLARE @next INT;\r\nSET @next = (SELECT (MAX (ManualID) + 1) FROM TestSequence WITH (TABLOCKX, HOLDLOCK));\r\n\r\nINSERT\u00a0 INTO TestSequence\r\nVALUES\u00a0 (@next, @@SPID);\r\n\r\nCOMMIT TRANSACTION<\/pre>\n
And option 2 looks like<\/p>\n
INSERT INTO TestSequence\r\nOUTPUT inserted.ManualID\r\nSELECT MAX(ManualID) + 1 FROM TestSequence WITH (TABLOCKX, HOLDLOCK)<\/pre>\n
The locking hints are, unfortunately, necessary. I tried several variations with less restrictive hints and they either:
\n– Produced duplicates
\n– Deadlocked when the table was small
\n– Deadlocked all the time<\/p>\n
None of which are desired, hence the use of an exclusive table lock to serialise access. Of course, the restrictive locks will make this slow under concurrent usage. An index on ManualID will help, a bit.<\/p>\n
Now we can test both of those the same way we tested the first version. An easy way to see whether there are any duplicates is to check the count and the distinct count.<\/p>\n
\"DuplicateCheck\"<\/a><\/p>\n
To reiterate something I said earlier, I do not recommend using this. Identity columns, with their gaps, are fine for the majority of cases, especially the use of them for artificial primary keys. Artificial keys, if used, are meaningless numbers that should not be exposed to users, and hence gaps should be irrelevant.<\/p>\n
The need for a gap-less sequence, stored in the table, should be an exceptional one, not a common one.<\/p>\n","protected":false},"excerpt":{"rendered":"
I gave my introductory session on transactions at all three of the South African SQL Saturdays in 2016, as well as at SQL Saturday Oregon in October 2017, and something that came up in most of them was the \u2018manual… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: Homebuilt sequential columns","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,17],"tags":[],"class_list":["post-2135","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-yr","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=2135"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2135\/revisions"}],"predecessor-version":[{"id":2290,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2135\/revisions\/2290"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=2135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=2135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=2135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}