SP locking problem (probably easy!) 
Author Message
 SP locking problem (probably easy!)

Hi all,

I'm using Sybase ASE11.5 on Solaris 2.6 to implement a small phonecard
database. It's small in that there are only a few tables, but will
contain large numbers of phonecard records (millions eventually), and
will be heavily accessed by various devices. However the same
principles should apply to my small test sample as to the final large
datasets. I need to ensure that a phonecard is never charged more than
the number of units it contains, but that all valid charges are
applied, and decided to use a stored procedure for this. In the course
of my testing, I've been able to double charge cards by running
several concurrent processes all updating the same row, and can't see
how to lock the record out in order to prevent this. I've investigated
the transaction modes, isolation levels, holdlock etc, to no avail.
Any clues would be great .. I've attached my current attempt.

Thanks,

David


as


set transaction isolation level 3

begin transaction



    begin
        rollback transaction
        return -2
    end


    begin
        rollback transaction
        return -1
    end



    begin
        rollback transaction
        return -3
    end



commit transaction




Mon, 28 Aug 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:

> I'm using Sybase ASE11.5 on Solaris 2.6 to implement a small phonecard
> database. It's small in that there are only a few tables, but will
> contain large numbers of phonecard records (millions eventually), and
> will be heavily accessed by various devices. However the same
> principles should apply to my small test sample as to the final large
> datasets. I need to ensure that a phonecard is never charged more than
> the number of units it contains, but that all valid charges are
> applied, and decided to use a stored procedure for this. In the course
> of my testing, I've been able to double charge cards by running
> several concurrent processes all updating the same row, and can't see
> how to lock the record out in order to prevent this. I've investigated
> the transaction modes, isolation levels, holdlock etc, to no avail.
> Any clues would be great .. I've attached my current attempt.

        You're trying too hard. I've attached a potential solution
        below with some free design advice as well. Given the final
        purpose of your database I'd also suggest looking at table
        design to minimise contention on this potentially large
        table, e.g. partitioning and index design.

        Avoid using names beginning with "sp_" for your sprocs so
        that you don't confuse them with the Sybase system sprocs.
        Put your sproc args on the following lines (I prefer one
        per line for readability and maintenance purposes).
        Having card_number defined as char(16) is undesirable
        from an indexing/performance perspective. You can't
        change this to some kind of numeric value?

Quote:
> as


> set transaction isolation level 3

> begin transaction



>     begin
>         rollback transaction
>         return -2
>     end


>     begin
>         rollback transaction
>         return -1
>     end



>     begin
>         rollback transaction
>         return -3
>     end



> commit transaction



        Try making it just one statement -

        begin transaction

        update  card


        and     current_units  != null

        commit transaction

        return 0

        You can dispense with the explicit transaction wrapper
        if you don't need to specify a particular return value,
        otherwise you can add some more statements to check if
        the update succeeded and return a different value. Let
        Sybase handle the returning of error conditions for you.
        Your negative return values are wrong as well. Sybase
        uses these values for error conditions like missing
        objects and datatype errors. You'll have to use positive
        ones. Check volume 1 of the Commands Reference Manual
        under "Create Procedure" for details.

        Note, however, that this doesn't prevent serveral updates
        from happening to your data. This is a design issue. I.e.
        why are several processes updating the same row?

-am



Tue, 29 Aug 2000 03:00:00 GMT
 SP locking problem (probably easy!)

[SNIP]

Quote:

>         Note, however, that this doesn't prevent serveral updates
>         from happening to your data. This is a design issue. I.e.
>         why are several processes updating the same row?

Funnily enough, in many applications, multiple users/processes need to
access the same row.  I am currently trying to get Sybase ASE 11.5 to do
something similar, but using ODBC.  (Oh, I have it working for DB2, Oracle
and MS-SQL Server 6.5).

