Transactions - Explain like I'm 5

Ignition
1

Gents/ladies,

I’m now on the subject of “Transactions” in my Ignition Gateway journey and even though I went through first module I don’t understand what this module is all about.

Could you explain to a basic blue collar worker in the industrial automation field what benefit and functionality the transaction bring to the table? (also I’m not experienced in sql).

Thanks very much.

2

Like you’re five …

You have a toy car. I have some chocolate. We want to swap.

  • If I give you my chocolate first you might not give me the car.
  • If you give me the car first I might not give you the chocolate.
  • If we exchange at the same time - a transaction - then we should be safe.

Databases can have the same problem - moving money between accounts, for example - so we use transactions to do the task completely or not at all.

Ignition has a historian. It usually saves data in a “narrow” column format.

# tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp
  '431',      '24',      NULL,        NULL,      NULL,         '192', '1651361074677'
   '11',      NULL,   3.14159,        NULL,      NULL,         '192', '1651361075007'
  '109',      NULL,      NULL,    "Mixing",      NULL,         '192', '16513610763321'
  • The tag id (which can be used to look up the tag name in another table).
  • The table can take integers, floats, strings and date types. These will be stored in the appropriate column.
  • The dataintegrity stores the OPC quality of the data. 192 signifies valid data.
  • The t_stamp is the milliseconds since 1970-01-01.

While this can be very useful it can sometimes be difficult to match up all the events that happened at a particular time because each record will have a (slightly) different timestamp. Transactions allow us to record in a “wide” format and to allow us to design the table to suit. For example:

#         timeStamp,  stopCause, speed, temperature, count
2022-05-01 22:07:07,        455,   7.3,        44.9,  2910
2022-05-01 22:24:07,        402,  17.7,        45.3,  3210

Now it’s very easy to retrieve all the records for that event.

The transaction manager makes it easy to record rows based on time intervals or events.

2 Likes
3

Imagine if you wanted to buy a new car. The steps could be:

  1. Check bank balance for enough money for car
  2. If enough money, Go to dealer
  3. Pay for new car

But at the same time you want to do this your wife is thinking, I want a new set of appliances for the kitchen. Her task list would be:

  1. Check bank balance for enough money for appliances
  2. If enough money, Go to hardware store
  3. Pay for new appliances

Consider that each step is an independent unit of work, and both you and your wife are attempting to execute your own task lists. Without transactions, all of the steps could be mixed up so that the total list of tasks gets executed in this order:

  1. Check bank balance for enough money for car
  2. Check bank balance for enough money for appliances
  3. If enough money, Go to hardware store
  4. If enough money, Go to dealer
  5. Pay for new appliances
  6. Pay for new car

So that while you started the car buying process first, your wife completed her complete task list before you did. This means that when you get to paying for the car, your wife has already drained the account of all your money and the finance guy at the dealership is about to kick you out.

This is where transactions come in. One aspect of them is that they basically “wrap” several independent items of work into one big work item. And that the system executes work items. So you now have 2 transactions:

  • Car Buying Transaction
  • Appliance buying transaction.

And that once the system starts executing the Car Buying transaction, the Appliance buying transaction can’t start until it is completed. So you will get your car if you start your process first. Or your wife will get her appliances if she starts first.

This sort of thing helps in keeping databases consistent by ensuring that complete sets of work items get processed as if they were a single work item. Another benefit of transactions is that if you get halfway through one and realize there is an error, you can roll back all the previous actions in the transaction as if nothing had happened. EG if the Car transaction was

  1. Drive to Dealer
  2. Check account balance
  3. Buy Car

And you got to step 2 and discovered that you didn’t have enough money for a new car, you could roll back the steps to the point that it seemed like you never left home in the first place.

Here is a tutorial on transactions, and another tutorial

4 Likes
4

@peter gave a good explanation

The only thing I have to add is the end goal of making turning multiple db queries into a transaction is to help keep your database in a state that you expect/keep it out of states that you want to avoid.

In the above example, if you made a “shop” in a Vision client, and you had the wife/husband buying two items at once but they only have enough money for one, and you don’t have any database constraints about account values being non-zero and you don’t have transactions to make one thing go through and then then next, etc, then what would happen is you their account would just be massively negative - which is something you would (probably) want to prevent and avoid in the first place

Although its tangential (or perpendicular?) to Ignition, learning about how to use a database to enforce data constraints will help make your system more robust and once you learn a few techniques for handling database errors via scripting and things become a lot easier to code in regards to “failing gracefully” and handling expected errors.

1 Like
5

I forgot to say that given you wanted an explanation as if you were 5 years old, I should have started my answer with:

When a mommy and a daddy want to .....

6

Really, you shouldn’t be playing with HMIs at such a young age.

5 Likes
7

I guess you are talking about Transaction Groups in Ignition not the general topic of transactions in data base in the context of OLTP!

Transaction Groups is I guess unique to Ignition compared to other SCADA systems and it takes time to understand the concept to a newcomer in the beginning in my opinion!

The idea behind it is to be able to write and read selected tags values from OPC (PLC) to and from a data base as a time series of records or overwrite them on a single record periodically or on a trigger condition. This is different from historical data which stores individual tag data as a function of time whereas transaction groups take a given list of tag values directly from OPC and writes them in the DB per cycle. Such records of data are useful for generating reports of related items as a function of time whereas historical data is used for trends etc. You can also write these tag values from DB to OPC to initialize PLC to a given snapshot of data from DB.

Experts may please correct me if I am incorrect or wrong!