Returning a 'uniquely unique' set of rows.

A. User (a.user@no.where.com) writes:
> I'm using SQL Server 2005 and I want to write a stored procedure or UDF
> that will return x number of rows to the calling client (an application
> written in C++ and where x is passed as a parameter.)
>
> However, having selected these rows I don't want _any_ other user to get
> them, and I'm not able to delete them until the requesting client has
> finished using them, but even then I simply want to mark their state as
> 'complete'.
>
> Obviously I need some sort of state flag within the procedure, but what
> about concurrent access issues. If anyone can help me out with this
> please I'd be very grateful.

So you need to track which rows you have returned. Either add a column
to mark the rows reserved, or add a seprate table. You will also need to
determine that a reservation is stale. That is, the client may crash and
never complete its work.

If there can be concurrent calls to the procedure, you need make sure that
the same set of rows are not returned to two clients. A simple way to
achieve this is to use an application lock to serliase all access to the
stored procedure.

If you need high concurrency, a combination of the UPDLOCK and READPAST
hints could work.

But since this table apparently is some sort of a queue, maybe you look
into Service Broker, and see if you should redesign your table into a
Service Broker queue.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

--CELKO-- (jcelko212@earthlink.net) writes:
>>> I'm using SQL Server 2005 and I want to write a stored procedure or UDF
that will return x number of rows to the calling client (an application
written in C++ and where x is passed as a parameter.) <<
>
> In SQL, we don't care about the host program language at all.

Incorrect. Maybe you don't. But we who write real-world application do.



--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

--CELKO-- wrote:
>>> I'm using SQL Server 2005 and I want to write a stored procedure
>>> or UDF that will return x number of rows to the calling client
>>> (an application written in C++ and where x is passed as a
>>> parameter.) <<
>
> In SQL, we don't care about the host program language at all.

Well I certainly do, and I think others reading this may also be.

> We also prefer a **predicate** to describe a subset of rows rather
> than a **count**

Who's we?

> What you are describing is like a paper file system
> where you hand a clerk (n) number of paper files to work on and
> return.

Your point being?

>>> However, having selected these rows I don't want _any_ other user
>>> to get them, and I'm not able to delete them until the
>>> requesting client has finished using them, but even then I
>>> simply want to mark their status as 'complete'. <<
>
> Yep, like a paper file system! Do you want to update them or just
> delete them? Do you want to hide them from other users?

If you read the message all of these questions are answered in the
original post.

>>> Obviously I need some sort of state flag within the procedure,
>>> but what about concurrent access issues. <<
>
> No, there are other models of concurrency without locking.
> Optimistic concurrency was originally based on manual microfilm
> systems in which multiple users could get a copy of the same
> document. You made xerox copies off of the microfilm, and passed out
> copies; clerks changed or added to the document; Central Records then
> had rules to decide what changes applied and which ones went to
> conflict resolution.
>
> You might want to learn about this option.

What option? Your statement makes no reference to any explicit option,
it only hints at other models.

With all due respect if you're trying to impress me with your knowledge
of xerox history I'm not impressed nor interested, only saddened at the
waste of bandwidth, time and effort.

--
A.
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

On Mar 24, 4:27 am, "A. User" wrote:
> Hi,
>
> I'm using SQL Server 2005 and I want to write a stored procedure or UDF
> that will return x number of rows to the calling client (an application
> written in C++ and where x is passed as a parameter.)
>
> However, having selected these rows I don't want _any_ other user to get
> them, and I'm not able to delete them until the requesting client has
> finished using them, but even then I simply want to mark their state as
> 'complete'.
>
> Obviously I need some sort of state flag within the procedure, but what
> about concurrent access issues. If anyone can help me out with this
> please I'd be very grateful.

One simple solution would be to have your stored procedure update x
number of rows with a unique indicator (maybe the date/time the
procedure was called, plus something to identify the client) and then
return all the rows that have that indicator. It would of course have
to filter out any rows that already have an indicator from a previous
run.

This would also assist in identifying the rows that can be marked
"complete" once the client is finished.
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

>> [ don't care about the host program language] Incorrect. Maybe you don't. But we who write real-world application do. <<

Well so much for the concept of a tiered architecture and the last 30
years of software engineering. If you are going to tie the database
to one particular host language, please tell me you picked COBOL from
the available options, since it is still the most popular one in
commercial use.

The application programmers care about his language and what it can
do, but our job on the RDBMS side is to assure him that the data we
"throw over the wall" is valid and in at least 1NF. It is the job of
the interface (ODBC, SQL/CLI, IDAPI, JDBC, et al) to convert it into
the host language data types. It is not your job when we wear the DB
hat.
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

>> Who's we? <<

People who wrote the SQL Standards and/or have over 25 years with
RDBMS

My point about the paper file system model is that you do not have
enough decades in IT to recognize what you are mimicking. You don't
understand why RDBMS replaced file system or why file systems repalced
paper system.

>> If you read the message all of these questions are answered in the original post. <<

Let's go back to the original spec and look at it word for word.

>> However, having selected these rows I don't want _any_ other user to get them, <<

SELECT is a concept in RDBMS so it is loaded with lots of meaning. It
does not imply any kind of change to the data nor any locking. You
also never said how each user does this SELECT, or how to handle
overlaps.

>> ... and I'm not able to delete them until the requesting client has finished using them, but even then I simply want to mark their state as 'complete'. <<

How do these other users use these rows? What action makes the status
change to "complete" and what does that have to do with deletion? If
you had read Codd, you might remember the Information Principle; all
information is modeled as scalar values in columns in rows of tables.
Ergo, your user needs to update the "<something>_status" values or the
system has to do this based on some rules (i.e. predicates).

I don't think you understand that a status is a state of being over
time, so you confuse the two.

>> Obviously I need some sort of state flag [sic: needs to be a "<something>_status" data element; flags are binary and belong in assembly language not SQL] within the procedure, but what about concurrent access issues. <<

This is why I brought up optimistic currency and tried to explain it
to you, since you do not have a background in RDBMS. It let multiple
users share the DB without the overhead of locks. I cannot post that
much education in a newsgroup, but Google it.

>> What option? Your statement makes no reference to any explicit option, it only hints at other models. <<

Well, I looked at your DDL and vague specs and did the best I could.
Oh, you never posted DDL or clear specs.

If these subsets were picked with a predicate, then we might be able
to use updatable VIEWs that have a WITH CHECK OPTION to guarantee that
each user has a disjoint subset.

>> With all due respect if you're trying to impress me with your knowledge of Xerox history I'm not impressed nor interested, only saddened at the waste of bandwidth, time and effort. <<

A professional would want to learn about his culture and the history
of the technology.
"Against stupidity the gods themselves struggle in vain." - Die
Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Would you like to post better specs and perhaps some DDL, so we can
come up with code? Would you actually learn how to do things right
and not just be a cowboy coder?
 >> Stay informed about: Returning a 'uniquely unique' set of rows. 

A. User wrote:
> HumanJHawkins wrote:
>> On Mar 25, 3:14 pm, "A. User" wrote:

<snipped>

> Top most SQL Experts you say well that he /she may well be, but
> professional they are not. It would appear to readers of the group that
> he / she takes every given opportunity to criticise other peoples'
> posting, rather than send a follow up message with the likes of "Sorry
> but I can't understand the exact meaning of your request, can you
> provide me (us) with more information, so that we may assist you better."
>

Are you looking for something like a "work-in-progress" model. User
takes "call" from queue - works the issue - closes the issue?? This can
be achieved in many ways, none of which are easy to manage based on the
limited description provided and more than likely will add a significant
number of changes to your current logical model to be successful.

It appears that the user already knows the value of 'x' before he/she
starts looking? How did they find it? Can someone else already have it?
Do they have the option to NOT work on the "piece of paper" as
described by Celko? There are a lot of things your OP does not consider
for the "problem" at hand.

To echo some of Celko's sentiments, if you are "just a programmer", then
you will need a real DBA to assist with the data model changes to keep
you from hurting yourself in the future.

I know a lot of companies are using this RAD paradigm for creating
applications, but most of them also perform like a bunch of monkeys
running around in circles - doing lots of stuff but not getting anywhere...
 >> Stay informed about: Returning a 'uniquely unique' set of rows.