November 16, 2007 at 7:17 am
Am I correct in thinking that if I had a stored proc that ran the following code
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT MAX(CategoryPK)+1 as NewCategoryPK FROM CATEGORIES
Then there is no way that the same value for NewCategoryPK could be returned if multiple
processes were trying to call the proc at the same time? They should wait in line one by one which
may slow things down but no duplicate values would be returned?
Thanks
November 16, 2007 at 8:11 am
Sorry forgot the UPDATE Statement in the middle!!
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
UPDATE CATEGORIES
SET CategoryPK = CategoryPK + 1
SELECT Max(CategoryPK) as NewCategoryPK FROM CATEGORIES
So if multiple requests access it they would all be given unique numbers back and the categoryPK
would increment sequentially?
November 19, 2007 at 7:47 am
Well, serializable is the harshest isolation level, and it will block everyone else from the entire key-range that your query touches. In general, this is the last option, since concurrency will suffer badly.
I don't quite follow the update logic..
The example shifts all keys up one step, then you find the new max() value?
Why update all keys? Or is there just one row? Or many?
There's probably other ways to do what you want to do without overkill on the locks.
/Kenneth
November 19, 2007 at 8:30 am
Hi Thanks for replying I was getting a bit confused myself at the time of writing that.
Currently we have a table that uses an identity field for the PK. We currently do the work on DEV (as in setting up new sites and the categories that go with) and copy the data from this table up to the Live server overwriting the whole of the live tables rows each time. However now I've been tasked to do the following.
-Allow certain users to add categories straight onto the live system from a category manager tool.
-Still need to let work be done on dev and copy up those categories to live. This means I won't be able to overwrite the whole table anymore just copy up the new categories and append to the new table.
-This means that
a) I can't have two keys (across the 2 servers) the same.
b) I'm not allowed to convert the integer based keys into guids (which is how I solved a similar problem)
At the moment just the DEV server table has identity switched on as all the new values are created there and then copied to live. However after this change the live server will also need to be able to generate new keys for new records when required.
So I need to replace the current key generation system which is using identitys (on dev) to a new system.
I was going to give each server a 3 digit code (dev =100, live=200 etc) and then create a new key generator table that holds the last max(categoryPK) from the category table.
I would then turn off the identity field on DEV and either
-convert it to the new combined key (databasecode + incremental no)
or
-keep the column but it would hold just the incremental part (2334 generated by a new key generator)
-add a new column in which would be the database source column (100=dev)
-change all the code to use these 2 columns to identify the row
(or at least new code could use these 2 and maybe still have a computed/combined column (databasecode + incremental no) for legacy code)
Then I would need to add some code to generate the keys. Replacing my existing code in stored procs that currently does
insert into CATEGORIES values(@values) select scope_identity() --get new key back out
with something like
exec usp_sql_get_new_key @newID output
insert into CATEGORIES values(@newId,@values)
where the stored proc usp_sql_get_new_key was something like
SELECT @DatabaseID = dbo.udf_SYSTEM_CONSTANTS('DATABASE_CODE') --get the 3 digit code for server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT@NextID = CurrentValue
FROMKEY_GENERATOR
--if value is null or 0 we need to find the current max from appropriate table
IF ISNULL(@NextID,0) = 0
BEGIN
SELECT@NextID = ISNULL(MAX(CategoryPK),0)+1
FROMCATEGORIES
END
ELSE
SELECT @NextID = @NextID + 1
--now update the key generator table so that the same ID isnt used again
UPDATEKEY_GENERATOR
SETCurrentValue = @NextID
SELECT @New_ID = CAST(@DatabaseID + CAST(@NextID as varchar) as int)
So that if two processes wanted new keys at the same time (Which would be very unlikely as its only admin that create them through a web interface) then the
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE command would prevent two IDs coming back the same.
I don't need to worry about the IDs having to be consistent (1,2,3,4 etc)
just got to make sure a key generated on dev could not possibly be given the same numeric value as a key generated on live.
The reason I can't change the datatype for the key is as usual the time it would take to rewrite all the code and plus there are a few third party systems expecting the IDs to be numeric.
Plus I need to keep all the existing keys generated before I make this change.
I'm sure theres lots of ways to achieve this or something similar or hopefully even better quicker ways that involve me not doing much coding at all but I'm probably daydreaming again.
Thanks for any help or advice in advance.
November 20, 2007 at 2:26 am
A couple of thoughts...
The whole thing sort of gives me a chilly spine. It just doesn't feel right.
Example: you know that an identity value alone designated as PK doesn't give you what you probably intend? You also need to enforce uniqueness on the natural key (the category name?)
Mixing dev and prod environments is usually not a very good idea. Prod data should spawn in production, not in dev, and the two environments should not be 'connected' in any way.
For example: Consider this. Is there any difference between these two?
ID = 1001, category = 'Category A'
ID = 2001, category = 'Category A'
According to the plan you described, one is from dev and the other prod (100 and 200 series), but with the same category name. Is this something that is expected? It will probably happen sooner or later, and per definition they are not duplicates, since the PK is the ID, and it's different.
Should you enforce the alternate key (category) then you'll just get an error if this was to be introduced into the live database, but how would that error be handled then?
Sounds to me that the whole scheme is about not risking ending up in that situation?
The actual values of 'ID' shouldn't matter to the 'app', regardless of if it's created in dev or prod.
This is what feels fishy to me in particular.
/Kenneth
November 20, 2007 at 3:26 am
Yes your right I don't want to end up with two values in the category table with the same textual value even if they did have different IDs. The natural key is the category but the categoryPK is used to
store the category values selected by users when they save related data. Once a site goes live new categories may be added later but the process is that they are always added to dev/demo first and then copied to live.
I was going to write an uploader that would enable the transfer of data from dev to live and check for the existence of duplicate categories before copying them up to prevent this from happening.
The issue is that the sites are data driven in that as well as category options for lists, records in settings table determine which columns appear on results pages and which fields appear in X order on certain pages. This setting data is (at present) only ever created by our admin during set up/config of a site which happens on dev and then when the site is made live its copied to the prod server. This has worked fine until the idea was spawned that certain settings could be managed by the site owners themseleves purely on their live site. They would be able to open up a CMS page and change the ordering of columns,fields,display labels etc. At the moment the settings tables are totally in sync dev>prod but if they did this certain setting records would only exist on the live server and I don't want to overwrite them when making updates from dev.
I could just forget about worry about keeping the IDs that were created on DEV having to be the same when copied to Live and just generate totally new keys for all the records as they are inserted into the tables. As a lot of setting data is interrelated I would have to write code to ensure that as new IDs are given to the records on live all the related data is updated with these new IDs.
At the moment its nice that if the customer requests an extra setting we do the work on dev/demo show them the change and then can make it live either by copying the whole settings tables up or manually copying/paste the related records from the settings tables and as the IDs are the same this works with no changes to the records between dev and live. Also we currently have sites set up on a demo URL for some period of time before they go live and there are a few 3rd party sites that need that time to set up feeds so that they can post data to us. They use a page that lists all the categories and categoryPKs so they can set this feed up. Once the site goes live as these values don't change they don't need to make any changes. If all the IDs changed on the transition from dev to live they would need to reintegrate or wait until the site was live.
I am going to have to write an uploader tool either way by the looks of it and I'd rather make the right decision even it means more work now than the wrong one if it means loads of work later. I have some code that we use when setting up a new site on dev as it clones an existing site by copying siteAs setting records and creating a new siteB with those records but new updated IDs so I could use that code in the transition from dev to live.
What do u reckon? Is it all a nasty pile of mess that I could be doing without? Can I get my boss to change his mind and not let customers add categories/settings on the fly on the live site or should I hand in my notice and book a taxi home?
Ideas, thoughts, perls or wisdom or just an abusive slating is all welcomed with open arms.
November 21, 2007 at 2:36 am
Seems like what you have in place is a sort of 'model' of how these sites works and are built.
So far, this model has worked ok according to the business rules at hand.
Now, the boss comes with a new set of rules, and the model no longer fits.
Basically, there are two options.
Change the 'model' so that it will be compliant with the new business rules.
Change the rules so that they will be compliant with the existing model.
The existsing 'model' incorporates things as the integration between dev and prod environments.
(dev is the actual 'master' of id's in production - I'm not too sure this is a good idea overall)
What you have in practice is not two environs, you have a single production environment that is distributed, called 'dev' and 'prod'.
Gut feel is that you should change the model in such a way that 'dev' becomes truly 'dev' - no tendrils reaching into production, it should be totally sealed off. Then 'prod' also becomes truly production all on it's own.
/Kenneth
November 21, 2007 at 5:31 am
Yes your right in that at the moment dev isn't really dev but more stage 1 of a 2 stage process
in rolling out sites where each stage takes place on a different server.
I am currently trying to think of ways that this model can be changed but its not as flexible as I'd like it to be. Currently when we set up new sites I just fill out a form specifying the sitename,URL and the site I want to "clone/replicate" and other major site specifics and click a button. The system then creates all the necessary DB records by copying from the cloned site. It also sets up the necessary folders, creates the dev & demo site in IIS, virtual folders and does all the security settings. The techy guys won't let me run this process on the live server for security reasons and incase something goes wrong. Therefore we create the sites on dev do the work and copy it up. As the replicate takes away 95% of the pain of creating a new site it seems stupid not to use it as it saves so much time and my boss loves it as he can crank the handle so hard n fast that sites are rolled out too quick to count.
My boss has actually just come out and drawn a lovely little scribble on the white board.
stage 1: he wants me to write an uploader tool to easily copy new sites from dev to live (stage1>stage2)
stage 2: he wants me to enable the CMS/Category manager on the live server so that site admin can create their own settings/categories.
stage3: he wants the ability to take a copy of a live site and bring it down to dev. Freeze the live site so no changes can be done on it. Allow work to be done on the dev server and then copy it back up to live again.
In between trying not to laugh and cry I tried explaining that they are all interelated and that I don't really want to do stage 1 if I am going to have rewrite the whole system to do stage 2 and 3 but I don't think he knows how the system works to be honest. But hes probably no different from most bosses in that regard.
At the moment I am thinking about some sort of splitting of the current DB so that the settings/categories are removed and placed in some sort of shared DB that is used by demo & live. Then all we need is a new webserver for demo sites so the replicate can be run without affecting live sites and I can keep dev to myself.
Lots of ideas so little time. At least I haven't started the work yet so anythings possible.
Thanks for your help anyway.
November 21, 2007 at 11:37 pm
The easiest way I've seen to handle this problem does not involve any sort of serialization. There should be a control table that contains the next number to use. Anyone that wants to get a new ID just has to get the next number and update the control table at the same time. You don't have to use a transaction or anything.
The control table would have a column called NewID. Here's the code:
DECLARE @NewID INT
UPDATE ControlTable
SET @NewID = NewID, NewID = NewID + 1
Try it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply