Record Lock on SQL table

Record Lock on SQL table

Post by Roger Engdah » Fri, 27 Jun 2003 01:52:59



If I access an SQL table (one record, one longint field) from Paradox via an
OBDC connection with a tcursor
and if I can lock the record I grab the value, increment it by one and then
post and unlock the record.

While I have the record locked (tc.lockrecord() returns true) is there any
sort of SQL or other mechanism that prevents any other non-Paradox app from
accessing the table and grabbing and incrementing at the same time?

These are order numbers. Twice in 4 months (125,000 orders) we have seen
duplicate order numbers retrieved by the Paradox app and by another
non-Paradox app getting order numbers from the same source.

--
Roger Engdahl
  651 438 5931
  612 819 7088

 
 
 

Record Lock on SQL table

Post by Steven Gree » Fri, 27 Jun 2003 02:16:25



> While I have the record locked (tc.lockrecord() returns true) is there any
> sort of SQL or other mechanism that prevents any other non-Paradox app from
> accessing the table and grabbing and incrementing at the same time?

that "lock" is only respected within the scope of other BDE apps..

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------

 
 
 

Record Lock on SQL table

Post by Larry DiGiovann » Fri, 27 Jun 2003 02:52:26


Roger:

IMHO, you are better off writing a procedure or function on your database
server and calling that.  If you specify your platform you might get more
detailed advice.

In any event you need to explicitly begin a transaction for your lock to
be honored.  That'll insure the consistency of your updates, and block
other updates until the end of your transaction.  Untested code below,
check the ObjectPAL reference for BeginTransaction for more details.

db.open(alias)
if not db.begintransaction() then
  ; you have no transaction, hence no lock
  ; react accordingly
end if
tc.open(yourtable)
tc.edit()
tc.lockrecord()
; do your thing
tc.unlockrecord()
db.committransaction()
;etc

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

 
 
 

Record Lock on SQL table

Post by Larry DiGiovann » Fri, 27 Jun 2003 02:53:34



> that "lock" is only respected within the scope of other BDE apps..

Not if you do it right, per my reply to Roger.  But part of me doesn't
even trust that.  I'd use a stored procedure or function (depending on the
platform).

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

 
 
 

Record Lock on SQL table

Post by Steven Gree » Fri, 27 Jun 2003 03:20:06



> But part of me doesn't even trust that.  I'd use a stored procedure or
> function

in theory, none of me trusts it <g>..

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------

 
 
 

Record Lock on SQL table

