One transaction at a time???!!
Joe King
problem is that when one client begins a transaction none of the other
clients can access the database until the first one has either committed or
rolled back. This is most obvious when one developer is stepping through
code - the others just hang. This is not the case if performing transactions
in isqlw. Can anyone shed any light on this behavior?
Joe.
--
==============================================
When replying by email please use: joe....@net.ntl.com
==============================================
Ng K C Paul
Joe King (joe....@NOSPAM-net.ntl.com) wrote:
: I'm currently working on a VB5 client to a Sybase server using ODBC. The
:
:
Anthony Mandic
>
> I'm currently working on a VB5 client to a Sybase server using ODBC. The
> problem is that when one client begins a transaction none of the other
> clients can access the database until the first one has either committed or
> rolled back. This is most obvious when one developer is stepping through
> code - the others just hang. This is not the case if performing transactions
> in isqlw. Can anyone shed any light on this behavior?
It sounds like an open transaction problem. Perhaps a "select
for update" statement or running in chained mode. To prevent
this only do transactions for the duration of the actual DML.
However, your code will need to be changed to check for data
consistency. Look up the details on timestamps for a common method.
-am
Joe King
>Try to perform all transactions in one stored procedure
That isn't practical as the system doesn't use stored transactions (yes, I
know!!).
Joe
Susan Schwartz
> I'm currently working on a VB5 client to a Sybase server using ODBC. The
> problem is that when one client begins a transaction none of the other
> clients can access the database until the first one has either committed or
> rolled back. This is most obvious when one developer is stepping through
> code - the others just hang. This is not the case if performing transactions
> in isqlw. Can anyone shed any light on this behavior?
>
> Joe.
>
> --
> ==============================================
> When replying by email please use: joe....@net.ntl.com
> ==============================================
Joe,
Because you can run multiple transactions under isql, I would think there is no
locking problem -- several transactions vying for access to the same tables at
the same time. Rather, I'd look to ODBC for the problem and its solution.
First question: are you running the transactions on separate statement handles
(hstmt's)? -- I'd imagine you'd have to. But ... are you running all the
hstmt's on the same connection handle (hdbc)? If so, there's your problem. The
connection handle encapsulates a single implicit transaction, and that big
transaction would run your little individual transactions nested, sequentially.
Incidentally, if you are using one connection handle per hstmt you might still
have a problem if the one connection's transaction is blocking access to
another connection's transaction. In that case, I'd try either free-ing the
connection handle when you are done with it or calling SQLEndTran with the
COMMIT parameter on the connection handle at the end of your explicit
transaction.
Susan