Example:
        table 1:
                contains a single record, the data is the "last" order
                "number" , note that it may not be a pure number, it may
                contain text/date/time info, as in 1998C131326OSB5R7 ==>
                1998 March 12 13:26 Overseas Bin 5 Representative 7.

        table 2:
                an audit trail of the changes to table 1.

        table 3:
                contains fields that are used to generate "record numbers"
                for entries in table 2 (among others).  (Don't shoot me,
                I'm only the piano player (and yes, I know about identity
                columns, but my client wants to be anonymous)).

        Now, this is _just_ an example, I know that it can (possibly) be
        written in straight SQL, but I need to be able to do it in ODBC,
        and, to reiterate, it is _just_an example.
        I am using the concept "LOCK x" to indicate that I want all
        accesses until I issue an "UNLOCK" to lock out _all_ other users
        from the data I will be selecting.  Consider LOCK to be like
        "begin tran" and UNLOCK to be "commit tran".

                while (processing) do
                        // start transaction 1
                        lock table 1
                        select t1.data from row 1
                        do stuff to t1.data
                        update t1.data to row 1
                        unlock table 1
                        // end transaction 1

                        // start transaction 2
                        lock table 3
                        select record number from row indexed as "table 2"
                        increment record number
                        update record number to row indexed as "table 2"
                        generate t2.data from t1.data and record number
                        insert t2.data to table 2
                        unlock table 3
                        // end transaction 2
                end
        Now, if I have n users running this process, _single_ rows in
        both table 1 and table 3 are going to be updated frequently,
        essentially for any successfully completed overall transaction.
        This sort of thing is quite common in my experience, and while
        the need to create a "record number" is debatable, the use of a
        single overall "next" code is quite ubiquitous.

I have 'C' code using ODBC that shows this happily _not_ working on ASE
11.5, and Sybase in Australia have been asked for help, but if anybody
reading this would like to to try it out, contact me directly and I will
happily mail it to you.

        Cheers,
                Gary    B-)

--
---------------------------------------------------------------------------


                        POWERflex Corporation   Developers of PFXplus
                        Vox:  +61 3 9888 5833   Fax:  +61 3 9888 5451

                        Visit our Web Site:           www.pfxcorp.com

---------------------------------------------------------------------------



Tue, 29 Aug 2000 03:00:00 GMT
 SP locking problem (probably easy!)

We had a similiar problem.  Its due to the fact that the select
statement does not lock the row (Although the holdlock should...)

We solved it by adding a statment above the select (but within the
transaction)  that is essentially a noop, but updates the row:

.
.
begin transaction

   update card set current_units = current_units

.
.

We always picked a non-indexed column to update so that so index
pages were updated (since this is not a real update anyway).

Dennis

Quote:

>Hi all,

>I'm using Sybase ASE11.5 on Solaris 2.6 to implement a small phonecard
>database. It's small in that there are only a few tables, but will

.
.
Quote:


>as


>set transaction isolation level 3

>begin transaction



    .
    .
    .
Quote:
>commit transaction





Thu, 31 Aug 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:

[SNIP]
>         Yes but multiple accesses doesn't imply multiple updates. Several
>         readers and one writer I can understand but not vice versa.

N-readers and 1-writer make life easier, but if you are not in a batch
processing environment, n-readers and n-writers is far more prevalent.

[SNIP]

Quote:
>         Your problem starts in transaction 2. The way you should be
>         doing it is -

>         begin transaction

>         update table 3
>         set value = value + 1

>         select value
>         from table 3

>         update table 2 etc.

>         commit transaction

>         The update does the lock correctly for you. And since your
>         select is within the scope of a transaction, you get your
>         recently updated value. You could put your table 2 actions
>         outside of this transaction to minimise the transaction length
>         as well. I'm not sure about transaction 1. It depends on
>         what your are doing with 'stuff'.

No, it doesnt work, the seqence:
        update table 3 set value = value + 1
        select value from table 3
will not return the updated data, unless you are running at an isolation
level of read uncommitted.  Not a very good idea if you want to be able
to
rollback transactions cleanly.

        Cheers,
                Gary    B-)

--
---------------------------------------------------------------------------


                        POWERflex Corporation   Developers of PFXplus
                        Vox:  +61 3 9888 5833   Fax:  +61 3 9888 5451

                        Visit our Web Site:           www.pfxcorp.com

---------------------------------------------------------------------------



Fri, 01 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:


> [SNIP]
> >         Yes but multiple accesses doesn't imply multiple updates. Several
> >         readers and one writer I can understand but not vice versa.
> N-readers and 1-writer make life easier, but if you are not in a batch
> processing environment, n-readers and n-writers is far more prevalent.

        But not on the exact same data.

Quote:
> [SNIP]
> >         Your problem starts in transaction 2. The way you should be
> >         doing it is -

> >         begin transaction

> >         update table 3
> >         set value = value + 1

> >         select value
> >         from table 3

> >         update table 2 etc.

> >         commit transaction

> >         The update does the lock correctly for you. And since your
> >         select is within the scope of a transaction, you get your
> >         recently updated value. You could put your table 2 actions
> >         outside of this transaction to minimise the transaction length
> >         as well. I'm not sure about transaction 1. It depends on
> >         what your are doing with 'stuff'.

