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?

ROWLOCK hint does not seem to have any effect

Hi
Here is the situation.
Many clients simulteneously update the same table, but *never*
the same rows. Each client has its *own* subset of rows.
So there is theoretically no concurrency problem.
Yet we are having locking issues.
We use ROWLOCK hint but it looks like it does not do anything.
Here is my simple test of it.
From one connection, I begin a transaction and update a record in a table
with the ROWLOCK hint.
Then I leave it as is
and
From another connection, I try to update a different record in the same
table also with the ROWLOCK hint.
Second command does not do anything until a transaction in the first
connection is commited.
If it is being locked by row, why is this happenning ?
Thanks
Alex
Hi
If SQL Server has to do a Table or Range Scan to get to data, the 2
operations may step on each other.
Correct indexing, espacially clustered indexes can help a lot.
Post DML and DDL so that we can look at it.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex" wrote:

> Hi
> Here is the situation.
> Many clients simulteneously update the same table, but *never*
> the same rows. Each client has its *own* subset of rows.
> So there is theoretically no concurrency problem.
> Yet we are having locking issues.
> We use ROWLOCK hint but it looks like it does not do anything.
> Here is my simple test of it.
> From one connection, I begin a transaction and update a record in a table
> with the ROWLOCK hint.
> Then I leave it as is
> and
> From another connection, I try to update a different record in the same
> table also with the ROWLOCK hint.
> Second command does not do anything until a transaction in the first
> connection is commited.
> If it is being locked by row, why is this happenning ?
> Thanks
> Alex
>
>
>
sql

Rowcounts don't appear

[reposted from the beta NG]

Hi,
I have a data-flow that, when I run it in the designer on its own, displays rowcounts in the GUI as data flows through the pipeline - as we would expect.

However, if I execute the package from another package (still from the designer), the rowcounts don't show anymore. Everything goes green/yellow/red as normal - just no rowcounts.

This is on IDW14. Haven't yet got IDW15 up and running.

Note that on the NG, Evan Black has confirmed he is seeing the same behaviour.

Regards
JamieHi,
I just want to reiterate the importance of this.

I am currently running a package that has so far been running for 2 hours. It is incredibly annoying that I cannot see how far through the execution it actually is.
I don't know what is going on with my package. All I know is that it is doing something thanks to task manager and the fact that, through Profiler, I can see it executing queries against SQL Server (because it contains a LOOKUP component).

I am trying to see if a change that I have made has made the package quicker or slower than yesterday. If I was able to to see rowcounts in the GUI I would be able to make a manual check of the throughput of the package. As it stands I can't do this and I'm pretty narked about it to say the least!

-Jamie|||With the Feb. CTP, I reported that I seemed to have a choice, when executing one DTS package from another, as to which dtsx package (of target) to target -- because each package has two dtsx files, one in the source directory and one in the bin directory:
#1)
Point the target of the connection at the dtsx package in the bin subdirectory
Advantage: Get coloring and everything when target is executed from caller
Disadvantage: Opens a new dtsx window in IDE, and if you edit it, all changes
will be silently discarded
#2)
Point the target of the connection at the dtsx package in the source directory
Advantage: (avoids disadvantage above, which leads to losing work and cursing)
Disadvantage: no coloring, so you wind up watching a screen where nothing happens for a long while, hoping that good things are really happening

This sounds somewhat related to what you're reporting.
|||Thanks Perry,
I always point at the version that ISN'T in the bin directory because as you say, any changes will be lost. Hence you should never point at the version in the bin directory. It is used by the runtime engine I think and should not be used by the developer.

I'm using the latest CTP. Colouring does occur when a package is executed from a parent, although not as readily as if a package is executed on its own. And we certainly don't get rowcounts.

So yes, this is a related issue. If I were you though I wuld not be touching the file in the bin directory!

-Jamiesql

Row ID

Does SQL maintain it's own Row ID's for records in a table? I was wondering
if there was a row id property that I could query on because the particular
table does not have any columns of type identifier or guid. If there is su
ch a property, what is the
correct syntax to use in the where clause?
Thanks in advanceSQL doesn't expose any physical row identifiers for use in queries. The way
to uniquely identify a row is, of course, by its primary key. If your table
doesn't have a primary key then you should add one.
David Portas
--
Please reply only to the newsgroup
--|||SQL Server does maintain a row id, but in the physical model which cannot be
accessed directly using a t-SQL query.
To access a row in a table using a SQL query you must have an set of column
values in a row which can uniquely identify a row in your table. The correct
approach to do this would be to define a primary key in your table; then
you'll be able to use these key column(s) in your WHERE clause.
- Anith
( Please reply to newsgroups only )|||Good thing Joe Celko doesn't frequent this group
"liz" <anonymous@.discussions.microsoft.com> wrote in message
news:2B33F68F-3091-4162-9A60-60BC2D599260@.microsoft.com...

quote:

---

> Does SQL maintain it's own Row ID's for records in a table? I was

---

wondering if there was a row id property that I could query on because the
particular table does not have any columns of type identifier or guid. If
there is such a property, what is the correct syntax to use in the where
clause?

quote:

---

> Thanks in advance

---

|||"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:#Hzqcz42DHA.536@.tk2msftngp13.phx.gbl...

quote:

---

> Good thing Joe Celko doesn't frequent this group

---

Who?

Row ID

Does SQL maintain it's own Row ID's for records in a table? I was wondering if there was a row id property that I could query on because the particular table does not have any columns of type identifier or guid. If there is such a property, what is the correct syntax to use in the where clause
Thanks in advanceSQL doesn't expose any physical row identifiers for use in queries. The way
to uniquely identify a row is, of course, by its primary key. If your table
doesn't have a primary key then you should add one.
--
David Portas
--
Please reply only to the newsgroup
--|||SQL Server does maintain a row id, but in the physical model which cannot be
accessed directly using a t-SQL query.
To access a row in a table using a SQL query you must have an set of column
values in a row which can uniquely identify a row in your table. The correct
approach to do this would be to define a primary key in your table; then
you'll be able to use these key column(s) in your WHERE clause.
--
- Anith
( Please reply to newsgroups only )|||Good thing Joe Celko doesn't frequent this group :)
"liz" <anonymous@.discussions.microsoft.com> wrote in message
news:2B33F68F-3091-4162-9A60-60BC2D599260@.microsoft.com...
> Does SQL maintain it's own Row ID's for records in a table? I was
wondering if there was a row id property that I could query on because the
particular table does not have any columns of type identifier or guid. If
there is such a property, what is the correct syntax to use in the where
clause?
> Thanks in advance|||"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:#Hzqcz42DHA.536@.tk2msftngp13.phx.gbl...
> Good thing Joe Celko doesn't frequent this group :)
Who?