Thread: Record locking issue using ODBC from .NET

I want to have exclusive read access to a table row, so that if a client has access to that row, any other client
shouldeither wait or get an error of the type "could not obtain lock on row in relation 'customers'", depending on the
semanticsused in the query string(nowait) 

Both clients execute:

...
OdbcTransaction ^ transaction = connection->BeginTransaction();
String ^ query = "select * from customers where id = 3 for update nowait";
OdbcCommand ^ cmd = (gcnew OdbcCommand(query, connection, transaction));
reader = cmd->ExecuteReader();
...

After the first client locks the record and before he ends the transaction, the second tries to acquire the lock. As
expectedan exception is raised, BUT: 

One would expect that this exception is of type OdbcException explaining that "the lock cannot be aquired". Instead of
thisI get a System::InvalidOperationException which disables my connection for some reason. However the above exception
hostsan inner exception of the desired type(OdbcException).  

Does anybody have any idea why I get this type of behaviour, why is my connection closed?

Thanks a lot in advance,
Antonis

Hi Antonis,
Which version of driver are you using ?

regards,
Hiroshi Inoue

anomatos@freemail.gr wrote:
> I want to have exclusive read access to a table row, so that if a client has access to that row, any other client
shouldeither wait or get an error of the type "could not obtain lock on row in relation 'customers'", depending on the
semanticsused in the query string(nowait) 
>
> Both clients execute:
>
> ...
> OdbcTransaction ^ transaction = connection->BeginTransaction();
> String ^ query = "select * from customers where id = 3 for update nowait";
> OdbcCommand ^ cmd = (gcnew OdbcCommand(query, connection, transaction));
> reader = cmd->ExecuteReader();
> ...
>
> After the first client locks the record and before he ends the transaction, the second tries to acquire the lock. As
expectedan exception is raised, BUT: 
>
> One would expect that this exception is of type OdbcException explaining that "the lock cannot be aquired". Instead
ofthis I get a System::InvalidOperationException which disables my connection for some reason. However the above
exceptionhosts an inner exception of the desired type(OdbcException).  
>
> Does anybody have any idea why I get this type of behaviour, why is my connection closed?
>
> Thanks a lot in advance,
> Antonis

Please try the 8.2.0100 which would be released soon.
If you are in a hurry, please install the 8.2.0002 version at
http://pgfoundry.org/projects/psqlodbc/ .

regars,
Hiroshi Inoue

> anomatos@freemail.gr wrote:
> Hello there,
>
> I'm using version 8.01.02.00 of the PostgreSQL driver, any ideas?
>
>

> > Hi Antonis,
> > Which version of driver are you using ?
> >
> > regards,
> > Hiroshi Inoue
> >

> > > anomatos@freemail.gr wrote:
> > >
> > > I want to have exclusive read access to a table row, so that if a client
> > > has access to that row, any other client should either wait or get an
> > > error of the type "could not obtain lock on row in relation 'customers'",
> > > depending on the semantics used in the query string(nowait)
> > >
> > > Both clients execute:
> > >
> > > ...
> > > OdbcTransaction ^ transaction = connection->BeginTransaction();
> > > String ^ query = "select * from customers where id = 3 for update nowait";
> > > OdbcCommand ^ cmd = (gcnew OdbcCommand(query, connection, transaction));
> > > reader = cmd->ExecuteReader();
> > > ...
> > >
> > > After the first client locks the record and before he ends the transaction,
> > > the second tries to acquire the lock. As expected an exception is raised,
> > > BUT:
> > >
> > > One would expect that this exception is of type OdbcException explaining that
> > > "the lock cannot be aquired". Instead of this I get a
> > > System::InvalidOperationException which disables my connection for some
> > > reason. However the above exception hosts an inner exception of the desired
> > > type(OdbcException).
> > >
> > > Does anybody have any idea why I get this type of behaviour, why is my
> > > connection closed?
> > >
> > > Thanks a lot in advance, Antonis