> No, it doesnt work, the seqence:
>         update table 3 set value = value + 1
>         select value from table 3
> will not return the updated data, unless you are running at an isolation
> level of read uncommitted.  Not a very good idea if you want to be able
> to rollback transactions cleanly.

        You haven't noticed the transaction wrapper (begin .. commit tran)
        I had around the statements. The select WILL return the updated
        data if its within the scope of the same transaction - the update
        locks the data and the transaction wrapper holds this lock until
        the transaction is commited (or rolled back). So the select will
        return the correct data. Not having a wrapper makes the transaction
        atomic and the select could well read the data after another
        update, which is not a good idea.

-am



Fri, 01 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:

> We had a similiar problem.  Its due to the fact that the select
> statement does not lock the row (Although the holdlock should...)

        A holdlock doesn't place an exclusive lock on an object.

Quote:
> We solved it by adding a statment above the select (but within the
> transaction)  that is essentially a noop, but updates the row:

> begin transaction

>    update card set current_units = current_units


        This of course just wastes resources. Its best to do the
        update first and then the select - but within one transaction.
        You're doing essentially the same thing but in a more
        roundabout way. The end effect is the same since the update
        initially locks the data for the duration of the transaction.

-am



Fri, 01 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

[SNIP]

Quote:
> > N-readers and 1-writer make life easier, but if you are not in a batch
> > processing environment, n-readers and n-writers is far more prevalent.

>         But not on the exact same data.

Quite often on the same data, ask any programmer who has worked on any
for of order taking/processing system how often they hammer a single,
external, data item.  (My example was taken from an existing,
in-production-and-being-sold, financials system)

Quote:
>         You haven't noticed the transaction wrapper (begin .. commit tran)
>         I had around the statements. The select WILL return the updated
>         data if its within the scope of the same transaction - the update
>         locks the data and the transaction wrapper holds this lock until
>         the transaction is commited (or rolled back). So the select will
>         return the correct data. Not having a wrapper makes the transaction
>         atomic and the select could well read the data after another
>         update, which is not a good idea.

Well, you may be right, but if I do the sequence (inside a transaction):
        update fred a = a + 1
        select a from fred
using ODBC, a is unchanged.
Of course, this may all be because the Sybase ODBC drivers for ASE 11.5
are scrofulous!
(And as the folks at Sybase in Oz have started playing pass the parcel
with it, I doubt I will get a usable solution)  (Why is the Microsoft
attitude spreading to other software vendors?!?!?!)

        Cheers,
                Gary    B-)

--
---------------------------------------------------------------------------


                        POWERflex Corporation   Developers of PFXplus
                        Vox:  +61 3 9888 5833   Fax:  +61 3 9888 5451

                        Visit our Web Site:           www.pfxcorp.com

---------------------------------------------------------------------------



Sat, 02 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:


> [SNIP]
> > > N-readers and 1-writer make life easier, but if you are not in a batch
> > > processing environment, n-readers and n-writers is far more prevalent.

> >         But not on the exact same data.
> Quite often on the same data, ask any programmer who has worked on any
> for of order taking/processing system how often they hammer a single,
> external, data item.  (My example was taken from an existing,
> in-production-and-being-sold, financials system)

        I was thinking along the lines of batch processes updating
        data. Even if you had more than one looking to update values
        of X into Y, only would would actually do it. I think your
        example looks like 'increment value' as in a running total
        or balance or 'decrement value' as in keep track of how many
        items are left. Not exactly the same thing - but close enough.
        This sort of activity is identified as more contentious but
        not insurmountable.

Quote:
> Well, you may be right, but if I do the sequence (inside a transaction):
>         update fred a = a + 1
>         select a from fred
> using ODBC, a is unchanged.
> Of course, this may all be because the Sybase ODBC drivers for ASE 11.5
> are scrofulous!

        OK, we're getting somewhere now. If you try it with a direct
        connection to a Sybase Server (thru isql or whatever), you'd
        find that it would work. I'm not sure what ODBC is doing, but
        its definitely not the right thing.

Quote:
> (And as the folks at Sybase in Oz have started playing pass the parcel
> with it, I doubt I will get a usable solution)  (Why is the Microsoft
> attitude spreading to other software vendors?!?!?!)

        There are other ODBC vendors around. Alternately, you could
        try Sybase's native drivers. These don't do anything particularly
        stupid, the way ODBC appears to.

-am



Sat, 02 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

[SNIP]

Quote:
>         I was thinking along the lines of batch processes updating
>         data. Even if you had more than one looking to update values
>         of X into Y, only would would actually do it. I think your
>         example looks like 'increment value' as in a running total
>         or balance or 'decrement value' as in keep track of how many
>         items are left. Not exactly the same thing - but close enough.
>         This sort of activity is identified as more contentious but
>         not insurmountable.

