I need to do something retarded.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I need to do something retarded.

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-18 : 12:20:09

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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-18 : 12:24:15

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-06-18 : 12:29:59

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.
________________________________________________

Kristen
Test

22859 Posts

Posted - 2010-06-18 : 12:48:35

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)

Kristen
Test

22859 Posts

Posted - 2010-06-18 : 12:50:30

"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?!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-18 : 13:29:39

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-18 : 13:35:26

I just realized that it won't work with multi-row inserts either.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-18 : 13:49:28

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-18 : 14:02:54

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-20 : 13:19:22

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

niechen861102
Starting Member

9 Posts

Posted - 2010-06-20 : 21:42:43

spam removed

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-06-21 : 10:25:44

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.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources