Solved

Update Transaction in a Client-Server Application

Posted on 2008-06-25
3
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
NovaDenizen earned 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
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…

617 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