Devart Forums

Hi

Sorry to be naïve but I'm new to SDAC and TSQL and I can’t work out how to perform a simple task.

SDAC 4.70
Delphi 2006

I’m developing a multiuser system with over 100 users. Blocks of work are queued and I need to allocate the next unallocated block to a user. It is essential that the same block of work cannot be assigned to two users.

I have a table representing the blocks and the block ID is then used to deliver the actual block of work to the user (the block of work is actually many records gathered from many tables).

There is a field that indicates the user the block is allocated to - say ALLOCATED_USER - NULL before the block's been allocated.

If this were a single user application, I could simply do

TMSquery1.open;
blockID:=query1.fieldbyname('ID').asstring;
TMSquery2.execute;


where TMSquery1 SELECTed the ID of the next block in the queue (the WHERE CLAUSE is actually quite complicated but includes ALLOCATED_USER IS NULL) and where TMSquery2.sql is of the form

UPDATE BLOCKS SET ALLOCATED_USER = WHERE ID =

What I want to achieve is that, once I've selected the record, no-one else would be able to to select that record with TMSquery1.

I assume that means that I would need to lock the record on the SELECT in TMSquery1 and release it after the update has been committed in TMSquery2.

I have looked at the lock and transaction demo files and read what I can in the help and on this forum but can't see how to do this.

This is clearly a similar problem to booking theatre tickets/airline seats so I'm sure it's a well trodden path.

Thank you

Jeff

To lock the record of a table you should execute the TMSQuery.Lock method in exclusive mode, like this: In this case, if other user will call Lock for this record or will change and post it, he will get an exception.
If you don't want any other users even to read this record, you should set the TMSConnection.IsolationLevel property to ilRepeatableRead.
You can look through the example of using the Lock method in the SDACDemo.

Dimon

Thanks.

I'm sorry to ask for more of your time but I've noted what you said and looked through the Lock demo again I'm still not clear.

If this were single user, I was going to code something like

query1.open; // SELECT ...
x:=query1.fieldbyname('ID').asstring;
query2.execute; // UPDATE record selected in query1
query1.close;

My questions are:

1) I'm not sure at what point I would execute the Lock.Would I, for example, execute it in query1's AfterOpen event handler?

2) Who owns the lock? Would query2 be able to update the record?

3) The demo has this happening within a transaction - is this necessary?

To put it another way, would the following work?

query1.open; // SELECT ... with Lock(ltExclusive) in the AfterOpen event handler
x:=query1.fieldbyname('ID').asstring;
query2.execute; // UPDATE record selected in query1.
query1.unlock;
query1.close;

Would this mean that it would be impossible for a second person to open query1 and get the same record?

My understanding of what you've said is that the lock in the event handler would fail if a 2nd person tried to access the record by opening query1 before the unlock was executed by the 1st user, causing the 2nd user to have an exception rather than the select being queued.

Since the query2 update takes the record out of scope for the SELECT in query1, this means that the record can only ever be selected by one person.

4) TMSConnection.IsolationLevel property = ilRepeatableRead.

If I set this, does it mean that if I have

User 1 Query1.open //Select and lock
User 2 Query1.open

that, instead of User 2 getting an exception, the record selected by user 1 would not be in scope for the SELECT and they would get a different record (we're using a where clause something like "where ID = (select MIN(ID) from ..." so if user 1's record is ignored, user 2 will get a different one)?

Best wishes and thanks for your help

Jeff

1. You can execute the Lock method in the AfterOpen event handler or in any other place before calling
query2.execute; // UPDATE record selected in query1

2. The locked record belongs to the transaction in which Lock is executed. Therefore, query2 will be able to update the record if it has the same transaction as query1.

3. To lock a record the query should be in transaction.

4. When IsolationLevel is ilRepeatableRead and one user locks a record then if another user tries to read this record he will get an exception.