As part of changes to a File Server app I've moved all of the utility functions into the main app. This means that on occasions I need exclusive access (eg TABLE OPTIMISE). I also have a small app that just looks after checking for emails. To coordinate things I'm using a slightly modified TAppUserCount - TAppLocks. Things work fine until I close the main app down. Part of that is to drop the TAppLocks table. It works but if there's another user, or the email checker is up and running it takes several seconds to fail on the DROP TABLE statement.

If I can test quickly that someone else is using it things will be fine, otherwise people will start throwing things.

So any ideas?

Roy Lambert

>If I can test quickly that someone else is using it things will be fine, otherwise people will start throwing things.<

All you have to do is learn to duck.

1) Why drop the table? That is expensive because as you found out, it requires exclusivity. Why not have a permanent table with a row for each active user (logged in) and their session info and a generated GUID (so the user knows which record is his)?. When the person logs out, he deletes his own record. See #3 below in case his program crashes and his record remains.

2) You can create a transaction on the table and specify the timeout parameter to be quite short, like 500ms. This will cause the transaction to fail immediately if someone else has it locked.

START TRANSACTION
[ON TABLES <TableName> [,<TableName>]]
[TIMEOUT <Timeout>]
<Timeout> = Milliseconds to wait for lock

If you get the transaction, this will give you a lock on the table to update a "Status" column in the table that gets set to "Dropping" so when you save the record and commit the transaction, another session will know not to grab the table before you have time to drop it. We wouldn't need to do this if we could drop a table inside of a transaction but we can't.

3) Use Configuration information like:

select * from configuration.serversessions;
select * from configuration.serversessionlocks where objectname='MyTable'

to see if there is a lock on the table. You can also use configuration.serversessions with #1 to see if the user is still connected. If not, but his record is still there, then his application crashed and the record can be deleted.

One of those may help unless of course I've totally misunderstood the question in which case you will need to duck and weave to avoid those flying objects.

Barry

Barry


>>If I can test quickly that someone else is using it things will be fine, otherwise people will start throwing things.<
>
>All you have to do is learn to duck.

I would never have thought of that option

>1) Why drop the table? That is expensive because as you found out, it requires exclusivity. Why not have a permanent table with a row for each active user (logged in) and their session info and a generated GUID (so the user knows which record is his)?. When the person logs out, he deletes his own record. See #3 below in case his program crashes and his record remains.

Logged on users, to restrict usage, are tracked by a TAppUserCount component (thanks Terry) this is a workflow protection thingy. Example: if the "background" app is logged on to my ISP and downloading emails I don't want a user trying to do the same. If someone wants to run OPTIMISE TABLE I want everyone off, the background stuff closed etc. That's this bits function. Dropping the table is part of this.

>2) You can create a transaction on the table and specify the timeout parameter to be quite short, like 500ms. This will cause the transaction to fail immediately if someone else has it locked.
>
>START TRANSACTION
>[ON TABLES <TableName> [,<TableName>]]
>[TIMEOUT <Timeout>]
><Timeout> = Milliseconds to wait for lock
>
>If you get the transaction, this will give you a lock on the table to update a "Status" column in the table that gets set to "Dropping" so when you save the record and commit the transaction, another session will know not to grab the table before you have time to drop it. We wouldn't need to do this if we could drop a table inside of a transaction but we can't.

I'll have a look at that one, it might do just what I want.

>3) Use Configuration information like:
>
>select * from configuration.serversessions;
>select * from configuration.serversessionlocks where objectname='MyTable'
>
>to see if there is a lock on the table. You can also use configuration.serversessions with #1 to see if the user is still connected. If not, but his record is still there, then his application crashed and the record can be deleted.

That one would be wonderful but I think you missed the bit where I said fileserver. From memory these are, unfortunately, only abailable for clientserver. However, the transaction idea could well work - I'm off to try it.

Roy