This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of Transfer Learning for Natural Language Processing (MEAP) and have Paul Azunre on-line!
See this thread for details.
Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

MySQL prepared statement - ensuring update statements always occur in the correct order.

 
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am swapping two dates so I want to ensure that the prepared statement updates occur in the correct order (i.e., multi-threading and system performance do not affect the order). This is hard to test (i.e., it is obvious if it goes wrong in the test environment; however, sometime it does not go wrong until it is in the production environment which has a different performance) so I ask my learned colleagues will the following always occur in the correct order:





I am also very open to a better way of coding this.

Kind regards,

Glyn
 
Marshal
Posts: 25436
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Those three updates will be executed one by one in the order that you see them in the code. That shouldn't be an issue in any environment.

However perhaps that code is being executed simultaneously in two different threads or in two different processes. If that were the case then the two could interact destructively. To fix that you could wrap the three updates in a transaction.
 
Glyndwr Bartlett
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Sorry; however, your reply does not make sense to me. I read it that they will be executed one by one in the correct order; however, they may be executed simultaneously. How can both occur?

I can not find any information on how to wrap the three updates in a transaction. The only thing I found was to place "START TRANSACTION;" before the the three updates and then "COMMIT;" after them. However, that gives an error "START can not be resolved to a type".

This is my full code:



Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 25436
65
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Glyndwr Bartlett wrote:Sorry; however, your reply does not make sense to me. I read it that they will be executed one by one in the correct order; however, they may be executed simultaneously. How can both occur?



Suppose you and I are given the instructions to do those three things to the database. So maybe you do the first thing and then the second thing. And then I nip in and do the first thing. And then you do the third thing, and then I do the second and third thing.

The end result is that the steps are executed in this order: first, second, first, third, second, third. This is likely going to make a mess of the database.

I'm assuming (from the fact that you report that a mess was made of the database) that your production system is running several threads, or several processes, which all update the same database. If you don't know whether that is true or not then you need to go and find out. There's no point in trying to fix problems if you don't know the basics of how the system works.

The only thing I found was to place "START TRANSACTION;" before the the three updates and then "COMMIT;" after them. However, that gives an error "START can not be resolved to a type".



Well, of course you need to start and commit the transaction in Java code. You're writing in Java after all. This means finding the JDBC methods which do that. I expect it's the Connection which deals with transactions.
 
Glyndwr Bartlett
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

OK, a slight misunderstanding, resulting from my poorly written question. This is not in production yet, I just want to make sure that it will not go wrong in production (I am trying to be preemptive). Secondly, this is an Event with a program each day (this code allows the owner to move the programs from one day to another). The person who created the Event is the only who can update the daily programs. So, therefore, I take it that the three will always sets of statements will always be executed in the correct order, as no one else will be performing this task at the same time?

Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 25436
65
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a basic rule of Java. Java statements are always executed one after another in the order they appear in the source code.
 
Whose rules are you playing by? This tiny ad doesn't respect those rules:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic