SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I need to do something retarded.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/18/2010 :  12:20:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Hello all. I need help to do something which I am very annoyed about.

There has been a design decision in our next version of software (which I've been bitterly against) and which has just bit us in the ass in pre production testing (surprise)

We have a schema where the primary keys of the various tables are [ID] columns which are INT and which need to get an ever increasing value for inserts.

Surely you would use an IDENTITY property right. That's the obvious thing to do.... Easy -- reliable.

However the design is to use a hiLo algorithm where a seed value is kept in a different database and what happens when the application starts is this:

It decides that it needs a pool of numbers to form the new keys based on a keysize setting -- lets say that it reserves 1000 keys at a time.

So to do this the application first reads the hiLo seed and sees (for sake of argument (5) it then increments the hilo seed by 1 (so the hiLo is now 6) and this actually represents the numbers 5000 through 5999.

The application then USES those numbers for the inserts -- it doesn't rely on an auto number column and reading the identity value returned on an insert. It uses this pool across everything it does so if it needs to insert into 5 tables in turn it will use the first 5 numbers form it's pool to make the keys. This was apparently to improve performance (the rational was that the application would never need to bother retrieving ID's after an insert it would automatically know what it had just inserted and use that to form the foreign keys for any child objects). These inserts are all done automatically via hibernate so there are no stored procs involved.

So onto the problem

The application is supposed to never insert a key value lower than the highest that currently exists in the key column. However it has been and this has been causing exceptions and other problems (because our app RELIES on ID order -- DOUBLESIGH) This seems to be intermittent. However this only causes a problem further down the line -- the actual insert all goes through. So there is no immediate way to tell when this nasty behaviour happens.

So onto what I actually need to do.

I think I need to make a check constraint on every ID column in the database. I'll need to implement this check constraint so that an inserted value checks to make sure that it is the highest in the column (application only ever inserts 1 row at a time).

The only way I can thing of to do this is to make up a function for each table and add that as a check constraint call.

Is there any easier way?

I'm so angry about this design decision. It seems so totally retarded to me.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/18/2010 :  12:24:15  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Note -- I'm not trying to put something in to FIX this (I don't think it is fixable -- I think the whole concept is fundamentally screwed) I just need to get a constraint in there so that in testing we can see when this happens and then the devs can go back and look at the code.

Note -- oh yeah, you may be wondering. "What if I need to insert something manually into the database myself" I raised this question the first time the subject came up and the answer was "oh yeah - we didn't think about that"

I'm wondering if we hired Whitefang about 8 months ago.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 06/18/2010 :  12:29:59  Show Profile  Reply with Quote
So let the numskull who insisted on this loony decision create the work-around hack.

Hibernate. <Shiver/>

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/18/2010 :  12:48:35  Show Profile  Reply with Quote
Well ... FWIW ... we do the "pool of numbers" thing - albeit in very limited cases.

We use it to allocate a range of numbers that are known before-insert, and we also want a simple way of setting the available range - so that each client's range is unique so we can copy-back from a client database without any possible collisions (e.g. we build a CMS page template on a particular client's site and then want to make that template available to all other clients. The ID number is guaranteed unique)

To your problem:

How about mechanically generating triggers to check the ID number on INSERT? They could then just "phone home" and LOG to a table when something looks wrong, rather than actually ROLLBACK (although they could do that too)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/18/2010 :  12:50:30  Show Profile  Reply with Quote
"too" === ROLLBACK and then LOG it, obviously ... otherwise there won't be anything in your LOG table!

So ... you not planning on seeing the sun this weekend then?!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 06/18/2010 :  13:29:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
OK, this is a ***STUPID*** idea, and I haven't done thorough testing, but it's a possible workaround for you:
create table test1(i int not null default(cast(@@dbts as int)) primary key, v rowversion not null)
create table test2(i2 int not null primary key default(cast(@@dbts as int)), v rowversion not null)
create table test3(i3 int not null default(cast(@@dbts as int)) primary key, v rowversion not null)
create table test4(i3 int not null default(cast(@@dbts as int)) primary key)

insert test1 default values
insert test1 default values
insert test2 default values
insert test3 default values
insert test1 default values
insert test2 default values
insert test3 default values
insert test1 default values
insert test2 default values
insert test3 default values
insert test1 default values
insert test4 default values
insert test2 default values  -- dupes value in test4
insert test3 default values
insert test1 default values

select * from test1
select * from test2
select * from test3
select * from test4

insert test4 default values -- will dupe value in other table on next insert
insert test4 default values -- PK violation because it doesn't increment @@DBTS due to missing rowversion column
Since @@DBTS is database-scoped and monotonically incrementing, you could hijack it as a "sequence" generator. However you have no control over the values it generates, and it also increments on updates. It also requires a rowversion/timestamp column on all the tables that would use the sequence (which wastes space unless you already have them).

Since this value is also supposed to come from another database this will not work without a lot of extra plumbing. You'd also exceed the limit of an int value after 2 billion inserts and updates. I imagine if one of us put our minds to it we could have a centralized sequencing system that could use this technique, but I can't see it being worth the hassle. You'd have to insist on a general database call for every activity in the database, and you still couldn't support the hi-lo seed and increment (that I can see anyway).

And in the spirit of being contrary, maybe you should check your local job market and update your CV/resume.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 06/18/2010 :  13:35:26  Show Profile  Visit robvolk's Homepage  Reply with Quote
I just realized that it won't work with multi-row inserts either.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/18/2010 :  13:49:28  Show Profile  Reply with Quote
The only viable "hack" here is to convert the system to use IDENTITY to generate the numbers.

Anything else will just be a long term problem, and you are better of dealing with it now in pre-production, instead of looking like idiots when the system goes live.





CODO ERGO SUM
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 06/18/2010 :  14:02:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
In re-reading your post I realized I completely missed what you were asking for, I apologize.

Here's a possible constraint to do what you need, but it will not perform well under high volume:
create table myTable (id int not null primary key)
go
create function dbo.checkID(@i int) returns bit AS 
BEGIN
	declare @b bit
	if @i>=(select max(id) from myTable)
	set @b=1
	else
	set @b=0
	return @b
END
GO
insert myTable values(1)
alter table myTable with nocheck add constraint CHK_CheckID CHECK (dbo.checkID(ID)=1) 
GO
insert myTable values(2)
insert myTable values(0) -- fails
insert myTable select 10 union all select 11
insert myTable values(5) -- fails
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/20/2010 :  13:19:22  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Hi all.

Cheers for all the replies. I'm even more convinced that this whole pool of ID's is a terrible idea and I'm going to keep telling all the devs this. I don't think it'll change their minds about the whole idea though. I'm not sure how easy it is to change at this stage of development either -- I think the *advantage* of hibernate is that it is easy to change this but then the schema would have to be changed etc. etc.

I find it even more bizzarre that this 'performance' decision has been taken considering that the applicaiton is closer to a data warehousing app than a oltp app and inserts (i think) will be the least of our performance problems.

Cheers - robvolk. I'd allready come to the same function and have written a dynamic sql wrapper to apply it to all the tables -- I was just hoping there was some other way.

Cheers again.

Charlie.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

niechen861102
Starting Member

9 Posts

Posted - 06/20/2010 :  21:42:43  Show Profile  Reply with Quote
spam removed

Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 06/21/2010 :  10:25:44  Show Profile  Reply with Quote
The "advantage" of hibernate (and other ORMs) is that they allow the developers to be lazy. At the expense of performance and quality.
That's all.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000