How to code table locking in C# .Net

Ingres Forums

unread,

Mar 11, 2015, 1:12:08 PM3/11/15

to

Hi,

I'm writing a C# program to access/update data in a Ingres database and
would like some help with how to implement locking.

In the past in our 4GL or embedded ESQL C programs, we've always done
pessimistic locking for updates and used sql statements like 'set
lockmode on test_table where level=table,readlock=exclusive'.

With this being a C# Windows program that will be run on various PCs,
which will all be remote from the Ingres server which is Linux, we lose
some control of just telling users to get out of the program if they're
locking another user.

In this C# program I've tried a couple things to have it timeout if it
can't aquire a lock but it just sits and seems to wait forever or until
the other process has freed up their lock on the table.

I tried setting the IngresCommand instance objects CommandTimeout member
to 2 but no luck.

I also tried executing this statement prior to running my update, 'set
lockmode on test_table where level=table,readlock=exclusive,timeout=2'
hoping the timeout=2 would help, but either way it still sits being
blocked by the other user.

Any help I'd appreciate in either getting these timeouts to work, which
I'd prefer, or if there is a new better approach to locking in the C#
world with Ingres.

Thanks,
Kyle

--
kyledarrenhanson
------------------------------------------------------------------------
kyledarrenhanson's Profile: http://community.actian.com/forum/member.php?userid=394
View this thread: http://community.actian.com/forum/showthread.php?t=16917

Ingres Forums

unread,

Mar 11, 2015, 2:14:40 PM3/11/15

to

I think it should be WITH timeout=2

Jeremy

--
jruffer
------------------------------------------------------------------------
jruffer's Profile: http://community.actian.com/forum/member.php?userid=504

Ingres Forums

unread,

Mar 11, 2015, 2:22:13 PM3/11/15

to

This works and actually does timeout if I test in ISQL:

set lockmode on adj_control where
level=table,readlock=exclusive,timeout=2

so I'm assuming should work if I run it from C# as a command statement.

--
kyledarrenhanson
------------------------------------------------------------------------
kyledarrenhanson's Profile: http://community.actian.com/forum/member.php?userid=394

Ingres Forums

unread,

Mar 12, 2015, 4:22:36 AM3/12/15

to

I can't tell around c# to this topic, but java app have the same
challenge.

See for this 'timeout in java'
(https://supportactian.secure.force.com/help/articles/FAQ/Setting-timeout-In-Java/?q=java+timeout&l=en_US&fs=Search&pn=1)

--
bilgihan
------------------------------------------------------------------------
bilgihan's Profile: http://community.actian.com/forum/member.php?userid=8658

Ingres Forums

unread,

Mar 12, 2015, 7:40:21 AM3/12/15

to

thanks for the link. Changing the timeout globally isn't the best
option for us because will affect how all of our current applications
are coded, but it's something I'll consider if we don't find another
solution.

I'll check further if .NET has similar setTimeout method that Java has,
but will still gladly take any info people can offer.

thanks for you help :)

--
kyledarrenhanson
------------------------------------------------------------------------
kyledarrenhanson's Profile: http://community.actian.com/forum/member.php?userid=394

Ingres Forums

unread,

Mar 12, 2015, 8:29:21 AM3/12/15

to

This seems to work, at this point I'll stick with it unless someone has
a better way using the C# Ingres libraries:

I run this after I first connect using C#, then commit and it
successfully will throw exception if lock request exceeds the specified
time:

SET LOCKMODE SESSION WHERE TIMEOUT=5

Ingres Forums

unread,

Mar 12, 2015, 9:02:04 AM3/12/15

to

Can the full statement which works in isql be issued if you use
"excecute immediate"?
GJ

--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763