Newbie help with binary fields and locks

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roy Chastain (roy_at_kmsys.com)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 09:41:10 -0400

I have a requirement to find the first on N occurrences that does NOT exist. These are strings in the form of short fixed part
followed by a short variable numeric part such as abc000, abc001 etc. Both the contents and length of the fixed part (abc in this
example) can change from instance to instance and the length of the variable part (000 and 001 in this example) can change.

If I were not doing this in a SQL database, I would set up an array of bits and simply look for the first zero bit. Its index
would give me the numeric value that I would convert to the proper length string and concatenate that to the end of the fixed
portion. Once an index is selected it would be set to 1 to prevent further use until it is later released by setting it back to
zero.

I believe that the idea is still the best method in SQL, but I am not sure exactly the best way to go about it.

My thoughts are that I would create a row for each unique instance (defined as unique by the fixed part and there are only about
10 or so) that would define the fixed portion and the length of the variable portion. This row would include a varbinary column
and a column to indicate the length of the varbinary field (which is really my maximum number of generateable strings).

Here is were I get a little fuzzy. I don't see any type of atomic operations in SQL short of defining a transaction. All of this
SQL access is being done via a C# program.

I am assuming that I can execute a Begin Transaction with the SqlCommand.ExecuteNonQuery method. Here is what I think I need.

create the SqlConnection and SqlCommand class instances
Use ExecuteNonQuery to execute a Begin Transaction
Select the correct row based on the fixed portion
Search the varbinary field for a 0 bit
If no zero bit handle the error of too many items and abort the transaction
Change the 0 to 1
Update the row
Insert new row in to another table with information about this newly created string (other information from other parts of the
program)
Use ExecuteNonQuery to execute a Commit

Now the way that this must work is that only one transaction at a time can update the varbinary field. If this were not SQL, I
would do the equivalent of an Exclusive lock on the in memory array of bits.
Question is do I need to add a lock hint on my select of the row to indicate that I need an exclusive lock? Could someone please
give me the syntax for that?

Thanks
-------------------------------------------
Roy Chastain
KMSystems, Inc.

Relevant Pages

  • Re: Please justify-who is right?
    ... use a different symbol would have broken almost all VS/APL and APL SV ... a tool that would find all slashes outside of strings or comments and either ... SQL code which writes SQL, though -- now that is difficult to read ... the APL "execute" primitive is horrible, ...
    (comp.lang.apl)
  • Re: Any way to get an execution trace out of Access?
    ... If you're doing something like assigning open tasks to users, you may need to run the sql in a transaction with serial isolation. ... The problem appeared to be that the Select subquery would execute "simulataneously" for multiple users, so even if the Update portion were properly controlled, 2 users could be assigned the same task. ... By increasing the transaction isolation level, the Select subquery was forced to be part of the Update transaction. ...
    (microsoft.public.access.formscoding)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.general)
  • Re: Urgent problem: Any help greatly appreciated
    ... Can you combine those store procedures to derive a new one in your SQL ... > I am trying to execute a number of SQL stored procedures in a single ... > transaction. ... > executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", ...
    (microsoft.public.dotnet.languages.csharp)