Yes, if it is a simple atomic operation on a value, the contention can
be handled, but (one of the many) _real_ thing(s) I am trying to do is
not a simple operation, it also involves data from other tables.  And
at the end of a sequence of operations, I want a whole lot of changes
to appear, and not allow any other users to look at the data.  For the
period in which this change is occurring, until it is committed or
rolled
back, the state of play is unknown.

[SNIP]

Quote:
>         OK, we're getting somewhere now. If you try it with a direct
>         connection to a Sybase Server (thru isql or whatever), you'd
>         find that it would work. I'm not sure what ODBC is doing, but
>         its definitely not the right thing.

Yes, if I run a simulation written in pure SQL, it does the right thing.
Unfortunately, I need to be able to do it in ODBC (I'll explain why a
bit later).

Quote:
>         There are other ODBC vendors around. Alternately, you could
>         try Sybase's native drivers. These don't do anything particularly
>         stupid, the way ODBC appears to.

Um, I _am_ using the Sybase native drivers, at least they are the ones
that come on the same CD as the ASE stuff.  And no vendor that I could
find has an ODBC driver that will work with ASE 11.5, they only appear
to support Sybase 10.  (the go looking for strangely named stored
procedures, and of course, die).

Why does it have to be ODBC?
        The base application is about 200K lines of 'C' code, and works
using ODBC on DB2, MS-SQL Server 6.5, and Oracle 7.  Obviously, there
are
a few different paths taken to get the same result, but in general, any
change in data looks like:
        select x from y for update
        new_x = modify(x)
        update y set x = new_x
        commit

So, I guess I'll keep trying to get a variant of "update y set x=x" and
so forth until I get it to work, or I convince the boss that we are
pissing away too much money trying to get Sybase to work.

        Cheers,
                Gary    B-)

--
---------------------------------------------------------------------------


                        POWERflex Corporation   Developers of PFXplus
                        Vox:  +61 3 9888 5833   Fax:  +61 3 9888 5451

                        Visit our Web Site:           www.pfxcorp.com

---------------------------------------------------------------------------



Sun, 03 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:

> Yes, if it is a simple atomic operation on a value, the contention can
> be handled, but (one of the many) _real_ thing(s) I am trying to do is
> not a simple operation, it also involves data from other tables.  And
> at the end of a sequence of operations, I want a whole lot of changes
> to appear, and not allow any other users to look at the data.  For the
> period in which this change is occurring, until it is committed or
> rolled back, the state of play is unknown.

        Yes it more problematic to do it over several tables at once.
        My preference would be to try breaking up as much of the
        activity as possible into smaller units. This, however, can    
        be impractical. Another approach might be to use a look up
        table that can be used as a reference to a key of a common
        identifying object. When this is being processed a flag can
        be set in this table that would mark it as being in use by
        a particular process. Other processes would then need to
        monitor this table as well and exhibit appropriate behaviour
        depending on the status of the flag.

Quote:
> Yes, if I run a simulation written in pure SQL, it does the right thing.
> Unfortunately, I need to be able to do it in ODBC (I'll explain why a
> bit later).

        I believe that you can call stored procedures thru ODBC. In
        which case you can write an sproc to do it and call it thru
        ODBC. That should solve the issue you had with the ODBC bug.

Quote:

> >         There are other ODBC vendors around. Alternately, you could
> >         try Sybase's native drivers. These don't do anything particularly
> >         stupid, the way ODBC appears to.

> Um, I _am_ using the Sybase native drivers, at least they are the ones
> that come on the same CD as the ASE stuff.  And no vendor that I could
> find has an ODBC driver that will work with ASE 11.5, they only appear
> to support Sybase 10.  (the go looking for strangely named stored
> procedures, and of course, die).

        Yes, you're using the Sybase drivers but thru ODBC. It needs them
        to communicate with Sybase. But I'm suggesting that you can use
        them directly and without using ODBC. Unfortunately this involves
        changes to your application's code.

Quote:
> Why does it have to be ODBC?
>         The base application is about 200K lines of 'C' code, and works
> using ODBC on DB2, MS-SQL Server 6.5, and Oracle 7.  Obviously, there
> are
> a few different paths taken to get the same result, but in general, any

        So you need to use ODBC to keep the code as uniform as possible.
        Calling sprocs thru ODBC will only work with Sybase and SQL Server.

Quote:
> change in data looks like:
>         select x from y for update
>         new_x = modify(x)
>         update y set x = new_x
>         commit

