row-locking question

i'm considering implementing my own artificial key table that would
work similarly to an identity value. insert transactions would first
have to visit this artificial key table, lock the artificial key row
defining the key needed by the data row to be inserted. after the table
lock, it would get the value, insert the record, increment the value,
and update the artificial key table.
the reason i am doing this in a user-defined fashion instead of using
an identity column are varied. first, sometimes the sequence needs to
be semi-random. also, i want to avoid all the issues with migrating
identity columns.
so, i have two questions:
1) assuming that the code and locking are written the the appropriate
levels of granularity, integrity, and speed, is this a reasonable thing
to attempt?
2) all the books i am reading say "trust SQL Server locking management"
-- but i'm guessing they didn't have this kind of use in mind. is this
a circumstance where manual transactional locking is appropriate?
thanks for any suggestions / advice,
jasonI've given this a lot of thought, because IDENTITY doesn't provide a
database-wide unique value and the size of ROWGUIDs hinder performance. I
had considered the possibility of two user-defined functions, one scalar
function that returns a single surrogate value, and one table-valued
function that returns a specified number of values. The main problem with
this scheme is the issue of locking. Either you can obtain the surrogates
prior to starting a transaction, or you have to find another way to deal
with the locks and blocking, or you set up separate identity ranges for each
table. What is needed is a way to serialize access to a "next key" table
outside of the calling transaction. I've thought about writing an extended
stored proceedure or using the sp_OA procs to do this because they can open
or use a separate shared connection, but I haven't had time to pursue the
issue futher, nor am I convinced that the performance will be satisfactory.
Extended procedures are deprecated in SQL Server 2005 because it hosts the
CLR, so anything I write now will probably have to be rewritten later.
"jason" <iaesun@.yahoo.com> wrote in message
news:1122929530.893879.301170@.g44g2000cwa.googlegroups.com...
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the table
> lock, it would get the value, insert the record, increment the value,
> and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable thing
> to attempt?
> 2) all the books i am reading say "trust SQL Server locking management"
> -- but i'm guessing they didn't have this kind of use in mind. is this
> a circumstance where manual transactional locking is appropriate?
> thanks for any suggestions / advice,
> jason
>|||jason wrote:
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the
> table lock, it would get the value, insert the record, increment the
> value, and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable
> thing to attempt?
> 2) all the books i am reading say "trust SQL Server locking
> management" -- but i'm guessing they didn't have this kind of use in
> mind. is this a circumstance where manual transactional locking is
> appropriate?
> thanks for any suggestions / advice,
> jason
You can do this, but try and keep the key value generation routine in a
separate transaction to prevent tying it to the new inserts themselves.
For example:
Begin Tran
Update dbo.keytest
Set NextKey = NextKey + 1
Select @.key = NextKey From dbo.keytest
Commit Tran
Insert Into dbo.MyTable (newkey) values (@.key)
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Actually you can save a few steps by doing this below. Since the Update is
ATOMIC by itself you don't need a BEGIN TRAN - COMMIT and the extra select.
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u3zEmPvlFHA.3256@.tk2msftngp13.phx.gbl...
> jason wrote:
> You can do this, but try and keep the key value generation routine in a
> separate transaction to prevent tying it to the new inserts themselves.
> For example:
> Begin Tran
> Update dbo.keytest
> Set NextKey = NextKey + 1
> Select @.key = NextKey From dbo.keytest
> Commit Tran
> Insert Into dbo.MyTable (newkey) values (@.key)
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||This kind of thing is best done at the raw engine level. if you really
need it, switch products to something with row-level locking. Have you
looked at Firebird, Interbase and other optimistic concurrency control
databases?|||seems reasonable. decreases the lock time, and only costs me an ID
value if the insert fails for some reason. thanks.|||seems like a good implementation, thanks!|||no, i haven't looked at any alternative database products. this
database is thoroughly intwined with a growing .NET middleware, and i
think the company is looking quite enthusiastically at Sql Server 2005,
with the .NET platform built in.
Sql Server does claim to have row-level locking. they just say it is
good to trust the lock manager for most tasks. is Sql Server manual
row-level locking inadequate in some way?
thanks,
jason|||Hi
SQL Server 2000 and 2005 both do full row level locking in their default
configurations.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jason" <iaesun@.yahoo.com> wrote in message
news:1122989683.046590.261270@.f14g2000cwb.googlegroups.com...
> no, i haven't looked at any alternative database products. this
> database is thoroughly intwined with a growing .NET middleware, and i
> think the company is looking quite enthusiastically at Sql Server 2005,
> with the .NET platform built in.
> Sql Server does claim to have row-level locking. they just say it is
> good to trust the lock manager for most tasks. is Sql Server manual
> row-level locking inadequate in some way?
> thanks,
> jason
>|||by the description of the task at hand, does this sound like something
i would have to employ manual locking to accomplish? or would beginning
a transaction with an update, and then commiting the transaction when
"safe" do all the locking i need?

