ODBC Transaction row lock

Posted on 2006-05-09
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
    LVL 40

    Expert Comment

    Hi ssdjgru1,

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


    Richard Quadling.

    Author Comment

    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

    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.

    LVL 12

    Accepted Solution


     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

    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

    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


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
    This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now