Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Transaction in a Client-Server Application

Posted on 2008-06-25
3
Medium Priority
?
310 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:pralteb
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
NovaDenizen earned 2000 total points
ID: 21876065
> 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.
0
 

Author Comment

by:pralteb
ID: 21878087
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.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 21878456
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).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

916 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