SYBASE locks (sh_intent)

[Karl Breeze]

I am having problems with Sybase 11.x / CTD 1.5 and sh_intent locks...

When selecting data from a table, sh_intent locks are being placed on all
the pages that have been read. These locks are only removed when the
statement is committed or if the cursor is disconnected. This is causing
concurrency problems if any updates are performed using a key other than the
primary key on a table that has these sh_intent locks on it.

I have tried changing the SQL.INI file (Auto Commit, Enhanced Cursors, etc.)
to try to prevent these locks being placed. I have also tried appending FOR
READ ONLY to the end of all SELECT statements. I have appended my preferred
SQL.INI settings to this message.

The curious thing is that these lock do not appear when I use an old version
of the SQLSYB32.DLL (dated 27/11/96). All subsequent versions of this DLL
leave the locks lying around. I really don't want to use a DLL that is over
two years old with my CTD 1.5 application, I am even wondering whether it is
a bug with the earlier DLL that is preventing these locks appearing!

Has anyone else had this problem?

Is there a SQL.INI configuration solution?

Please can anyone email me their SQL.INI settings for connecting to Sybase.

Thanks in advance,
Karl Breeze
iDEA ENGINEERING

[SYBGTWY]
SYBAUTOCOMMIT=ON
MAPGTICURSORS=OFF
MAPERROR=OFF
LOCKTIMEOUT=40
DISPLAYBINARYASHEX=ON
YEILDONSERVERCALL=0
ENHANCEDCURSORS=OFF
CHECKEXISTS=OFF
FETCHROW=20
SYBMAXMESSAGES=30,1024
ENABLEMULTIPLECONNECTIONS=OFF
CLOSECURSORATEOF=OFF

[Slava Imeshev]

Karl,

have you tried to use SybPrepareProc and
SybExecuteProc(hSql,strStatements,strIntoList)
instead of SqlPrepare and SqlExecute? as far as i know when the second case
centura creates an internal sybase cursor and FOR READ ONLY option couldn't
work. btw, what isolation level do you use?

Slava Imeshev

Karl Breeze wrote in message <7c0d6s$9s...@horizon.centurasoft.com>...

[Karl Breeze]

Slava,

Thanks for your reply.

After further investigation we have decided that the locking that is
happening is exactly what the Sybase manual says should happen. I think
that it will be better for us to develop with the knowledge that these lock
are going to be placed, and that any UPDATE statements should always use a
unique index.

Although the old DLL did not cause these locks to be placed, I believe that
this allowed less thought to be given to the implications of DML commands.
This in turn leads to bad coding that does not implement concurrency
correctly.

I'm quite sure that the old DLL contained bugs with the locking and that we
should be coding against the new DLL.

Regards,
Karl

Slava Imeshev wrote in message <7cfkni$k5...@horizon.centurasoft.com>...

[James Pearson]

Karl,

I noticed one thing in your SQL ini that might help with your problem. Set
closecursoroneof=on. This setting, in conjunction with sybautocommit=on, should
effectively remove the shared intent lock as long as you fetch through the
entire result set.

I also agree with your other comments. It is important to take a hard look at
your SQL and transaction management.

Hope this helps. Good luck.