• ParkinT (unregistered)
    Comment held for moderation.
  • benmurphyx (unregistered)

    Can I start crying now?

  • snoofle (cs)

    ... Obligatory: race condition?

  • moefh (unregistered) in reply to snoofle
    snoofle:
    ... Obligatory: race condition?
    Yep... the wait/lock, as implemented, is not atomic. It's amazing how people keep reinventing the square wheel all the time.
  • TRWTF (unregistered)

    TRWTF - Not one, but two underscores in the table name. db__locks?

    Well, and of course the whole crappy implementation of something that already exists natively in SQL.

  • Mr Clever Ideas (unregistered)

    Easy. Just wrap the UPDATE tbl__Locks statement in a transaction, along with a final check that no-one else has just locked your table. Problem solved!

  • Daniel Smedegaard Buus (unregistered)

    That's really cute :)

    Although it'd be interesting to see the kind of SQL operations he wants to do after his "locking system" lets him continue. A transaction provides atomicity, but doesn't protect against race conditions. Two simultaneous updates using transactions will still mean one update "disappearing". But, even if it's this problem he's trying to solve, he should be using an SQL table lock to solve it, not this homebrewed SELECT thing which has exactly the same problem. Maybe he should try doing it three times in a row for extra-extra safety ;)

  • ubersoldat (cs)

    The comments are locked, proceed to next post.

  • Bob (unregistered) in reply to moefh
    Comment held for moderation.
  • dtech (cs)

    So what are those 17 previous examples of where solving the meta-problem actually was effective?

    Only thing I can think of are the Bytecode->Assembly->3rd gen->Managed code transistions, even if those are meants that's only 4.

    Maybe Relational databases (from flat-files) too

  • Ike (unregistered)

    I never meta-problem I couldn't solve.

  • m (cs) in reply to dtech
    dtech:
    So what are those 17 previous examples of where solving the meta-problem actually was effective?

    Only thing I can think of are the Bytecode->Assembly->3rd gen->Managed code transistions, even if those are meants that's only 4.

    Maybe Relational databases (from flat-files) too

    lexer and parser generators, monitors (as language features), metaprogramming (as in Ada’s generics or C++’s templates—is this a meta-meta-problem?). That’s 7 to 8 so far. Maybe something not language related exists too.

  • RichP (cs) in reply to dtech

    They're like the five "most most passionate, the most pure" from The Princess Bride. Knowing that the previous examples exist and are categorized is all that's important.

  • jon (unregistered)

    I wish I could have been there when someone asked "Hey, what happens if two processes try to update db__locks at the same time?".

    I assume he would start "That's simple. I'll just add ....." and then his face would slowly change, as he realized what the next question after that would be.

  • hoodaticus (cs)

    TRWTF is that I am the only developer I have ever seen use a transaction in my company.

    As a result, I normally write all sensitive DB processes myself and then hand off the API to my devs to use.

  • hoodaticus (cs) in reply to jon
    jon:
    I wish I could have been there when someone asked "Hey, what happens if two processes try to update db__locks at the same time?".

    I assume he would start "That's simple. I'll just add ....." and then his face would slowly change, as he realized what the next question after that would be.

    Yeah this is fucking retarded. I've written better DB locks stoned out of my mind - with self-expiration so you don't have to clear failed locks. And mine were thread safe.

    Use a nullable DATETIME field called LockTime when you need a db-wide record mutex. I won't go into details as you guys can hopefully figure out the rest.

  • net.split (unregistered)

    So I did something kind of like this once as a newbie, except it was much more brutal. I made a simple web game back in college (complete with SQL scattered across PHP files, oh god it was bad). Some enterprising players found out they could refresh the page really fast to gain lots of benefits without spending resources more than once.

    My solution? Put a lock on the player's account at the start of every page load, then remove it at the end of the script. Every time a page script started, it checked to see if the lock was there; if it was, then it would stop with an error page.

    What if there was an error in the script? The player was locked out of the game until some amount of time passed (about 5 seconds).

    What if different users kicked off race condition processes? Then something probably broke.

    In my defense, I was using MySQL prior to 5, which did not support transactions. But still.

  • JDege (unregistered)

    I've actually had to build something like this, because DB transactions don't solve a real business problem.

    Scenario: user loads a record into an edit form, goes to lunch, comes back, changes a field and hits "save". But what if another user had modified the underlying record, while he was at lunch?

    Database transactions are not the solution to this problem - holding a transaction open indefinitely while waiting for user input is a seriously bad idea.

    The choices are two: either you implement a locking mechanism, like that described, or you provide some sort of validation, prior to the save, to ensure that the underlying record has not changed from what it was when the edit form was loaded, and abort the save if it has.

    Of course, if you implement a locking mechanism, you need to provide a mechanism for over-riding the lock, and then for checking to see that the lock hasn't been over-ridden before a save, and it all just escalates in complexity.

    So most programmers just ignore the problem. But in some business cases, you cannot.

  • versioning? (unregistered) in reply to JDege

    It doesn't need to be that complex, and you don't really need to do much validation. Put a version number on each record and send the version number back to the HTML page. When the user submits the modification request "update ..., version=version+1 where version=LAST_VERSION_SEEN and ..." and check how many records were changed. Wrap all the actual updates (only on submit) in a transaction and roll those back to notify the browser if anything changed.

    So added complexity is:

    1. A transactional update and rollback which you should have anyways.
    2. A single version number on each table, which is returned in every form
    3. An additional condition in every where clause
    4. A single sql%rowcount after every update statement.

    I'm sure there are other solutions but that is pretty easy to implement.

  • An Old Hacker (unregistered)

    As mentioned, there are times when home-brew transactions are required. Such as when you are writing a memory manager for an all-assembly micro OS.

    Here's how its done, and you can actually get by even if you lack atomic in the underlying system.

    1. Enter the fact that you want a lock into a the queue. (Since we're talking databases, use an auto incrementing key.)
    2. Wait until there is no one ahead of you in the queue.
    3. Wait until the lock clears.
    4. Write the lock with your id.
    5. Do your stuff.
    6. Clear your lock.
    7. Remove yourself from the queue.

    Obviously, it takes more than this if you are running a distributed host, or if you have layers of clients, but this is the theory. Depending on the details of the environment, it might be better to move 7 to 3.5.

  • An Old Hacker (unregistered) in reply to An Old Hacker
    An Old Hacker:
    As mentioned, there are times when home-brew transactions are required. Such as when you are writing a memory manager for an all-assembly micro OS.

    Here's how its done, and you can actually get by even if you lack atomic in the underlying system.

    1. Enter the fact that you want a lock into a the queue. (Since we're talking databases, use an auto incrementing key.)
    2. Wait until there is no one ahead of you in the queue.
    3. Wait until the lock clears.
    4. Write the lock with your id.
    5. Do your stuff.
    6. Clear your lock.
    7. Remove yourself from the queue.

    Obviously, it takes more than this if you are running a distributed host, or if you have layers of clients, but this is the theory. Depending on the details of the environment, it might be better to move 7 to 3.5.

    Obviously, that is 4.5.

  • Ross Presser (unregistered)

    cries

    I actually did something very much like this ... in dBase II on a CP/M variant, a long time ago.

    It almost worked at least 75% of the time.

  • geoffrey (unregistered) in reply to JDege
    JDege:
    I've actually had to build something like this, because DB transactions don't solve a real business problem.

    Scenario: user loads a record into an edit form, goes to lunch, comes back, changes a field and hits "save". But what if another user had modified the underlying record, while he was at lunch?

    Database transactions are not the solution to this problem - holding a transaction open indefinitely while waiting for user input is a seriously bad idea.

    The choices are two: either you implement a locking mechanism, like that described, or you provide some sort of validation, prior to the save, to ensure that the underlying record has not changed from what it was when the edit form was loaded, and abort the save if it has.

    Of course, if you implement a locking mechanism, you need to provide a mechanism for over-riding the lock, and then for checking to see that the lock hasn't been over-ridden before a save, and it all just escalates in complexity.

    So most programmers just ignore the problem. But in some business cases, you cannot.

    If you only update the fields that were changed, you don't have to hold a transaction open. Hold a lock on the relevant data around the row(s) being updated, and update only the fields that were changed. Then if you have collisions, it really doesn't matter -- if two people are updating the same field, it's typically not up to the software to determine which user is more important.

  • DaveK (cs) in reply to An Old Hacker
    An Old Hacker:
    As mentioned, there are times when home-brew transactions are required. Such as when you are writing a memory manager for an all-assembly micro OS.

    Here's how its done, and you can actually get by even if you lack atomic in the underlying system.

    1. Enter the fact that you want a lock into a the queue. (Since we're talking databases, use an auto incrementing key.)
    And if we aren't talking databases (but say for example are writing a memory manager for an all-assembly micro OS) what happens when two threads try to "enter the fact that [they] want a lock into the queue" at the same time? How do they avoid racing if you "lack atomic in the underlying system"? All the lock-free designs I know about rely on having an atomic compare-and-swap instruction available.
  • DaveK (cs) in reply to moefh
    moefh:
    snoofle:
    ... Obligatory: race condition?
    Yep... the wait/lock, as implemented, is not atomic. It's amazing how people keep reinventing the square wheel all the time.
    I can see the race condition, but I can't see how the locks are getting stuck on '1', unless the "... snip whole bunch of code ..." is hiding a potential early/error/exceptional exit of some sort.
  • JDege (unregistered) in reply to geoffrey
    If you only update the fields that were changed, you don't have to hold a transaction open. Hold a lock on the relevant data around the row(s) being updated, and update only the fields that were changed. Then if you have collisions, it really doesn't matter -- if two people are updating the same field, it's typically not up to the software to determine which user is more important.

    In a number of business cases, that's very much not true. Consider an ordinary workforce management system. The dispatcher looks at a work order, and decides to assign it to a particular field worker. If, between the time dispatcher opens the work order, and assigns it to a field worker, some other dispatcher assigns it to a different field worker, the software must prevent the assignment.

    In most such cases, the logic for transferring a work order from one worker to another is very different than that for assigning a work order in the first place. And in most circumstances, the dispatcher won't want to transfer the order, if it has already been assigned. So the appropriate response for the software is to indicate to the dispatcher that the order has not been assigned, because it had already been assigned, and to allow the dispatcher to decide whether to transfer or to leave it be.

    You can, and often should, put in refresh capabilities, so that if another dispatcher assigns a work order, the first dispatcher's display is updated to reflect that, but such refresh capabilities take time, and are always outside of database transaction boundaries, so even if you do, you still have a window where duplicate assignment can occur.

    Personally, I'd not use a lock table to handle this sort of problem - I'd use an auto-increment modification-number field on the record. (Inside the transaction, read the number and abort if it's not what you originally read). Except that I've seen customers absolutely insist on record locking - to the extent of providing visual indicators for which records were locked, and allowing for explicit lock over-rides in the UI.

  • Mcoder (cs) in reply to m
    m:
    dtech:
    So what are those 17 previous examples of where solving the meta-problem actually was effective?

    Only thing I can think of are the Bytecode->Assembly->3rd gen->Managed code transistions, even if those are meants that's only 4.

    Maybe Relational databases (from flat-files) too

    lexer and parser generators, monitors (as language features), metaprogramming (as in Ada’s generics or C++’s templates—is this a meta-meta-problem?). That’s 7 to 8 so far. Maybe something not language related exists too.

    Operational systems.

  • Saepius (unregistered) in reply to JDege
    JDege:
    I've actually had to build something like this, because DB transactions don't solve a real business problem.

    Scenario: user loads a record into an edit form, goes to lunch, comes back, changes a field and hits "save". But what if another user had modified the underlying record, while he was at lunch?

    Database transactions are not the solution to this problem - holding a transaction open indefinitely while waiting for user input is a seriously bad idea.

    The choices are two: either you implement a locking mechanism, like that described, or you provide some sort of validation, prior to the save, to ensure that the underlying record has not changed from what it was when the edit form was loaded, and abort the save if it has.

    Of course, if you implement a locking mechanism, you need to provide a mechanism for over-riding the lock, and then for checking to see that the lock hasn't been over-ridden before a save, and it all just escalates in complexity.

    So most programmers just ignore the problem. But in some business cases, you cannot.

    Yeah, it's another "wtf" by yet another web programmer that hasn't done any real business work. Like you, I've implemented pessimistic concurrency control for the same reason. (optimistic was not acceptable in this situation). collisions were unacceptable.

    A simple counter incremented when granting a lock was used as a token to detect overrides. And, of course, check -and-grant-lock happened inside a database transaction, avoiding race conditions there.

    Chances are the original situation was that the transactions were "long running" and thus not candidates for database transactions.

  • Saepius (unregistered) in reply to Daniel Smedegaard Buus
    Daniel Smedegaard Buus:
    That's really cute :)

    Although it'd be interesting to see the kind of SQL operations he wants to do after his "locking system" lets him continue. A transaction provides atomicity, but doesn't protect against race conditions. Two simultaneous updates using transactions will still mean one update "disappearing". But, even if it's this problem he's trying to solve, he should be using an SQL table lock to solve it, not this homebrewed SELECT thing which has exactly the same problem. Maybe he should try doing it three times in a row for extra-extra safety ;)

    You know how I know you're a MySQL user?

    1. SQL transactions DO protect against race conditions. On SQL server (non snapshot) by locking records that have been read. With snapshot (or on oracle) by checking database values haven't changed between transaction start and commit, and raising an error if they have.

    2. Table locks are not normally used in anything but MySQL, as transactions lock the relevant parts of the tables as needed.

  • Ol Bob (cs)

    Recipe for "Disaster Primadonna" Ingredients 1 Isolated and Unsupervised Programmer 1 New Product 0 Interest by Programmer in formal learning about New Product

    A. Install New Product on Server. B. Allow Isolated Programmer to tinker in isolation. C. Assign New Project to Programmer. D. Cook under pressure of Project Deadline. E. Serve to all follow-on programmers with a side helping of NSAID of choice.

    Bon appetit!

  • dtech (cs) in reply to An Old Hacker

    Won't work 100% safe without atomics: what if 2 processes write to the queue at the same time? (step 1)

    Either one (or both) lock up (wait forever to be ahead of the queue) or you take a serious performance hit by reading the whole queue every time.

  • Ol Bob (cs) in reply to Saepius

    Relational databases are not built for long-running transactions. Start transaction - do work - commit or rollback, as fast as possible. Do not use data-bound controls unless it's a single-user app (and IME most "single-user" apps eventually become multi-user apps). Some database products are better at long runners than others, but all will eventually crap out with varying values of "crap". If you're lucky you'll get an error such as "original data expired - unable to update" error. If you're unlucky you'll get a "rollback buffer space exhausted - fatal error - server shutting down" error. YMMV.

    Share and enjoy.

  • Cico71 (unregistered) in reply to JDege
    Comment held for moderation.
  • Yazeran (cs) in reply to JDege
    JDege:
    I've actually had to build something like this, because DB transactions don't solve a real business problem.

    Scenario: user loads a record into an edit form, goes to lunch, comes back, changes a field and hits "save". But what if another user had modified the underlying record, while he was at lunch?

    Database transactions are not the solution to this problem - holding a transaction open indefinitely while waiting for user input is a seriously bad idea.

    The choices are two: either you implement a locking mechanism, like that described, or you provide some sort of validation, prior to the save, to ensure that the underlying record has not changed from what it was when the edit form was loaded, and abort the save if it has.

    Of course, if you implement a locking mechanism, you need to provide a mechanism for over-riding the lock, and then for checking to see that the lock hasn't been over-ridden before a save, and it all just escalates in complexity.

    So most programmers just ignore the problem. But in some business cases, you cannot.

    My solution to this problem has always been the 'optimistic locking' scheme where an integer field is used and passed back without the user having the option to change it and then used in the where clause in the (parameterised) UPDATE statement:

    Update table SET row1=?,row2=?... WHERE id = ? AND counter = ?

    (coupled with a SP which increments the counter upon updates amongst other things)

    and then check for number of rows updated (which should be 1 if no-one has messed with that record since the user loaded the content in the edit form and 0 otherwise (more than one and something seriously bad just happened!)

    If 0 was returned, notify the user in a suitable manner that he/she has to reload and start over.

    Then wrap everything in a transaction so you can rollback if something went wrong or the number of updates is greater than 1 (which should never happen)

    Yours Yazeran

    Plan: To go to Mars one day with a hammer.

  • Cico71 (unregistered) in reply to Ol Bob
    Ol Bob:
    Relational databases are not built for long-running transactions.

    Yes, long-running transaction are better managed by Workflow Management Systems that where en vogue in the nineties. Quite a lot of long-running transactions can't simply be rolled back in the traditional sense, they need a chain of compensating actions. In many cases they even spawn different systems.

    It's not rocket science: a lot of status transitioning and queuing.

    Most scenarios can be solved using simple updates and SQL Server Broker (built-in into the engine, other RDBMS offer similar queuing technologies).

    If you dare, there's still Biztalk around :)

  • Cico71 (unregistered)
    Comment held for moderation.
  • Cico71 (unregistered) in reply to JDege
    JDege:
    In a number of business cases, that's very much not true. Consider an ordinary workforce management system. The dispatcher looks at a work order, and decides to assign it to a particular field worker. If, between the time dispatcher opens the work order, and assigns it to a field worker, some other dispatcher assigns it to a different field worker, the software must prevent the assignment.

    I see a dispatcher as something which is easily and elegantly handled with queues and messages, so in SQL Server with SQL Broker that provides transactional queuing and messaging in-line with all other DML operations.

    If you just need a simple queue, and don't wan't to fiddle with all the message-oriented model, just use locking and the READPAST query hint skip locked rows.

    If you just need to take a sort of high-level mutex, use sp_getapplock and sp_releaseapplock.

    Keep it simple and don't reinvent the wheel.

  • PiisAWheeL (cs) in reply to Ike
    Ike:
    I never meta-problem I couldn't solve.

    You win... as corny as that was it made me laugh.

  • Kasper (unregistered) in reply to jon
    jon:
    I wish I could have been there when someone asked "Hey, what happens if two processes try to update db__locks at the same time?".

    I assume he would start "That's simple. I'll just add ....." and then his face would slowly change, as he realized what the next question after that would be.

    I recently did something similar to a boy who was around 12 years old. Hopefully catching it that early prevents him from doing something stupid at a later time. I don't recall how we got to discuss locking in multi threaded programs, but he then suggested the idea of simply using a boolean variable to lock a data structure.

    So I asked him, what if two threads both read the boolean simultaneously and find that it is not locked, and then simultaneously proceed to change the value to indicate that it now is locked. From the look on his face it was obvious that he realized the solution wasn't that simple. And I told him most languages have features that do the hard work for you, just remember to use them.

  • Brian White (unregistered) in reply to jon
    jon:
    I wish I could have been there when someone asked "Hey, what happens if two processes try to update db__locks at the same time?".

    I assume he would start "That's simple. I'll just add ....." and then his face would slowly change, as he realized what the next question after that would be.

    update db__locks with(updlock) ? My main issue is... wtf you can only fake lock an ENTIRE table? I've been used to rowlocking since around 2000. This must be an extremely low volume site.

  • BentFranklin (cs) in reply to dtech
    dtech:
    So what are those 17 previous examples of where solving the meta-problem actually was effective?

    Content management systems might be one.

  • Stop right there (unregistered) in reply to Kasper
    Kasper:
    I recently did something similar to a boy who was around 12 years old. Hopefully catching it that early prevents him from doing something stupid at a later time.
  • Harrow (unregistered) in reply to Kasper

    You were a great mentor until you said this:

    Kasper:
    ...I told him most languages have features that do the hard work for you, just remember to use them.
    The kid was not trying to co-ordinate a couple of processes so he could meet his production deadline[1], he was trying to grok the fundementals of asynchronous locking. Your advice sounds like "don't worry about it, it's a solved problem."[2]

    -Harrow.


    [1] Unless you were talking to the Doogie Howser of application development. [2] Unless you actually told him to figure it out, maybe once, but not every time he needed it.

  • Junkyard Science (unregistered) in reply to An Old Hacker
    An Old Hacker:
    1) Enter the fact that you want a lock into a the queue. (Since we're talking databases, use an auto incrementing key.) 2) Wait until there is no one ahead of you in the queue. 3) Wait until the lock clears. 4) Write the lock with your id. 5) Do your stuff. 6) Clear your lock. 7) Remove yourself from the queue.
    Congratulations, you've just implemented a semaphore in a database... There are ways to handle locking and exclusiveness in ACID databases. Using home-brew is not one of them. Using any home-brew semaphore and locking mechanisms in a database is likely to be a significant cause if lock contention and therefore very poor performance.
  • Barf 4Eva (unregistered)

    DIE CODE DIE!

  • Barf 4Eva (unregistered)

    Steps on how to build a brick wall developers will love to bang their head against.

    1. Implement own table/row locking mechanism for database.
  • Andy Canfield (unregistered)

    Poster said "there are a whole lot of problems associated with built-in database transactions. First and foremost, they're built-in and using them would give programmers one less meta-problem to solve which would increase the boring factor by at least three. Secondly, built-in transactions ... uh ... -- okay, so that's the only problem I can think of right now."

    The two techniques are locking and transactions. Locking works like this: while ( ! GetLock() ) ; Perform operation; ReleaseLock();

    Transactions work like this: Perform operation; if ( ! Commit() ) What do I do now?

    The trouble with transactions is what to do when the commit fails. The trouble with locking is that it creates a tremendous amount of unnecessary overhead.

  • Brendan (unregistered) in reply to Andy Canfield

    Hi,

    Andy Canfield:
    The two techniques are locking and transactions. Locking works like this: while ( ! GetLock() ) ; Perform operation; ReleaseLock();

    Transactions work like this: Perform operation; if ( ! Commit() ) What do I do now?

    The trouble with transactions is what to do when the commit fails. The trouble with locking is that it creates a tremendous amount of unnecessary overhead.

    When your commit fails, you retry the work again (and again and again and again until the commit succeeds). You need a lock to guarantee that you don't waste time doing work that fails to commit (and avoid a tremendous amount of unnecessary overhead); but if you're using locks to prevent failed commits then you don't need transactions.

    In general; locks (when done right) are always at least as good as transactions or better than transactions in terms of performance/scalability. Transactions are always at least as good as locks or better than locks in terms of "correctness". Locks are for smart people that can get them right; and transactions are for where the increased overhead is justified because the smart people who can get locking right are working on other things. :-)

    • Brendan
  • Cico71 (unregistered) in reply to Brendan
    Brendan:
    In general; locks (when done right) are always at least as good as transactions or better than transactions in terms of performance/scalability.

    I'm not sure why you consider locks and transactions as two different means to accomplish a given thing.

    Lock managers are used to guarantee some transactions' properties and to enable higher concurrency at the cost of lower consistency (see transaction isolation levels).

    If you need serialized access, the lock manager can be quite simple to guarantee high consistency, but you will not get high concurrency.

    If you want higher concurrency, you need a more complex lock manager that support different isolation levels scenarios and (realistically) that supports escalation on a hierarchy of lockable resources.

    If you don't want consistency (oh, sorry, if you want "eventual" consistency) you will still need other kind of locking mechanism (e.g. latches) to arbitrate access to physical structures.

  • Planar (unregistered) in reply to DaveK
    Comment held for moderation.

Leave a comment on “Classic WTF: I Think I'll Call Them "Transactions"”

Log In or post as a guest

Replying to comment #:

« Return to Article