Record Lock on SQL table 
Author Message
 Record Lock on SQL table

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



Mon, 12 Dec 2005 00:52:59 GMT
 Record Lock on SQL table

Quote:

> 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
-------------------------------------------------------------------------



Mon, 12 Dec 2005 01:16:25 GMT
 Record Lock on SQL table
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



Mon, 12 Dec 2005 01:52:26 GMT
 Record Lock on SQL table

Quote:

> 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



Mon, 12 Dec 2005 01:53:34 GMT
 Record Lock on SQL table

Quote:

> 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
-------------------------------------------------------------------------



Mon, 12 Dec 2005 02:20:06 GMT
 Record Lock on SQL table
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



Quote:
> 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




Mon, 12 Dec 2005 02:23:18 GMT
 Record Lock on SQL table
MS SQL Server 7.0 with latest service pack on MS NT4.0 SP 6A.

 Roger Engdahl
   651 438 5931
   612 819 7088



Mon, 12 Dec 2005 04:27:13 GMT
 Record Lock on SQL table

Quote:

> 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



Mon, 12 Dec 2005 04:56:28 GMT
 Record Lock on SQL table

Quote:

> 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



Mon, 12 Dec 2005 21:17:35 GMT
 Record Lock on SQL table
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


Quote:
> 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



Mon, 12 Dec 2005 21:39:48 GMT
 Record Lock on SQL table
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


Quote:
> 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




Mon, 12 Dec 2005 23:09:46 GMT
 Record Lock on SQL table

Quote:

> 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
-------------------------------------------------------------------------



Mon, 12 Dec 2005 23:40:53 GMT
 Record Lock on SQL table

Quote:

> 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



Tue, 13 Dec 2005 01:02:30 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. SQL7 server crawls (locks) when deleting records from a 3 million record table

2. Record Locking - Is there a way to test to see if a record has been locked

3. Access Table locked when no record return from SQL

4. SQL database table / record locking

5. Table is locked, workstation coughs, table stays locked.

6. Visual Basic, T-SQL and MS SQL Server record-locking

7. Locking records in SQL-server, usin SQL in VB

8. Record locking bc of linked tables

9. Locking and unlocking table records...

10. User check if an record from a table is locked by other user ar run time

11. Too many record locks on this table

12. Record Locking/Table Update problems


 
Powered by phpBB® Forum Software