ROWGUIDCOL

I've read everything BOL says about ROWGUIDCOL and it's still not clear to me why it is necessary. It appears to be similar to an IDENTITY property, except that it does not have a seed or an increment. Because of that, it appears to have no practical purpose other than annotating a CREATE TABLE script to let people know it is used to identify a row.

ROWGUIDCOL as described seems to fit the case of where a guid is the primary key (I do realize it does not enforce uniqueness).

IDENTITY can generate unique values for a key for a single table on a single machine. If you want to automatically generate UNIQUE values accross multiple tables or machines, use a uniqueidentifier column that defaults to NEWID() or is always given a new guid value by your application when your application creates a row. ROWGUIDCOL is just a convenient designator for a GUID-based unique ID column. It also gives you the ability to reference it using $ROWGUID. GUID-based identifiers are used extensively in replication scenarios.

|||Thanks for that clarification. $ROWGUID could be useful. What if the guid is only the leading edge of the primary key, not the entire primary key? In other words, does the guid have to uniquely identify the row for $ROWGUID to work?

NEWSEQUENTIALID() is an alternative to NEWID(). As I posted in http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=52136, Newid in C# caused reduced performance because of index page splits. Using NEWID() in T-SQL has the same problem. Using NEWSEQUENTIALID() instead of NEWID() greatly improved insert performance without any adverse effects on select performance. NEWSEQUENTIALID() provides an ascending sort order to the guids, which makes it more similar to IDENTITY than NEWID() is.sql

Row insert timestamp?

Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!Track it yourself

USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) PRIMARY KEY
, Col2 Char(1)
, Col3 datetime DEFAULT getdate()
)
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!|||Originally posted by blm14_cu
But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!

Did you try my code?

And lazy is good...|||Here, Here's lazy...

uncomment the EXEC to exeute the statements...

But

BE CAREFUL

You can't easily get rid of a column once you add it....|||ooops..forgot the code

Use Northwind
GO

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99|||Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.|||Originally posted by blindman
Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.

Yo blind dude...

Want to post how you get rid of a column?|||I use ALTER TABLE DROP COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself. You can do it in Enterprise Mangler too.

-PatP|||Being one of the Lazy DBAs, I do it directly from Enterprise Mangler or have EM generate a script that I can edit.|||What a scrub...there's my DB2 Roots showing..

Thanks guys

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+TABLE_NAME+'_myDateCol_Default]'
FROM INFORMATION_SCHEMA.TABLES
GO

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP COLUMN [myDateCol]'
FROM INFORMATION_SCHEMA.TABLES
GO

Oh and the original code is wrong

Use Northwind
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime CONSTRAINT '
+'['+TABLE_NAME+'_myDateCol_Default] DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT FROM myCursor99 INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

sheesh...what a scrub....|||Oh dear, Brett...

I guess, on the bright side, your work just got a lot easier.|||Funny thing is, I've never had a need to drop a column....

Anyone?

Usually it's part of massive reengineering effort...