Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


ODBC Transaction row lock

Posted on 2006-05-09
Medium Priority
Last Modified: 2010-05-18
I have an IBM DB2 database, which is accesseded through ODBC. Because I am building a distributed system it is essential to use transactions in some cases. The problem is that if I do odbcConnection.BeginTransaction(), it locks the whole table when i issue a query which is very specific (for example: I query for row which has unique field ID).
Is there a way to lock just the affected rows and not the whole table with ODBC.
(I noticed something on that matter with OdbcCommand.transaction.IsolationLevel, but the property is read only)
Question by:ssdjgru1
  • 4
  • 2
LVL 40

Expert Comment

by:Richard Quadling
ID: 16638206
Hi ssdjgru1,

From my understanding the whole table is locked. Just be glad that the whole DATABASE is not locked!


Richard Quadling.

Author Comment

ID: 16638232
Tabel lock is not acceptible because there is around 500 computers in network and they all need fields in different rows of table...if they would wait for each other the performance would be convergating to zeeeroo ;)

thank you anyway.....anybody else?
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 200 total points
ID: 16638280
Create your own semaphor locking mechanism.


2 - UPDATE table SET semaphore=1 WHERE id=known_id AND semaphore<>1
3 - If you affected 1 row, then YOU have the lock. If you did not affect the row, then you do NOT have the lock and you will have to retry step 2 x-times or for x-seconds.
4 - Once you have the lock, you can SELECT * FROM table where id=known_id
5 - Process the data as you like.
6 - UPDATE table SET semaphore=0 WHERE id=known_id AND semaphore<>0
7 - If you affected 1 row, then you have successfully unlocked the row. If you did not affect 1 row, then there is an issue and the lock is still set.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Accepted Solution

sumix earned 400 total points
ID: 16641928

 You may try setting different IsolationLevels for your transaction. The isolationlevel can be set when you start the transaction using BeginTransaction method of the connection, something like:

   OdbcTransaction Trans = odbcConnection1.BeginTransaction(IsolationLevel.ReadCommitted);
   odbcCommand1.Transaction = Trans;

  (notice that ReadCommitted isolationlevel is less restrictive than Serializable)
LVL 40

Expert Comment

by:Richard Quadling
ID: 16831455
I know the semaphore technique will work. Its not pretty admittedly. I don't know about the isolation level, but I believe this is as appropriate. It may be more so.

If sumix can give some explanation about the levels (what they are and when/how to use them), then that will very much assist in answering this question (and I'll learn something for free!!!!).

Other than that, split is a fair outcome. Both solutions will assist the ssdjgru1.

Author Comment

ID: 16831491
Sumix had the right solution for me, but I forgot to give him points. Solution from RQuadling works also, but I don't have a possibility to change databese to write some semaphores.

RQuadling: IsolationLevels are the levels of isolation of the data that is beeing queried - it tells us basicly what are the locking rules for some table. Than you for your effort.
LVL 40

Expert Comment

by:Richard Quadling
ID: 16831683

For many years I worked on a NON SQL db. Locking data was handled using semaphores as these where the only way of doing what is known as "atomic locking". Basically, the lock and the test are handled as a single entity. If the lock was set, you had set it. If the lock was not set, someone else has locked it.

With the advent of client server mechanisms, I've seen many applications work such that you are only told you CANNOT save the data you've channged AFTER you make the change and are trying to save it. You now lose the edits you've made. Clearly an undersirable effect.

If I'm editing a row, I want to lock it until I've finished editing it. NOT be told that I cannot have my edits saved because someone else got there first.

The isolation level would suggest that this is a solution for me also.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question