CFTRANSACTION vs CFLOCK

[Roy Frieband]

Hi,

I have an autoincrement routine for generating unique integer primary keys.
Obviously this need to be done so 2 users can never get the same primary
key. Both CFTRANSACTION and a named CFLOCK seem to work to protect this
process. Is one safer or better than the other? Is there any reason to use
them both, i.e. a named lock inside of a transaction?

--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address

[Roy Frieband]

Follow up to the last question. If using named locks for the increment table
is it better to lock the entire table against all users, or just if they are
requesting a new key for the same table.

Example:
The increment table has this structure:
key integer*
tabletoupdate varchar100
lastusedid integer

Is it better to use a named lock to prevent access to the entire table, or
have seperate named locks for each tabletoupdate field. This way the table
would be locked if 2 users wanted to get an incrementing integer for the
same table but not if they wanted to get one for different tables. I'm
asking all these questions because this is such a critical part of the
application that I want to be sure I am doing it the best way. Thanks again
for any input.

--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address

Post by Roy Frieband
Hi,
I have an autoincrement routine for generating unique integer primary keys.
Obviously this need to be done so 2 users can never get the same primary
key. Both CFTRANSACTION and a named CFLOCK seem to work to protect this
process. Is one safer or better than the other? Is there any reason to use
them both, i.e. a named lock inside of a transaction?
--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address

[Cam Penner]

Post by Roy Frieband
Follow up to the last question. If using named locks for the increment table
is it better to lock the entire table against all users, or just if they are
requesting a new key for the same table.

Definately the second - unless you have nested locks. Then
it becomes fuzzier. If you need to lock for getting table
A, and then B in one thread, and B then A in the other, you
better request and release the locks sequentially, or you
may end up in a deadlock situation. If you grab and hold
lock A in one thread, and B in the other, then both will
hang waiting for the other to finish. Eventually your
whole site will hang on those locks.

But if you're careful to only have one lock requested at a
time, then locking Table indicies (providing they are in
separate rows of the DB) is much better.

Beware of having locks open inside database transactions,
and vice-versa too. Deadlocks are a PITA to track down.
Careful coding will save you many many many many many hours
of debugging.

[Roy Frieband]

Thanks Cam,

Appreciate the input.

--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address

"Cam Penner"

Post by Cam Penner

Post by Roy Frieband
Follow up to the last question. If using named locks for the increment table
is it better to lock the entire table against all users, or just if they are
requesting a new key for the same table.

Definately the second - unless you have nested locks. Then
it becomes fuzzier. If you need to lock for getting table
A, and then B in one thread, and B then A in the other, you
better request and release the locks sequentially, or you
may end up in a deadlock situation. If you grab and hold
lock A in one thread, and B in the other, then both will
hang waiting for the other to finish. Eventually your
whole site will hang on those locks.
But if you're careful to only have one lock requested at a
time, then locking Table indicies (providing they are in
separate rows of the DB) is much better.
Beware of having locks open inside database transactions,
and vice-versa too. Deadlocks are a PITA to track down.
Careful coding will save you many many many many many hours
of debugging.
--
Cam

[Whosyer]

Roy -

As an aside - the easiest way to ensure getting a unique key and grabbing it
for use in subsequent queries (if you are using SQL Server only as far as I
am aware) is to set IDENTITY on for your table primary key in the db and
then, in the first INSERT query use:

<CFQUERY NAME="qMyQuery"....>
SET NOCOUNT ON
INSERT INTO......
(..)
VALUES
(..)
SELECT NEWID = @@IDENTITY
SET NOCOUNT OFF
</CFQUERY>

Returns this unique ID as #qMyQuery.NEWID#

Locking and so-on still a crucial issue but largely unnecessary just for
retrieving IDs - where transaction is essential however is for enabling
rollback in the event of a drama.

cheers

Jon

Post by Roy Frieband
Thanks Cam,
Appreciate the input.
--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address
"Cam Penner"

Post by Cam Penner

Post by Roy Frieband
Follow up to the last question. If using named locks for the increment

table

Post by Cam Penner

Post by Roy Frieband
is it better to lock the entire table against all users, or just if

they

Post by Roy Frieband
are

Post by Cam Penner

Post by Roy Frieband
requesting a new key for the same table.

Definately the second - unless you have nested locks. Then
it becomes fuzzier. If you need to lock for getting table
A, and then B in one thread, and B then A in the other, you
better request and release the locks sequentially, or you
may end up in a deadlock situation. If you grab and hold
lock A in one thread, and B in the other, then both will
hang waiting for the other to finish. Eventually your
whole site will hang on those locks.
But if you're careful to only have one lock requested at a
time, then locking Table indicies (providing they are in
separate rows of the DB) is much better.
Beware of having locks open inside database transactions,
and vice-versa too. Deadlocks are a PITA to track down.
Careful coding will save you many many many many many hours
of debugging.
--
Cam

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004

[Roy Frieband]

Hi Jon,

Post by Whosyer
the easiest way to ensure getting a unique key
and grabbing it for use in subsequent queries (if
you are using SQL Server only as far as I
am aware) is to set IDENTITY on for your table

I am currently using SQL server, and would surely use IDENTITY method if I
could. However this app has to be database independent so I can't use stored
procedures, triggers, or any other database dependent methods. Therefore I
need to get the primary keys in a way that would work for any database. I do
appreciate the input however and will use transactions for rollback as you
mention.

--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address

Post by Whosyer
Roy -
As an aside - the easiest way to ensure getting a unique key and grabbing it
for use in subsequent queries (if you are using SQL Server only as far as I
am aware) is to set IDENTITY on for your table primary key in the db and
<CFQUERY NAME="qMyQuery"....>
SET NOCOUNT ON
INSERT INTO......
(..)
VALUES
(..)
SET NOCOUNT OFF
</CFQUERY>
Returns this unique ID as #qMyQuery.NEWID#
Locking and so-on still a crucial issue but largely unnecessary just for
retrieving IDs - where transaction is essential however is for enabling
rollback in the event of a drama.
cheers
Jon

Post by Roy Frieband
Thanks Cam,
Appreciate the input.
--
Regards,
Roy F.
To Reply by email remove NOMORESPAM from the email address
"Cam Penner"

in

Post by Roy Frieband

Post by Cam Penner

Post by Roy Frieband
Follow up to the last question. If using named locks for the increment

table

Post by Cam Penner

Post by Roy Frieband
is it better to lock the entire table against all users, or just if

they

Post by Roy Frieband
are

Post by Cam Penner

Post by Roy Frieband
requesting a new key for the same table.

Definately the second - unless you have nested locks. Then
it becomes fuzzier. If you need to lock for getting table
A, and then B in one thread, and B then A in the other, you
better request and release the locks sequentially, or you
may end up in a deadlock situation. If you grab and hold
lock A in one thread, and B in the other, then both will
hang waiting for the other to finish. Eventually your
whole site will hang on those locks.
But if you're careful to only have one lock requested at a
time, then locking Table indicies (providing they are in
separate rows of the DB) is much better.
Beware of having locks open inside database transactions,
and vice-versa too. Deadlocks are a PITA to track down.
Careful coding will save you many many many many many hours
of debugging.
--
Cam

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004