Update Transaction in a Client-Server Application

I am developing a small-scale client-server database application. The server creates a handler thread for each client request and uses connection pooling.

How do I perform update transactions (single rows updated by row key)? If I start a transaction and submit a SELECT ... FOR UPDATE followed -- some time later -- by an UPDATE statement and a commit, I'll be splitting my transaction in two. Can I do this? And if these two requests arrive via different db connections will the DBMS still accept the UPDATE and unlock the row?

> And if these two requests arrive via different db connections will the DBMS still accept the UPDATE and unlock the row?

No. Locking in MySQL is per-db-connection. If you lock something on one db connection, no other db connection can modify the locked object until the lock is released, else it wouldn't be a very useful lock. The lock is released by either explicitly unlocking (with a COMMIT or ROLLBACK), or closing the connection (implicitly doing a ROLLBACK).

I can think of two ways to approach this problem. One is to change your client-server protocol to include a command that is equivalent to compare-and-swap (http://en.wikipedia.org/wiki/Compare_and_swap). On the server you could implement this by something like this:
1. Client sends compare_and_set(key, oldvals, newvals) to server
2. Server does: "START TRANSACTION" then "SELECT mytable.* FOR UPDATE WHERE id = $key", starting a transaction, locking the row, and getting the current values of the row.
3. Server compares fields in mytable to oldvals. If they don't match, abort the transaction and return failure code to client
4. Current values and oldvals match, so server performs "UPDATE mytable SET .... WHERE id = $key" then "COMMIT". Server returns success code to client.

This would require clients to retry failed actions. This is often called "optimistic" locking, since most of the time you're lucky and don't have to retry there is little additional cost. But there's no guarantee that it will get done in a finite number of tries, so this strategy is optimistic and nondeterministic.

Another way to do it is to create an application-level lock table, giving clients explicit lock and unlock methods, and on the server side wrapping all client actions with a transaction that first checks the lock table to see if the action is permitted.

Thanks for a very helpful reply, but do we really need to invent a solution? Surely there must be a tried-and-tested technique that application developers use every day.

I used to write database apps years ago using Informix. Back then we did a SELECT ... FOR UPDATE at the outset to lock the row, allowed the user to make the edits, then did an UPDATE to change the database -- all wrapped in a single transaction. But that wasn't client-server so perhaps that's why this seems so much more problematic.

I inferred that you have a basically stateless client-server layer that concurrently accepts commands that cause small discrete reads or writes to the database, and that your server grabs a connection from a pool in order to individually service each action.

A typical database transaction involves a series of reads or writes, and it seemed like your architecture was not designed to maintain the concept of a session.

So I figured that you needed either a way to implement a small discrete modification event that is capable of fully supporting concurrency (compare-and-set), or a way to establish explicit application-level locks so that clients can create and manage their own sessions (an explicit lock table).

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …

Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…