I'd step past the idea of locking entirely, and build a versioning
architecture.

You'd have a table containing a version id, a timestamp, and then the file
id/name of the file.

That way, nobody blocks anyone else's edits, you can always retrieve a
previous version, and no-one loses changes.


CREATE TABLE tbl_file (
int_fileid INTEGER NOT NULL, /* The unique file identifier */
str_filename VARCHAR(256), /* If you need to store the filename here
as well */
str_desc VARCHAR(2048), /* If you would like a description of the file
*/
blb_notes BLOB SUB-TYPE TEXT,
PRIMARY KEY(int_fileid)
);

CREATE TABLE tbl_fileversion (
int_versionid INTEGER NOT NULL, /* The Version Identifier */
dtm_stamp TIMESTAMP default 'now' NOT NULL /* When this version was
submitted */
int_fileid INTEGER NOT NULL, /* The ID of the file stored in the
repository */
str_note VARCHAR(512), /* Commit notes - something about this version
*/
PRIMARY KEY(int_versionid, int_fileid),
FOREIGN KEY(int_fileid) REFERENCES tbl_file (int_fileid)
);

When a file is registered, you create a record in tbl_file, and a
corresponding version record in tbl_fileversion.
With each alteration, you simply add another record into tbl_fileversion for
the file, and write the new file to disk.

I'd write the filenames on the server like this:
[fileid]_[versionid]_[filename]
or, in a nice directory structure like:
/substr(-2,2,[fileid])/[fileid]_[versionid]_[filename]



So an example of a file changed three times (Over a perios of time, with
changes to other files in between) would be
100_34_Budget Plans for FinYear07-08.doc
100_371_Budget Plans for FinYear07-08.doc
100_499_Budget Plans for FinYear07-08.doc

or in the nice directory structure:
/00/100_499_Budget Plans for FinYear07-08.doc

Tada! Non-blocking, multi-user, multi-versioning, and no-one ever loses
their work.

Nige.


Nigel Weeks
Tech Support and Systems Developer
Rural Press Tasmania
The Examiner Newspaper
Ph. 03 6336 7234
Mob. 0408 133 738
Email. <mailto:nweeks@...> nweeks@...

"You may now be on
the other side of the fence,
but the grass is just as likely
to die there too." - me



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Monday, 16 July 2007 8:25 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: How do I "lock tables" in FireBird SQL?



Another way that is can be done.

There are strict rules in Firebird regarding which transactions are
allowed to modify a record, based on whether the last modification to
that record was committed at the time your record started (snapshot
transactions) or whether it is committed at the time you try an update
(read committed).

This means that if you run an update query on a record and it doesn't
throw a lock conflict exception, no-one else has. Now in theory you
could just leave that transaction uncommitted until you finish the
document, but that is a bad design. If the user goes home without
closing the document properly, you end up with a long running
transaction that inhibits garbage collection.

So you need to allow for four possibilities
* Another user has the record locked in a committed transaction.
* Another user has the record locked in an uncommitted transaction.
* You already have the record locked.
* No-one has the record locked

You could then write a stored procedure to 'lock if possible' or to
return you the name of the user who it thinks does.

SET TERM ^ ;
CREATE OR ALTER PROCEDURE LOCKDOCUMENT
(
USERID INTEGER,
DOCUMENTID INTEGER
)
RETURNS
(
LOCKAQUIRED CHAR(1),
CONFLICTSWITHUSERID INTEGER
)
AS
BEGIN
CONFLICTSWITHUSERID = NULL;
LOCKAQUIRED = 'F';

BEGIN
UPDATE DOCUMENTS SET
CURRENTUSERID = :USERID
WHERE ID = :DOCUMENTID
AND (CURRENTUSERID IS NULL OR CURRENTUSERID = :USERID);

IF (ROWCOUNT > 0) THEN
BEGIN
-- A record was 'modified' by the update
LOCKAQUIRED = 'T';
END
ELSE
BEGIN
-- Find out who modified it
SELECT CURRENTUSERID
FROM DOCUMENTS
WHERE ID = :DOCUMENTID
INTO :CONFLICTSWITHUSERID;
END

WHEN ANY DO
BEGIN
-- Don't use ANY, lookup the code (I am just lazy).
END
END

-- By this stage, we know if we have aquired the lock or not.
-- In some situations, we know who has the lock if we can't
-- aquire it, but there are exceptions that can occur if you
-- have multiple users attempting to lock the same document at
-- the same time.

END
^

Start a transaction, run this procedure and commit immediately. If you
always follow that procedure, even when it can't return you the user,
retrying a few moments later will succeed (in telling you who has the
lock aquired anyway).

You also need an unlock document procedure that sets the CURRENTUSERID
to null if it = your USERID.

As Lester points out, it is possible that a user may have a power
failure or something that causes the lock to not be removed (this is a
consequence of your locking design). With the above procedure, this
can be fixed by having the user who the database thinks has the lock
then take out the lock again and unlock it properly. (The stored
procedure accommodates the fact you may already have the lock).

Adam





__________ NOD32 2394 (20070711) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com




[Non-text portions of this message have been removed]