> So, I guess I'll keep trying to get a variant of "update y set x=x" and
> so forth until I get it to work, or I convince the boss that we are
> pissing away too much money trying to get Sybase to work.

        Yes, if 'modify(x)' is functionality that can be done fully in SQL
        code then 'update y set x = modify(x)' is sufficient.

-am



Sun, 03 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

[SNIP]

Quote:

>         Yes it more problematic to do it over several tables at once.
>         My preference would be to try breaking up as much of the
>         activity as possible into smaller units. This, however, can
>         be impractical. Another approach might be to use a look up
>         table that can be used as a reference to a key of a common
>         identifying object. When this is being processed a flag can
>         be set in this table that would mark it as being in use by
>         a particular process. Other processes would then need to
>         monitor this table as well and exhibit appropriate behaviour
>         depending on the status of the flag.

[SNIP]

>         I believe that you can call stored procedures thru ODBC. In
>         which case you can write an sproc to do it and call it thru
>         ODBC. That should solve the issue you had with the ODBC bug.
[SNIP]
>         Yes, you're using the Sybase drivers but thru ODBC. It needs them
>         to communicate with Sybase. But I'm suggesting that you can use
>         them directly and without using ODBC. Unfortunately this involves
>         changes to your application's code.
[SNIP]
>         So you need to use ODBC to keep the code as uniform as possible.
>         Calling sprocs thru ODBC will only work with Sybase and SQL Server.

[SNIP]
>         Yes, if 'modify(x)' is functionality that can be done fully in SQL
>         code then 'update y set x = modify(x)' is sufficient.

All of the points you have made are valid, and in many ways I agree with
a lot of them.

I don't want to have to write a completely different set of routines to
access Sybase, possibly using a different logic set to that when using
ODBC.  (At least the ODBC manuals are available as books, i.e. on paper,
I hate trying to read manuals that have been translated to the screen,
I _need_ to be able to flick through pages, and underline, and dog-ear
pages).  (I admit, I haven't looked in regard to ASE 11.5).

The ODBC path we took works fine with DB2, Oracle, and MS-SQL Server,
why
won't it work with Sybase?

We can't do it all in SQL/stored procedures, because we don't know what
queries are going to be made by the end users.

Sybase Oz now believe they understand what we are trying to do, and want
to send out someone for a couple of hours next week.  I hope they do
understand it, and can show me how to get the functionality I require.

It may be necessary to do part or all of the above, and if so, I will,
I just wish that all this mucking about wasn't necessary.

        Cheers,
                Gary    B-)
--
---------------------------------------------------------------------------


                        POWERflex Corporation   Developers of PFXplus
                        Vox:  +61 3 9888 5833   Fax:  +61 3 9888 5451

                        Visit our Web Site:           www.pfxcorp.com

---------------------------------------------------------------------------



Sun, 03 Sep 2000 03:00:00 GMT
 SP locking problem (probably easy!)

Quote:

> All of the points you have made are valid, and in many ways I agree with
> a lot of them.

> I don't want to have to write a completely different set of routines to
> access Sybase, possibly using a different logic set to that when using
> ODBC.  (At least the ODBC manuals are available as books, i.e. on paper,
> I hate trying to read manuals that have been translated to the screen,
> I _need_ to be able to flick through pages, and underline, and dog-ear
> pages).  (I admit, I haven't looked in regard to ASE 11.5).

        You can get hardcopy manuals from Sybase. I prefer them to the
        CD too.

Quote:
> The ODBC path we took works fine with DB2, Oracle, and MS-SQL Server,
> why won't it work with Sybase?

        Sybase's ODBC driver is OEMed from InterSolv, I think. You
        could try checking their site for patches etc. or ask Sybase
        Tech Support if there are any.

Quote:
> We can't do it all in SQL/stored procedures, because we don't know what
> queries are going to be made by the end users.

> Sybase Oz now believe they understand what we are trying to do, and want
> to send out someone for a couple of hours next week.  I hope they do
> understand it, and can show me how to get the functionality I require.

> It may be necessary to do part or all of the above, and if so, I will,
> I just wish that all this mucking about wasn't necessary.

        Good luck.

-am



Mon, 04 Sep 2000 03:00:00 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Annoying ISAM problem - probably easy, but...

2. probably easy trasformation question

3. Probably an easy question- please help

4. Probably an easy question for you experts...

5. Probably an easy one...

6. probably easy but Im new

7. Probably an Easy One

8. Probably an easy one.......

9. Probably an easy one

10. Help with SELECT statement (probably easy)

11. Probably an easy question

12. Probably easy syntax question


 
Powered by phpBB® Forum Software