Post by Mike Irwin [CTech » Fri, 27 Jun 2003 03:23:18


Roger,

firstly, take a look at the FAQ on this stuff - it's called
"TIP:PdoxWin:Incrementing Primary Keys:2001.05.26" on the
Paradox-faqs newsgroup and on the Paradox Community site, and
"Incrementing Values (Keys)" in the FAQs on my site
(http://mirwin.homestead.com). Either way, you should get a
download of a set of files to illustrate how to achieve things
with Paradox tables.

"But I'm using SQL" you protest. Well, a bit of explanation
never hurt ! Anyhow, you haven't specified which server you're
using.

Let's assume that you're using Microsoft SQL Server, which has
Transact-SQL for a language. When you are using MS Sql Server
you should, really, be using an autoincrement field within the
SQL Server table.

Alternatively, it's fairly simple to write a stored procedure on
MS SQL Server to increment a value in a table and return the
result to you.

For example, let's say that you have several tables, each of
which has an Integer (32 bit) key. You'll want to provide the SP
with the table name and receive back the new value ...



as

        from CoordInf


        update CoordInf


and execute the SP as needed. So long as you always use the SP
to add new records you shouldn't have any problems.

Finally, you can add a trigger to your SQL Server table, that
fires after insertion of a new record, and which does the same
thing as the code above, in that it goes and gets a value and
uses that. In this way an "auto" field in SQL Server is like a
postAction trigger.

I don't have an instance of SQL Server here in front of me to
try things out on, so I'll check it tonight and post again then

hth

Mike



> If I access an SQL table (one record, one longint field) from Paradox via an
> OBDC connection with a tcursor
> and if I can lock the record I grab the value, increment it by one and then
> post and unlock the record.

> While I have the record locked (tc.lockrecord() returns true) is there any
> sort of SQL or other mechanism that prevents any other non-Paradox app from
> accessing the table and grabbing and incrementing at the same time?

> These are order numbers. Twice in 4 months (125,000 orders) we have seen
> duplicate order numbers retrieved by the Paradox app and by another
> non-Paradox app getting order numbers from the same source.

> --
> Roger Engdahl
>   651 438 5931
>   612 819 7088


 
 
 

Record Lock on SQL table

Post by Roger Engdah » Fri, 27 Jun 2003 05:27:13


MS SQL Server 7.0 with latest service pack on MS NT4.0 SP 6A.

 Roger Engdahl
   651 438 5931
   612 819 7088

 
 
 

Record Lock on SQL table

Post by Sundial Service » Fri, 27 Jun 2003 05:56:28



> If I access an SQL table (one record, one longint field) from Paradox via
> an OBDC connection with a tcursor
> and if I can lock the record I grab the value, increment it by one and
> then post and unlock the record.

You fundamentally cannot rely on "record locks" in an SQL database like you
can with a native Paradox table.  But you can come pretty darned close with
something like this:

        repeat
          SELECT next_number FROM next_number_table .. into "n"
          UPDATE next_number_table SET next_number = {n+1}
            WHERE next_number = {n}
        until the update works

Mind you, what I've written above is pseudocode .. not real code .. just to
describe the technique.

If your SQL engine provides a way to coin a new number, use it.

----------------------------------
Fast automatic table repair at a click of a mouse!
http://www.sundialservices.com/products/chimneysweep

 
 
 

Record Lock on SQL table

Post by Larry DiGiovann » Fri, 27 Jun 2003 22:17:35



> You fundamentally cannot rely on "record locks" in an SQL database like
> you can with a native Paradox table.

What do you mean by that?  You may not always be able to rely on OPal
lockRecord() to actually lock the record, but you can explicitly lock
records on most client/server databases I've dealt with.  For sure you can
with SQL Server 7 as Mike and I showed.

Quote:> But you can come pretty darned close with
> something like this:

>         repeat
>           SELECT next_number FROM next_number_table .. into "n"
>           UPDATE next_number_table SET next_number = {n+1}
>             WHERE next_number = {n}
>         until the update works

If you modified the above code to explicitly begin a transaction with the
proper isolation level, and specify the locking hint UPDLOCK, the above
would work.

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

 
 
 

Record Lock on SQL table

Post by Mike Irwin [CTech » Fri, 27 Jun 2003 22:39:48


As you probably saw, while the code was conceptually correct, it
doesn't work ! I tested one out and posted it at

http://mirwin.homestead.com/files/tips/SQLSnippets.html

in section 4.

hth

Mike


> Roger,

> firstly, take a look at the FAQ on this stuff - it's called
> "TIP:PdoxWin:Incrementing Primary Keys:2001.05.26" on the
> Paradox-faqs newsgroup and on the Paradox Community site, and
> "Incrementing Values (Keys)" in the FAQs on my site
> (http://mirwin.homestead.com). Either way, you should get a
> download of a set of files to illustrate how to achieve things
> with Paradox tables.

> "But I'm using SQL" you protest. Well, a bit of explanation
> never hurt ! Anyhow, you haven't specified which server you're
> using.

> Let's assume that you're using Microsoft SQL Server, which has
> Transact-SQL for a language. When you are using MS Sql Server
> you should, really, be using an autoincrement field within the
> SQL Server table.

> Alternatively, it's fairly simple to write a stored procedure on
> MS SQL Server to increment a value in a table and return the
> result to you.

> For example, let's say that you have several tables, each of
> which has an Integer (32 bit) key. You'll want to provide the SP
> with the table name and receive back the new value ...



> as

>    from CoordInf


>    update CoordInf


> and execute the SP as needed. So long as you always use the SP
> to add new records you shouldn't have any problems.

> Finally, you can add a trigger to your SQL Server table, that
> fires after insertion of a new record, and which does the same
> thing as the code above, in that it goes and gets a value and
> uses that. In this way an "auto" field in SQL Server is like a
> postAction trigger.

> I don't have an instance of SQL Server here in front of me to
> try things out on, so I'll check it tonight and post again then

 
 
 

Record Lock on SQL table

Post by marc » Sat, 28 Jun 2003 00:09:46


Lets see from another point of view:
about 6 new record/minute, if you have got a pentium (100 or 1000 *
1'000'000 clocks/sec), a 100BaseT network and you post the record immediatly
the chances to have twice the same number is very low -> maybe your record
sleeps somewhere before being available or the app. watch an old dataset.
bye
marco


> If I access an SQL table (one record, one longint field) from Paradox via
an
> OBDC connection with a tcursor
> and if I can lock the record I grab the value, increment it by one and
then
> post and unlock the record.

> While I have the record locked (tc.lockrecord() returns true) is there any
> sort of SQL or other mechanism that prevents any other non-Paradox app
from
> accessing the table and grabbing and incrementing at the same time?

> These are order numbers. Twice in 4 months (125,000 orders) we have seen
> duplicate order numbers retrieved by the Paradox app and by another
> non-Paradox app getting order numbers from the same source.

> --
> Roger Engdahl
>   651 438 5931
>   612 819 7088


 
 
 

Record Lock on SQL table

Post by Steven Gree » Sat, 28 Jun 2003 00:40:53



> the chances to have twice the same number is very low

that's not the same as "it's not possible".. you should never design anything on
that basis.. it will always bite you, eventually <g>

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------

 
 
 

Record Lock on SQL table

Post by Larry DiGiovann » Sat, 28 Jun 2003 02:02:30



> http://mirwin.homestead.com/files/tips/SQLSnippets.html

Your code jumps through some hoops to get the temp table, populate, then
have Paradox go back and get the value from another SELECT.

Why not have the stored procedure return the value directly to the
tCursor?

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources