Hi,

I need to insert a record into a table with conditions and if the conditions are not met roll the transaction back. I am inserting a record into an inventory table which has item numbers and warehouse locations. Each location can hold a quantity of only 1 of an item. The user specifies the location and quantity and presses a save button intiating a transaction that inserts the record. When the user enters the location a validation routine ensures it is not in use at that moment. This works if only one person is adding items into inventory but not when more than 1 user is performing the task because 2 users could select the same inventory location and pass validation if neither had pressed save yet.

Advantage does not support:

INSERT INTO inventory (item, qty, location) 
       VALUES ('ABC', 1, 'LOC1') 
        WHERE 'LOC1' NOT IN (SELECT location FROM Inventory)

as far as I can see.

Can anyone suggest a way to approach this that will work?

I am useing Advantage 10.1, VB .net3.5, DBF/CDX tables with an ADS Data dictionary.

Thanks, Carl

asked 06 Mar '13, 07:11

CarlP's gravatar image

CarlP
1196613
accept rate: 0%

edited 06 Mar '13, 08:15

Mark%20Wilkins's gravatar image

Mark Wilkins
7.2k226133


I think the typical way of handling this is to create a unique (candidate) index on the field. Then the application does not need any kind of special check. The first one to insert a given location wins, and nobody else can insert a record with that location. Even if the syntax specified in the OP was supported, it would not guarantee the desired results. There could be a race condition. Two users could execute the statement concurrently and both determine that the location does not exist, then both insert it. So a unique index is a much cleaner way of handling it.

create index location on inventory (location) candidate;

However, given the information that candidate indexes are not an option, then I think it is necessary to use some kind of external locking mechanism. One way to do this is to use a transaction and obtain a lock on a record in a separate table for the duration of the check for uniqueness and the insert of the new value. The idea would be:

  • begin transaction
  • update a record in a "lock" table. The actual updated value is not critical; it is the idea of holding the lock on the record for the duration that is important. Note that this does not lock the inventory table. It is simply a convention that if everyone doing this operation follows this convention, then it will work. If someone bypasses the convention, it won't work.
  • Check for the unique location value.
  • Insert the record if it is unique
  • Commit the transaction

Suppose the lock table looks like this:

create table mylock(locknum integer, held logical);
insert into mylock values (1, false);

With that, you could use a stored procedure such as the following to do the insert. I am not a stored procedure guru, so it is probably not the prettiest. Note too, that with v11.x, the syntax is quite a bit cleaner with the inputs.

CREATE PROCEDURE AddInventory
   ( 
      newItem CHAR ( 10 ),
      newQty Integer,
      newLocation CHAR ( 10 )
   ) 
BEGIN 
DECLARE locationCount INTEGER;
DECLARE @newItem STRING;
DECLARE @newQty INTEGER;
DECLARE @newLocation STRING;
@newItem = (SELECT newItem from __input );
@newQty = (SELECT newQty from __input );
@newLocation = (SELECT newLocation from __input);
TRY 
  BEGIN transaction;
     /* obtain the synchronization lock (this is the lock-by-convention idea) */
     UPDATE mylock SET held = TRUE WHERE locknum = 1;
     locationCount = (SELECT count(*) FROM inventory WHERE location = @newLocation);
     IF locationCount > 0 THEN
        /* Someone already entered this */
        RAISE LocationExists( 1, @newLocation );
     END IF;

     /* The location doesn't exist, we can add it */
     INSERT INTO inventory (item, qty, location ) VALUES (@newItem, @newQty, @newLocation );

     /* release the lock */
     UPDATE mylock SET held = FALSE WHERE locknum = 1;
  COMMIT WORK;
CATCH ALL
   ROLLBACK WORK;
   RAISE;
END TRY;
END;

Then insert new values with this:

execute procedure AddInventory( 'item', 42, 'someloc' );

If someone is holding the lock on that lock table, it will time out with an error (5035 I think). But with the retry of the lock that is automatic, this should be rare. If the location already exists, then it will fail with the LocationExists exception. Otherwise, it will insert the record and release the lock.

Note that this requires Advantage Database Server because it depends on the transaction holding the lock on the mylock.dbf table. However, this convention could be changed to not require a transaction. For example, you could run a statement such as this prior to the INSERT:

UPDATE mylock set held = TRUE where locknum = 1 AND held = FALSE;

Then check the update count from the update statement. If it updated zero records, then it means you are not the owner of the lock. If the update count is 1, then you own the lock and can proceed with the rest of the logic of checking to see if the location already exists in the inventory table.

link

answered 06 Mar '13, 08:08

Mark%20Wilkins's gravatar image

Mark Wilkins
7.2k226133
accept rate: 26%

edited 06 Mar '13, 13:31

Mark, Thanks for the suggestion. I just wish i could do that as then I would not have to ask this question. I left out an important detail that most, not all, locations only hold one item. There are a few that hold multiple items so I cannot use a unique key. Also both ADS and FoxPro 2.6 DOS access and write to these files. Otherwise your answer is perfect. My fault, I should have been more specific. Carl

(06 Mar '13, 08:15) CarlP

Mark, First I will admit I know nothing about stored procedures. That said, Thanks for the effort to put that together but I don't see how this works. I understand the concept of the mylock table being used as a flag to indicate the inventory table is locked but I don't see where the procedure checks mylock to see if the inventory table is locked. What am I missing?

This approach would work except for one thing, the FoxPro DOS applications can still sneak in and use the location and ADS would be unaware so race conditions could still exist. I think this is unavoidable in any case.

(06 Mar '13, 13:18) CarlP

@CarlP: It doesn't lock the inventory table. It uses a lock by convention. It locks a record in a completely separate table (mylock.dbf) and while that record is locked, anyone else using that same convention (the stored procedure) will not be able to get access to add a new inventory record. It does not, as you note, prevent someone from bypassing the convention. I'll add more notes to try to explain better.

(06 Mar '13, 13:24) Mark Wilkins

(Continued) To be honest, I don't think a perfect solution exists for this because both ADS and FoxPro can make changes.

You provided 2 workable solutions for any "normal" environment but mine is a bit screwy. If I live to convert all of the dozens of Fox, Clipper and Access apps we have to ADS and .net then I can go back and address these issues. Things to look forward too. :-)

My VB.net code updates a number of tables in a transaction so I think I will check the location one final time just before the commit to minimize issue. The warehouse can fix it if needed.

(06 Mar '13, 13:28) CarlP

I am going to mark this as the answer Mark because under any typical circumstance both are good answers. Thanks for the excellant work!! Carl

(06 Mar '13, 13:30) CarlP

Duh, now I see. The update inside the transaction holds a lock until the commit so only one user can run this procedure at a time provided it completes before the second lock request times out. Is that right?Missed that entirely.

(06 Mar '13, 13:56) CarlP

Yes - that is correct. And it would be possible to make it more complex and check for the lock failure in the CATCH block and retry for a number of times if that were desired. However, I think it would take a fairly large number of concurrent requests to actually result in a lock failure (sort of just guessing on that part).

(06 Mar '13, 14:00) Mark Wilkins
showing 5 of 7 show 2 more comments
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×24

Asked: 06 Mar '13, 07:11

Seen: 1,951 times

Last updated: 06 Mar '13, 14:00

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.