Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Transaction in a Client-Server Application

Posted on 2008-06-25
3
Medium Priority
?
309 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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