Handle locks via ODBC for update (or insert)

[Nesster]

Hi,
I looked around quite a bit for this and haven't been able to find the answer.

I'm looking for a clean way to handle table locks when attempting to write via ODBC. I'm using VB.Net (Visual Studio 2008), Datadirect 4.10 SQL92 9.1E ODBC driver, on a 9.1E DB (Win32).

I know the tricks for read only access, using the "WITH(NOLOCK)" parameter in the select statement. But what if I really got to write ? How can I check/handle the situation when the record is locked?

Thank you for your time.

 

[tboogerd]

I am looking to do the same thing. I however currently do this.

I have 2 odbc connection strings, one is set to read uncommitted and the other to serializable for their default isolations. On all select statements I use the read uncommitted connection string, but when I want to insert or update i use code to connect to the other connection string.

My issue is that when using the serializable connection string, if there are any record locks on that table i just handle the error message. I would rather know who/what has the table locked and handle it from their.

How are you using WITH(NOLOCK) in your sql statement?

 

[Nesster]

Here's how I use the WITH(NOLOCK):

SELECT * FROM PUB.QuoteDtl WHERE PUB.QuoteDtl.Company = 'company' AND PUB.QuoteDtl.QuoteNum = 'QuoteNum' AND PUB.QuoteDtl.PartNum = 'PartNum' WITH(NOLOCK)

We are at the same point in handling the error, if comes the situation where the record is locked by someone else. It'd be nice to be able to display to the user that the record is locked by <this> user.


Luckily I am able to do what I intended, without annoying record locks all the time, because the records I'm writing to can't be locked by someone else.


Lets post back here if we ever find something useful about this.

 

[tboogerd]

Sounds good on posting back to this forum. Thanks for the new NoLock SQL syntax, it will come in handy for future coding projects.

I was able to get my app to work properly simply by setting the ODBC Driver to Read Committed. After tedious testing it appears that no locks are created when using select statements and only a row-level record lock is created on the update. I'm not 100% but all I know is when I had the ODBC set to Serializable I continuously received record locks and after changing it to Read Committed my program has worked flawlessly.

 

[ryanm]

Hello, I have a question about locking in the other direction. Unless I am incorrectly setting up the isolation level of my connections I don't seem to have any luck in holding a lock myself.

Here is my question: if I want to update a row in a table, is there any way for me to lock that row in a pessimistic fashion?

I see that rollback and commit are not supported over ODBC, leading me to believe that I have no way of opening a transaction for an edit like this and then rolling back or commiting after the user is done editing the row... meanwhile holding a lock for the entire time.

Any ideas?

 

[RealHeavyDude]

You don't say anything 'bout the Progress/OpenEdge version you are using. Which is essential information here because the SQL engine and the drivers have evolved dramatically over time.

What makes you think that rollback and commit are not supported?

Regars, RealHeavyDude.

 

[maretix]

You don't say anything 'bout the Progress/OpenEdge version you are using. Which is essential information here because the SQL engine and the drivers have evolved dramatically over time.

What makes you think that rollback and commit are not supported?

Regars, RealHeavyDude.

Hi to all...
What about if i need to update 1000.000 records in one time by SQL via ODBC ???
I was not able to do ...
A thirdy part program need to execute UPDATE table SET field = 'value' over our table..
If they do that , they receive a LOCK ERROR, even if -L parameter on DB is high value
ODBC -2146232009 ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB.e7mopro.

Only if we update 3000 records by time , we do not receive errors.

Is it possible to read only 3000 records by time and create a loop in SQL ODBC ???
This thirdy part program cannot run a VB.NET program, but can only to run a single SQL Command....

Regards in advance.

 

[RealHeavyDude]

Updating 1 million records in one transaction appears to me to be a strange requirement. But, then again SQL and the Progress ABL have a completely different approach on how they handle transactions. In the Progress ABL I would never deliberately try to update 1 million records in a single transaction because most likely, not only will I blow the lock table, but maybe also the before image and thereby crash the database.

Nevertheless, AFAIK, you need to set the transaction isolation level on the ODBC data source accordingly.
Maybe this knowlegebase entry can help you:
http://knowledgebase.progress.com/articles/Article/7752

Heavy Regards, RealHeavyDude.

 

[maretix]

Updating 1 million records in one transaction appears to me to be a strange requirement. But, then again SQL and the Progress ABL have a completely different approach on how they handle transactions. In the Progress ABL I would never deliberately try to update 1 million records in a single transaction because most likely, not only will I blow the lock table, but maybe also the before image and thereby crash the database.

Nevertheless, AFAIK, you need to set the transaction isolation level on the ODBC data source accordingly.
Maybe this knowlegebase entry can help you:


Heavy Regards, RealHeavyDude.

Hi RealHeavyDude thanks for your answer.

Yes i know with ABL how to use TRANSACTIONS.
My problem is with ODBC ...i will read attachment you sent me.
It is a third part company that try to use an UPDATE over our DB Progress..and they cannot o different way to update data.
I 'd like to learn how to update a group of records ...with SQL of PROGRESS...

In T-SQL it would be like that ...

WHILE EXISTS ( SELECT * FROM table WHERE <condition to update>)
BEGIN
SET ROWCOUNT 1000
UPDATE Table WHERE <Condition to update>
SET ROWCOUNT 0
END.

Thanks.