0

I'm having trouble figuring out SQL Anywhere 16 behavior compare to Sybase 9 they both has an identical database set as dirty read or set transaction isolation level 0

Even from an Delphi application (using TAsaSession) or thru SQL_anywhere_XX, I´m getting the same behavior:

On Sybase 9, from two connection I can run an

UPDATE associate SET nm_associate = nm_associate + ' Test' WHERE id_associate = 620

without aCOMMIT at the end;

On Sybase 16 1st connection locks out associate's table and holds up the 2nd one until COMMIT runs and free its way.

I´m not sure if isolation level has anything to with it, or if it's anything else i need to set so I can migrate from 9 to 16 without this problem . Can anyone help-me on that?

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
Marisco
  • 125
  • 1
  • 3
  • 16
  • Sybase was a company that produced a number of different database products (Sybase ASE, Sybase IQ, SQL Anywhere, etc). It's helpful to label and tag your question accordingly and makes it more likely you will get a helpful answer. – Mike Gardner Mar 17 '17 at 11:40

2 Answers2

0

Isolation level 0 only has effect on read operations: it allows you to read data that is part of a not-yet-committed transaction by a different user. When running an update, at least level 1 is used for the update statement irrespective of what you have set the isolation level in the session to.

RobV
  • 2,263
  • 1
  • 11
  • 7
  • Thank you much for your response @RobV. `TAsaSession` has `TransIsolation = atReadUncommitted`, still, same behavior. So you have a bigger picture of the problem, in our project, those connection started to pile up on Sybase 16 then the i got a timeout message: "The operation has time out”. after while, it drops those connection by its own and sometimes the system returns to work, other times DLL is froze on IIS and need to be restated. Any other idea? – Marisco Mar 17 '17 at 17:55
0

Problem solved after Setting chained='On' on each connection.

AsaConnection.Connected := True;
vQry.Session := AsaConnection;
vQry.SQL.Text := 'SET OPTION chained=''On''';
vQry.ExecSQL;
Marisco
  • 125
  • 1
  • 3
  • 16