One place for hosting & domains

      Transactions

      How To Use Transactions in MongoDB


      The author selected the Open Internet/Free Speech Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      A transaction is a sequence of database operations that will only succeed if every operation within the transaction has been executed correctly. Transactions have been an important feature of relational databases for many years, but have been mostly absent from document-oriented databases until recently. The nature of document-oriented databases — where a single document can be a robust, nested structure, containing embedded documents and arrays rather instead of only simple values — streamlines storing related data within a single document. As such, modifying multiple documents as part of a single logical operation is often unnecessary, limiting the need for transactions in many applications.

      There are, however, applications for which accessing and modifying multiple documents in a single operation with guaranteed integrity is required even with document-oriented databases. MongoDB introduced multi-document ACID transactions in version 4.0 of the database engine in order to meet the needs of such use cases. In this article, you’ll explore what transactions are, the ACID properties of a transaction, and how to use transactions in MongoDB.

      Prerequisites

      Because of the way they’re implemented in MongoDB, transactions can only be performed on MongoDB instances that are running as part of a larger cluster. This could either be a sharded database cluster or a replica set. If you have an existing sharded MongoDB cluster or replica set running that you can use for testing purposes, then you can go on to the next section to learn about ACID transactions.

      However, setting up a proper, functional replica set or a sharded MongoDB cluster requires you to have at least three running MongoDB instances, ideally running on separate servers. Additionally, this guide’s examples all involve working with a single node running as a member of a replica set. Rather than having you go through the work of provisioning multiple servers and configuring MongoDB on each of them only for you to use just one of them in this guide, you can convert a standalone MongoDB instance into a single-node replica set that you can use to practice running transactions.

      This guide’s first step outlines how to do this, so in order to complete this tutorial, you will only need the following:

      Understanding ACID Transactions

      A transaction is a set of database operations (such as reads and writes) that are executed in a sequential order and in an all-or-nothing manner. This means that for the results of running these operations to be saved within the database and visible outside the transaction to other database clients, all of the individual operations must succeed. If any of the operations fail to execute correctly, the transaction aborts and every change made from the beginning of the transaction will be undone. The database will then be restored to its previous state as if the operations never happened.

      To illustrate why transactions are crucial to database systems, imagine you work at a bank and you need to transfer money from Customer A to Customer B. This means you have to decrease the balance for the source account and increase the balance for the destination account at the same time.

      If either of the two operations fails individually and the other goes through, the banking records would become inconsistent. Either Customer B would get the money out of nowhere (if the balance of Customer A’s account was not decreased), or Customer A would lose money for no reason (if their balance was decreased, but Customer B wasn’t credited). To make sure the results are always consistent, both operations must be successful, or both must fail. Transactions are especially handy in situations like this, ensuring an all-or-nothing execution.

      The four properties of database transactions that ensure such complex operations can be safely and reliably performed, guaranteeing data validity despite errors or interruptions, are abbreviated as ACID: atomicity, consistency, isolation, and durability. If the database system can guarantee all four of them for a set of operations grouped in a transaction, it can also guarantee the database will be left in a valid state even in the event of unexpected errors in execution.

      • Atomicity means that all the actions in a transaction are treated as a single unit of work, and either all or none will be executed with nothing in between. The previous example of money debited from one account to be added to another highlights the atomicity principle. Note that, in MongoDB, updates within a single document (no matter how complex and nested the document structure is) are always atomic, even without using transactions. It’s only in cases when you’re dealing with more than one document that transactions provide stronger atomicity guarantees.

      • Consistency means that any changes made to a database must adhere to the database’s existing constraints, otherwise the whole transaction will fail. If, for example, one of the operations violated a unique index or a schema validation rule, MongoDB would abort the transaction.

      • Isolation is the idea that separate, concurrently running transactions are isolated from one another, and neither will affect the other’s outcomes. If two transactions are executed simultaneously, the isolation rule guarantees the end result will be the same as if they were executed one after another.

      • Durability guarantees that as soon as the transaction succeeds, the client can be sure the data has been properly persisted. Even something like hardware failure or a power interruption won’t void the transaction.

      Transactions in MongoDB comply with these ACID principles and can be reliably used in cases when it’s necessary to alter multiple documents in a single go.

      Step 1 — Converting Your Standalone MongoDB Instance into a Replica Set

      As mentioned previously, because of the way they’re implemented in MongoDB, transactions can only be performed on databases that are running as part of a larger cluster. This cluster can either be a sharded database cluster or a replica set.

      If you’ve already configured a replica set or sharded cluster that you can use to practice running transactions, you can skip this step and use that cluster in Step 2. If not, this step outlines how to convert a standalone MongoDB instance into a single-node replica set.

      Warning: A single-node replica set like the one you’ll configure in this step is useful for testing purposes, but it won’t be suitable for use in a production environment. The reason for this is that replica sets are meant to be run on multiple distributed nodes, as this helps to keep the database highly available: if any one node fails, there will still be others in the set that clients can connect to. This single-node set won’t have any of this redundancy, and should only be used for testing in situations that require the use of a replica set.

      If you’d like to learn more about replication in MongoDB and the security implications it involves, we strongly encourage you to check out our tutorial on How To Configure a MongoDB Replica Set on Ubuntu 20.04.

      To convert your standalone MongoDB instance into a replica set, begin by opening up the MongoDB configuration file using your preferred text editor. This example uses nano:

      • sudo nano /etc/mongod.conf

      Find the section that reads #replication: towards the bottom of this file:

      /etc/mongod.conf

      . . .
      #replication:
      . . .
      

      Uncomment this line by removing the pound sign (#). Then add a replSetName directive below this line followed by a name that MongoDB will use to identify the replica set:

      /etc/mongod.conf

      . . .
      replication:
        replSetName: "rs0"
      . . .
      

      In this example, the replSetName directive’s value is "rs0". You can provide whatever name you’d like here, but it can be helpful to use a descriptive name.

      Note: When replication is enabled, MongoDB also requires you to configure some means of authentication other than password authentication, like keyfile authentication or setting up x.509 certificates. If you followed our How To Secure MongoDB on Ubuntu 20.04 tutorial and enabled authentication on your MongoDB instance, you will only have password authentication enabled.

      Rather than setting up more advanced security measures, for the purposes of this tutorial it would be prudent to disable the security block in your mongod.conf file. Do so by commenting out every line in the security block with a pound sign:

      /etc/mongod.conf

      . . .
      
      #security:
      #  authorization: enabled
      
      . . .
      

      As long as you only plan to use this database for practicing transactions or other testing purposes, this won’t present a security risk. However, if you plan to use this MongoDB instance to store any sensitive data in the future, be sure to uncomment these lines to re-enable authentication.

      Those are the only changes you need to make to this file, so you can save and close it. If you used nano to edit the file, you can do so by pressing CTRL + X, Y, and then ENTER.

      Following that, restart the mongod service to implement the new configuration changes:

      • sudo systemctl restart mongod

      After restarting the service, open up the MongoDB shell to connect to the MongoDB instance running on your server:

      From the MongoDB prompt, run the following rs.initiate() method. This will turn your standalone MongoDB instance into a single-node replica set that you can use for testing:

      If this method returns "ok" : 1 in its output, it means the replica set was started successfully:

      Output

      { . . . "ok" : 1, . . .

      Assuming this is the case, your MongoDB shell prompt will change to indicate that the instance the shell is connected to is now a member of the rs0 replica set:

      Note that this example prompt reflects that this MongoDB instance is a secondary member of the replica set. This is to be expected, as there is usually a gap between the time when a replica set is initiated and the time when one of its members is promoted to become the primary member.

      If you were to run a command or even just press ENTER after waiting a few moments, the prompt will update to reflect that you’re connected to the replica set’s primary member:

      Your standalone MongoDB instance is now running as a single-node replica set that you can use for testing transactions. Keep the prompt open for now, as you’ll use the MongoDB shell in the next step to create an example collection and insert some sample data into it.

      Step 2 — Preparing the Sample Data

      In order to explain how transactions in MongoDB work and how to use them, this step outlines how to open the MongoDB shell to connect to your replica set’s primary node. It also explains how to create a sample collection and insert a few sample documents into it. This guide will use this sample data to illustrate how to initiate and execute transactions.

      If you skipped the previous step because you had an existing sharded MongoDB cluster or replica set, connect to any node that you can write data to:

      Note: On a fresh connection, the MongoDB shell will automatically connect to the test database by default. You can safely use this database to experiment with MongoDB and the MongoDB shell.

      Alternatively, you could also switch to another database to run all of the example commands given in this tutorial. To switch to another database, run the use command followed by the name of your database:

      To understand the behavior of transactions, you’ll need a set of documents to work with. This guide will use a collection documents representing a few of the most populated cities in the world. As an example, the following sample document represents Tokyo:

      The Tokyo document

      {
          "name": "Tokyo",
          "country": "Japan",
          "continent": "Asia",
          "population": 37.400
      }
      

      This document contains the following information:

      • name: the city’s name.
      • country: the country where the city is located.
      • continent: the continent where the city is located.
      • population: the city’s population, in millions.

      Run the following insertMany() method, which will simultaneously create a collection named cities and insert three documents into it:

      • db.cities.insertMany([
      • {"name": "Tokyo", "country": "Japan", "continent": "Asia", "population": 37.400 },
      • {"name": "Delhi", "country": "India", "continent": "Asia", "population": 28.514 },
      • {"name": "Seoul", "country": "South Korea", "continent": "Asia", "population": 25.674 }
      • ])

      The output will contain a list of object identifiers assigned to the newly inserted objects:

      Output

      { "acknowledged" : true, "insertedIds" : [ ObjectId("61646915c66c110cc07ca59b"), ObjectId("61646915c66c110cc07ca59c"), ObjectId("61646915c66c110cc07ca59d") ] }

      You can verify that the documents were properly inserted by running the find() method with no arguments, which will retrieve every document in the cities collection:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 }

      Lastly, use the createIndex() method to create an index that will ensure every document in the collection has a unique name field value. This will be helpful for testing consistency requirements when running transactions later on in this guide:

      • db.cities.createIndex( { "name": 1 }, { "unique": true } )

      MongoDB will confirm that the index was created successfully:

      Output

      { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "commitQuorum" : "votingMembers", "ok" : 1, . . . }

      With that, you have successfully created the list of example documents of the most populated cities that will serve as the test data for testing the use of transactions. Next, you’ll learn how to set up a transaction.

      Step 3 — Creating Your First Complete Transaction

      This step outlines how to create a transaction consisting of a single operation that will insert a new document into the sample collection from the previous step.

      Begin by opening two separate MongoDB shell sessions. One will be used to execute commands in the transaction, and the other will allow you to check what data is available to other users of the database outside the transaction at different points in time.

      Note: To help keep things clear, this guide will use different-colored code blocks to distinguish between these two environments. The first instance, which you’ll use to execute transactions, will have a blue background, like this:

      The second instance will be outside of the transaction, allowing you to check how any changes you make within the transaction are visible to clients outside of it. This second environment will have a red background, like this:

      At this point, both shell sessions should list the three cities you inserted before if you query the cities collection. Verify that by issuing a find() query in both shell sessions, starting with the first one:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 }

      Then run the same query in your second shell session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 }

      After confirming that this query’s output is consistent in both sessions, try to insert a new document into the collection. However, instead of using an insertOne() method, you’ll insert this document as part of a transaction.

      Typically, transactions aren’t written and executed from the MongoDB Shell, as this guide outlines. More often than not, transactions are instead used by external applications. To ensure that any transactions it runs remain atomic, consistent, isolated, and durable, the application must start a session.

      In MongoDB, a session is a database object managed by an application through the appropriate MongoDB driver. This allows the driver to associate a sequence of database statements with one another, meaning they will have a shared context and can have additional configurations applied to them as a group, like enabling the use of transactions. What happens inside a single session might not be immediately visible to the outside world, as this step will illustrate.

      Rather than setting up an external application, this tutorial outlines the concepts and individual steps needed to work with a transaction directly in the MongoDB shell with a simplified JavaScript syntax.

      Note: You can learn more on how to use transactions using different programming languages in the official MongoDB documentation. The code examples described in the official documentation will be more complex than what’s included in this guide, but both methods follow the same principles.

      Even though this guide outlines how to use transactions through the MongoDB shell rather than in an application, it’s still necessary to start a session in order to execute a set of operations as a transaction. You can start a session with the following command:

      • var session = db.getMongo().startSession()

      This command creates a session variable that will store the session object. Each time you refer to the session object in the following examples, you’re referring to the session you’ve just started.

      With this session object available, you can start the transaction by calling the startTransaction method as follows:

      • session.startTransaction({
      • "readConcern": { "level": "snapshot" },
      • "writeConcern": { "w": "majority" }
      • })

      Notice that the method is called on the session variable and not db, as the commands in the previous step did.

      The startTransaction() method accepts two options: readConcern and writeConcern. The writeConcern setting can accept a few options, but this example only includes the w option, which requests that the cluster acknowledges when the transaction’s write operations have been accepted on a specified number of nodes in the cluster. Instead of a single number, this example specifies that the transaction will only be considered to have been saved successfully when a majority of nodes acknowledge the write operation.

      Say that you start a transaction, but after you doing so another user adds a document to the collection you’re using on another node in the cluster. Should your transaction read this new data or only the data written on the node on which the transaction was started? Setting the readConcern level allows you to specify which data the transaction should read when you commit the transaction. Setting it to snapshot means that the transaction will read a snapshot of data that has been committed by a majority of nodes in the cluster.

      Note that setting the transaction’s readConcern level requires you to set the writeConcern to majority. These values for read and write concerns are safe defaults to follow in most cases. They provide reliable guarantees of data persistence unless you have very particular requirements on performance and acknowledging writes across the replica set. You can learn more about different write and read concerns that MongoDB provides for use in transactions in the official MongoDB documentation.

      The startTransaction method won’t return any output if it executed correctly. If this method was successful, you’ll be inside a running transaction and you can begin executing statements that will become part of the transaction.

      Warning: By default, MongoDB will automatically abort any transaction that runs for more than 60 seconds. The reason for this is that transactions are not designed to be constructed interactively in the MongoDB shell but rather used in real-world applications.

      Because of this, you might encounter unexpected errors while following this tutorial if you don’t execute each command within the 60 second time limit. If you encounter an error like the following, it means that MongoDB has aborted the transaction because the time limit has been exceeded:

      Error message

      Error: error: {
              "errorLabels" : [
                      "TransientTransactionError"
              ],
              "operationTime" : Timestamp(1634032826, 1),
              "ok" : 0,
              "errmsg" : "Transaction 1 has been aborted.",
              "code" : 251,
              "codeName" : "NoSuchTransaction",
              "$clusterTime" : {
                      "clusterTime" : Timestamp(1634032826, 1),
                      "signature" : {
                              "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                              "keyId" : NumberLong(0)
                      }
              }
      }
      

      If this happens, you have to mark the transaction as having been terminated by running the abortTransaction() method, like so:

      • session.abortTransaction()

      You’ll then have to reinitialize the transaction with the same startTransaction() method you ran previously:

      • session.startTransaction({
      • "readConcern": { "level": "snapshot" },
      • "writeConcern": { "w": "majority" }
      • })

      Following that, you’ll need to execute each statement in the transaction again from the beginning. With this in mind, it may be helpful for you to first read through the rest of this step and then execute the commands within the 60 second time limit once you better understand the concepts involved.

      While you’re working within the running transaction, any statements you run as part of the transaction must be within the shared context of the session represented by the session variable you created previously.

      To a similar end, when working in a running transaction it can be helpful to create another variable that represents the collection you want to work with in the context of the session. The following operation will create a variable called cities by returning the cities collection from the test database. However, instead of pulling this directly from the db object it references the session object to ensure that this variable represents the cities collection in the context of the running session:

      • var cities = session.getDatabase('test').getCollection('cities')

      From now until you commit the transaction, you can use the cities variable just like you would use db.cities to refer to the cities collection. This newly assigned variable will guarantee that you’re running statements in a session and, likewise, in the started transaction.

      Test this out by checking that the object can be used to find documents from the collection:

      The command will return the same list of documents as before since the data has not yet been altered:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 }

      Following that, insert a new document representing New York City into the collection as part of the running transaction. Use the insertOne method, but execute it on the cities variable to make sure it will be run in the session:

      • cities.insertOne({"name": "New York", "country": "United States", "continent": "North America", "population": 18.819 })

      MongoDB will return a success message:

      Output

      { "acknowledged" : true, "insertedId" : ObjectId("6164849d53abeea9d9dd10cf") }

      If you were to execute cities.find() again, you’ll notice that the newly inserted document is immediately visible in the same session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164822453abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      However, if you run db.cities.find() in your second MongoDB shell instance, the document representing New York will be absent:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 }

      The reason for this is that the insert statement has been executed inside the running transaction, but the transaction itself has not been committed yet. At this point, the transaction can still either succeed and persist the data, or it could fail, which would undo all the changes and leave the database in the same state as before you initiated the transaction.

      To commit the transaction and save the inserted document permanently to the database, run the commitTransaction method on the session object:

      • session.commitTransaction()

      As with startTransaction, this command gives no output if it succeeds.

      Now, list the documents from the cities collection in both MongoDB shells. Start with querying the cities variable in the running session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164822453abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Then query the cities collection in the second shell running outside of the transaction:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      This time, the newly inserted document is visible both inside the session and outside it. The transaction has been committed and ended successfully, persisting the changes it made to the database. You can now access the New York object both outside transactions and inside all further transactions.

      Now that you know how to start and execute a transaction, you can move on to the next step, which outlines how to abort a transaction after starting one to roll back any changes you’ve made before executing it. Be sure to keep both of your MongoDB shell environments open, as you’ll continue using both for the remainder of this tutorial.

      Step 4 — Aborting a Transaction

      This step follows a similar path to the previous one, in that it has you start a transaction in the same way. However, this step outlines how to abort the transaction instead of committing the changes. When doing so, all changes introduced by the transaction are rolled back, returning the database to its previous state as if the transaction never happened.

      After following the previous step, you’ll have four cities in the collection, including the newly added one representing New York.

      In the first MongoDB shell, start the session and assign it to the session variable again:

      • var session = db.getMongo().startSession()

      Then start the transaction:

      • session.startTransaction({
      • "readConcern": { "level": "snapshot" },
      • "writeConcern": { "w": "majority" }
      • })

      Again, this method won’t return any output if it succeeds. If it is successful, you’ll be inside a running transaction.

      You will again need access to the cities collection inside the session context. You can do this by again creating a cities variable to represent the cities collection inside the session:

      • var cities = session.getDatabase('test').getCollection('cities')

      From now on, you can use cities variable to act on the cities collection in the context of the session.

      Now that the transaction is started, insert another new document into this collection as part of the running transaction. The document in this example will represent Buenos Aires. Use the insertOne method, but execute it on the cities variable to ensure it will be run in the session:

      • cities.insertOne({"name": "Buenos Aires", "country": "Argentina", "continent": "South America", "population": 14.967 })

      MongoDB will return the success message:

      Output

      { "acknowledged" : true, "insertedId" : ObjectId("6164887e322518cf706858b5") }

      Next, run the cities.find() query:

      Notice that the newly inserted document is immediately visible in the same session within the transaction:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 } { "_id" : ObjectId("6164887e322518cf706858b5"), "name" : "Buenos Aires", "country" : "Argentina", "continent" : "South America", "population" : 14.967 }

      However, if you were to query the cities collection in your second MongoDB shell instance, which is not operating within the transaction, the returned list won’t contain the Buenos Aires document as the transaction hasn’t been committed:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Say you made a mistake and you no longer want to commit the transaction. Instead, you want to cancel any statements that you’ve run as part of this session and abort the transaction altogether. To do this, run the abortTransaction() method:

      • session.abortTransaction()

      The abortTransaction() method tells MongoDB to discard all changes introduced in the transaction and return the database to the previous state. As with startTransaction and commitTransaction, this command gives no output if it succeeds.

      After successfully aborting the transaction, list the documents from the cities collection in both MongoDB shells. First, run the following operation in the running session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Then run the following query in your second shell instance which is running outside of the session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Buenos Aires is absent from both lists. Aborting the transaction after the document was inserted but before the transaction was committed, it’s like inserting it never happened.

      In this step, you learned how to terminate a transaction and roll back the changes introduced during its existence. However, transactions aren’t always aborted manually like this. Oftentimes, the reason MongoDB will terminate a transaction before it can be executed is because one of the operations within the transaction caused an error.

      Step 5 — Aborting Transactions Due to Errors

      This step is similar to the previous one, but this time you’ll learn what happens when an error occurs during any of the statements executed inside the transaction.

      You should at this point still have two open shell sessions. Your collection holds four cities, including the newly added document representing New York. However, the document representing Buenos Aires was not inserted, as it was discarded when you aborted the transaction in the previous step.

      In the first MongoDB shell, start the session and assign it to the session variable:

      • var session = db.getMongo().startSession()

      Then start the transaction:

      • session.startTransaction({
      • "readConcern": { "level": "snapshot" },
      • "writeConcern": { "w": "majority" }
      • })

      Create the cities variable again:

      • var cities = session.getDatabase('test').getCollection('cities')

      Following that, insert another new document into this collection as part of the running transaction. This example inserts a document representing Osaka, Japan:

      • cities.insertOne({"name": "Osaka", "country": "Japan", "continent": "Asia", "population": 19.281 })

      MongoDB will return the success message:

      Output

      { "acknowledged" : true, "insertedId" : ObjectId("61648bb3322518cf706858b6") }

      The newly inserted city will immediately be visible from inside the transaction:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 } { "_id" : ObjectId("61648bb3322518cf706858b6"), "name" : "Osaka", "country" : "Japan", "continent" : "Asia", "population" : 19.281 }

      However, the Osaka document won’t be visible in the second shell since it is outside the transaction:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      The transaction is still running and can be used to perform further changes on the database.

      Run the following operation to try to insert one more document into the collection as part of this transaction. This command will create another document representing New York City. However, because of the uniqueness constraint you applied to the name field when setting up this collection, and because this collection already has a document whose name field’s value is New York, this insertOne operation will conflict with that constraint and cause an error:

      • cities.insertOne({"name": "New York", "country": "United States", "continent": "North America", "population": 18.819 })

      MongoDB will return the error message noting that this operation violated the unique constraint:

      Output

      WriteError({ "index" : 0, "code" : 11000, "errmsg" : "E11000 duplicate key error collection: test.cities index: name_1 dup key: { name: "New York" }", "op" : { "_id" : ObjectId("61648bdc322518cf706858b7"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 } }) . . .

      This output indicates that this new document representing New York wasn’t inserted into the database. However, this doesn’t explain what happened to the document representing Osaka that you previously added as part of the transaction.

      Say that attempting to add that second New York document was a mistake, but you did intend to keep the Osaka document in the collection. You might try committing the transaction to persist the Osaka document:

      • session.commitTransaction()

      MongoDB will not allow this and instead throw an error:

      Output

      uncaught exception: Error: command failed: { "errorLabels" : [ "TransientTransactionError" ], "operationTime" : Timestamp(1633979403, 1), "ok" : 0, "errmsg" : "Transaction 0 has been aborted.", "code" : 251, "codeName" : "NoSuchTransaction", "$clusterTime" : { "clusterTime" : Timestamp(1633979403, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } } . . .

      Any time an error occurs inside of a transaction it will cause MongoDB to automatically abort the transaction. Also, because transactions are executed in an all-or-nothing manner, no changes from within the transaction are persisted in such a case. The error caused by adding a second document representing New York caused MongoDB to abort the transaction and discard the document representing Osaka.

      You can verify this by running the find() query both in both shells. In the first shell, run the query within the context of the session:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Then in the second shell, running outside of the session, run find() against db.cities:

      Output

      { "_id" : ObjectId("61646915c66c110cc07ca59b"), "name" : "Tokyo", "country" : "Japan", "continent" : "Asia", "population" : 37.4 } { "_id" : ObjectId("61646915c66c110cc07ca59c"), "name" : "Delhi", "country" : "India", "continent" : "Asia", "population" : 28.514 } { "_id" : ObjectId("61646915c66c110cc07ca59d"), "name" : "Seoul", "country" : "South Korea", "continent" : "Asia", "population" : 25.674 } { "_id" : ObjectId("6164849d53abeea9d9dd10cf"), "name" : "New York", "country" : "United States", "continent" : "North America", "population" : 18.819 }

      Neither will show Osaka nor a duplicated New York entry. When MongoDB automatically aborted the transaction, it also made sure all changes were reverted. Osaka was briefly visible inside the transaction context but never was available outside the transaction to other database users.

      Conclusion

      By reading this article, you familiarized yourself with ACID principles and multi-document transactions in MongoDB. You initiated a transaction, inserted documents as part of that transaction, and learned when the document becomes visible inside and outside the transaction. You learned how to commit the transaction and how to abort it and roll back any changes, as well as what happens when an error occurs inside the transaction.

      With these new skills in hand, you can leverage the ACID guarantees of multi-document transactions in applications where they might be needed. Remember, though, that MongoDB is a document-oriented database. In many scenarios, the document model itself, as well as careful schema design, can reduce the need for working with multi-document transactions.

      The tutorial provided only a brief introduction to transactions in MongoDB. We encourage you to study the official official MongoDB documentation to learn more about how transactions work.



      Source link

      How To Use the PDO PHP Extension to Perform MySQL Transactions in PHP on Ubuntu 18.04


      The author selected Open Sourcing Mental Illness to receive a donation as part of the Write for DOnations program.

      Introduction

      A MySQL transaction is a group of logically related SQL commands that are executed in the database as a single unit. Transactions are used to enforce ACID (Atomicity, Consistency, Isolation, and Durability) compliance in an application. This is a set of standards that govern the reliability of processing operations in a database.

      Atomicity ensures the success of related transactions or a complete failure if an error occurs. Consistency guarantees the validity of the data submitted to the database according to defined business logic. Isolation is the correct execution of concurrent transactions ensuring the effects of different clients connecting to a database do not affect each other. Durability ensures that logically related transactions remain in the database permanently.

      SQL statements issued via a transaction should either succeed or fail altogether. If any of the queries fails, MySQL rolls back the changes and they are never committed to the database.

      A good example to understand how MySQL transactions work is an e-commerce website. When a customer makes an order, the application inserts records into several tables, such as: orders and orders_products, depending on the business logic. Multi-table records related to a single order must be atomically sent to the database as a single logical unit.

      Another use-case is in a bank application. When a client is transferring money, a couple of transactions are sent to the database. The sender’s account is debited and the receiver’s party account is credited. The two transactions must be committed simultaneously. If one of them fails, the database will revert to its original state and no changes should be saved to disk.

      In this tutorial, you will use the PDO PHP Extension, which provides an interface for working with databases in PHP, to perform MySQL transactions on an Ubuntu 18.04 server.

      Prerequisites

      Before you begin, you will need the following:

      Step 1 — Creating a Sample Database and Tables

      You’ll first create a sample database and add some tables before you start working with MySQL transactions. First, log in to your MySQL server as root:

      When prompted, enter your MySQL root password and hit ENTER to proceed. Then, create a database, for the purposes of this tutorial we’ll call the database sample_store:

      • CREATE DATABASE sample_store;

      You will see the following output:

      Output

      Query OK, 1 row affected (0.00 sec)

      Create a user called sample_user for your database. Remember to replace PASSWORD with a strong value:

      • CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

      Issue full privileges for your user to the sample_store database:

      • GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

      Finally, reload the MySQL privileges:

      You’ll see the following output once you’ve created your user:

      Output

      Query OK, 0 rows affected (0.01 sec) . . .

      With the database and user in place, you can now create several tables for demonstrating how MySQL transactions work.

      Log out from the MySQL server:

      Once the system logs you out, you will see the following output:

      Output

      Bye.

      Then, log in with the credentials of the sample_user you just created:

      • sudo mysql -u sample_user -p

      Enter the password for the sample_user and hit ENTER to proceed.

      Switch to the sample_store to make it the currently selected database:

      You’ll see the following output once it is selected:

      Output

      Database Changed.

      Next, create a products table:

      • CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

      This command creates a products table with a field named product_id. You use a BIGINT data type that can accommodate a large value of up to 2^63-1. You use this same field as a PRIMARY KEY to uniquely identify products. The AUTO_INCREMENT keyword instructs MySQL to generate the next numeric value as new products are inserted.

      The product_name field is of type VARCHAR that can hold up to a maximum of 50 letters or numbers. For the product price, you use a DOUBLE data type to cater for floating point formats in prices with decimal numbers.

      Lastly, you use the InnoDB as the ENGINE because it comfortably supports MySQL transactions as opposed to other storage engines such as MyISAM.

      Once you’ve created your products table, you’ll get the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Next, add some items to the products table by running the following commands:

      • INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
      • INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
      • INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
      • INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

      You’ll see output similar to the following after each INSERT operation:

      Output

      Query OK, 1 row affected (0.02 sec) . . .

      Then, verify that the data was added to the products table:

      You will see a list of the four products that you have inserted:

      Output

      +------------+-------------------+-------+ | product_id | product_name | price | +------------+-------------------+-------+ | 1 | WINTER COAT | 25.5 | | 2 | EMBROIDERED SHIRT | 13.9 | | 3 | FASHION SHOES | 45.3 | | 4 | PROXIMA TROUSER | 39.95 | +------------+-------------------+-------+ 4 rows in set (0.01 sec)

      Next, you’ll create a customers table for holding basic information about customers:

      • CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

      As in the products table, you use the BIGINT data type for the customer_id and this will ensure the table can support a lot of customers up to 2^63-1 records. The keyword AUTO_INCREMENT increments the value of the columns once you insert a new customer.

      Since the customer_name column accepts alphanumeric values, you use VARCHAR data type with a limit of 50 characters. Again, you use the InnoDB storage ENGINE to support transactions.

      After running the previous command to create the customers table, you will see the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      You’ll add three sample customers to the table. Run the following commands:

      • INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
      • INSERT INTO customers(customer_name) VALUES ('ROE MARY');
      • INSERT INTO customers(customer_name) VALUES ('DOE JANE');

      Once the customers have been added, you will see an output similar to the following:

      Output

      Query OK, 1 row affected (0.02 sec) . . .

      Then, verify the data in the customers table:

      You’ll see a list of the three customers:

      Output

      +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JOHN DOE | | 2 | ROE MARY | | 3 | DOE JANE | +-------------+---------------+ 3 rows in set (0.00 sec)

      Next, you’ll create an orders table for recording orders placed by different customers. To create the orders table, execute the following command:

      • CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

      You use the column order_id as the PRIMARY KEY. The BIGINT data type allows you to accommodate up to 2^63-1 orders and will auto-increment after each order insertion. The order_date field will hold the actual date and time the order is placed and hence, you use the DATETIME data type. The customer_id relates to the customers table that you created previously.

      You will see the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Since a single customer’s order may contain multiple items, you need to create an orders_products table to hold this information.

      To create the orders_products table, run the following command:

      • CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

      You use the ref_id as the PRIMARY KEY and this will auto-increment after each record insertion. The order_id and product_id relate to the orders and the products tables respectively. The price column is of data type DOUBLE in order to accommodate floating values.

      The storage engine InnoDB must match the other tables created previously since a single customer’s order will affect multiple tables simultaneously using transactions.

      Your output will confirm the table’s creation:

      Output

      Query OK, 0 rows affected (0.02 sec)

      You won’t be adding any data to the orders and orders_products tables for now but you’ll do this later using a PHP script that implements MySQL transactions.

      Log out from the MySQL server:

      Your database schema is now complete and you’ve populated it with some records. You’ll now create a PHP class for handling database connections and MySQL transactions.

      Step 2 — Designing a PHP Class to Handle MySQL Transactions

      In this step, you will create a PHP class that will use PDO (PHP Data Objects) to handle MySQL transactions. The class will connect to your MySQL database and insert data atomically to the database.

      Save the class file in the root directory of your Apache web server. To do this, create a DBTransaction.php file using your text editor:

      • sudo nano /var/www/html/DBTransaction.php

      Then, add the following code to the file. Replace PASSWORD with the value you created in Step 1:

      /var/www/html/DBTransaction.php

      <?php
      
      class DBTransaction
      {
          protected $pdo;
          public $last_insert_id;
      
          public function __construct()
          {
              define('DB_NAME', 'sample_store');
              define('DB_USER', 'sample_user');
              define('DB_PASSWORD', 'PASSWORD');
              define('DB_HOST', 'localhost');
      
              $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
              $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
          }
      

      Toward the beginning of the DBTransaction class, the PDO will use the constants (DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD) to initialize and connect to the database that you created in step 1.

      Note: Since we are demonstrating MySQL transactions in a small scale here, we have declared the database variables in the DBTransaction class. In a large production project, you would normally create a separate configuration file and load the database constants from that file using a PHP require_once statement.

      Next, you set two attributes for the PDO class:

      • ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: This attribute instructs PDO to throw an exception if an error is encountered. Such errors can be logged for debugging.
      • ATTR_EMULATE_PREPARES, false: This option disables emulation of prepared statements and allows the MySQL database engine to prepare the statements itself.

      Now add the following code to your file to create the methods for your class:

      /var/www/html/DBTransaction.php

      . . .
          public function startTransaction()
          {
              $this->pdo->beginTransaction();
          }
      
          public function insertTransaction($sql, $data)
          {
              $stmt = $this->pdo->prepare($sql);
              $stmt->execute($data);
              $this->last_insert_id = $this->pdo->lastInsertId();
          }
      
          public function submitTransaction()
          {
              try {
                  $this->pdo->commit();
              } catch(PDOException $e) {
                  $this->pdo->rollBack();
                  return false;
              }
      
                return true;
          }
      }
      

      Save and close the file by pressing CTRL + X, Y, then ENTER.

      To work with MySQL transactions, you create three main methods in the DBTransaction class; startTransaction, insertTransaction, and submitTransaction.

      • startTransaction: This method instructs PDO to start a transaction and turns auto-commit off until a commit command is issued.

      • insertTransaction : This method takes two arguments. The $sql variable holds the SQL statement to be executed while the $data variable is an array of the data to be bound to the SQL statement since you’re using prepared statements. The data is passed as an array to the insertTransaction method.

      • submitTransaction : This method commits the changes to the database permanently by issuing a commit() command. However, if there is an error and the transactions have a problem, the method calls the rollBack() method to revert the database to its original state in case a PDO exception is raised.

      Your DBTransaction class initializes a transaction, prepares the different SQL commands to be executed, and finally commits the changes to the database atomically if there are no issues, otherwise, the transaction is rolled back. In addition, the class allows you to retrieve the record order_id you just created by accessing the public property last_insert_id.

      The DBTransaction class is now ready to be called and used by any PHP code, which you’ll create next.

      Step 3 — Creating a PHP Script to Use the DBTransaction Class

      You’ll create a PHP script that will implement the DBTransaction class and send a group of SQL commands to the MySQL database. You’ll mimic the workflow of a customer’s order in an online shopping cart.

      These SQL queries will affect the orders and the orders_products tables. Your DBTransaction class should only allow changes to the database if all of the queries are executed without any errors. Otherwise, you’ll get an error back and any attempted changes will roll back.

      You are creating a single order for the customer JOHN DOE identified with customer_id 1. The customer’s order has three different items with differing quantities from the products table. Your PHP script takes the customer’s order data and submits it into the DBTransaction class.

      Create the orders.php file:

      • sudo nano /var/www/html/orders.php

      Then, add the following code to the file:

      /var/www/html/orders.php

      <?php
      
      require("DBTransaction.php");
      
      $db_host = "database_host";
      $db_name = "database_name";
      $db_user = "database_user";
      $db_password = "PASSWORD";
      
      $customer_id = 2;
      
      $products[] = [
        'product_id' => 1,
        'price' => 25.50,
        'quantity' => 1
      ];
      
      $products[] = [
        'product_id' => 2,
        'price' => 13.90,
        'quantity' => 3
      ];
      
      $products[] = [
        'product_id' => 3,
        'price' => 45.30,
        'quantity' => 2
      ];
      
      $transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);
      

      You’ve created a PHP script that initializes an instance of the DBTransaction class that you created in Step 2.

      In this script, you include the DBTransaction.php file and you initialize the DBTransaction class. Next, you prepare a multi-dimensional array of all the products the customer is ordering from the store. You also invoke the startTransaction() method to start a transaction.

      Next add the following code to finish your orders.php script:

      /var/www/html/orders.php

      . . .
      $order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
      $product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";
      
      $transaction->insertQuery($order_query, [
        'customer_id' => $customer_id,
        'order_date' => "2020-01-11",
        'order_total' => 157.8
      ]);
      
      $order_id = $transaction->last_insert_id;
      
      foreach ($products as $product) {
        $transaction->insertQuery($product_query, [
          'order_id' => $order_id,
          'product_id' => $product['product_id'],
          'price' => $product['price'],
          'quantity' => $product['quantity']
        ]);
      }
      
      $result = $transaction->submit();
      
      if ($result) {
          echo "Records successfully submitted";
      } else {
          echo "There was an error.";
      }
      
      

      Save and close the file by pressing CTRL + X, Y, then ENTER.

      You prepare the command to be inserted to the orders table via the insertTransaction method. After this, you retrieve the value of the public property last_insert_id from the DBTransaction class and use it as the $order_id.

      Once you have an $order_id, you use the unique ID to insert the customer’s order items to the orders_products table.

      Finally, you call the method submitTransaction to commit the entire customer’s order details to the database if there are no problems. Otherwise, the method submitTransaction will rollback the attempted changes.

      Now you’ll run the orders.php script in your browser. Run the following and replace your-server-IP with the public IP address of your server:

      http://your-server-IP/orders.php

      You will see confirmation that the records were successfully submitted:

      PHP Output from MySQL Transactions Class

      Your PHP script is working as expected and the order together with the associated order products were submitted to the database atomically.

      You’ve run the orders.php file on a browser window. The script invoked the DBTransaction class which in turn submitted the orders details to the database. In the next step, you will verify if the records saved to the related database tables.

      Step 4 — Confirming the Entries in Your Database

      In this step, you’ll check if the transaction initiated from the browser window for the customer’s order was posted to the database tables as expected.

      To do this, log in to your MySQL database again:

      • sudo mysql -u sample_user -p

      Enter the password for the sample_user and hit ENTER to continue.

      Switch to the sample_store database:

      Ensure the database is changed before proceeding by confirming the following output:

      Output

      Database Changed.

      Then, issue the following command to retrieve records from the orders table:

      This will display the following output detailing the customer’s order:

      Output

      +----------+---------------------+-------------+-------------+ | order_id | order_date | customer_id | order_total | +----------+---------------------+-------------+-------------+ | 1 | 2020-01-11 00:00:00 | 2 | 157.8 | +----------+---------------------+-------------+-------------+ 1 row in set (0.00 sec)

      Next, retrieve the records from the orders_products table:

      • SELECT * FROM orders_products;

      You’ll see output similar to the following with a list of products from the customer’s order:

      Output

      +--------+----------+------------+-------+----------+ | ref_id | order_id | product_id | price | quantity | +--------+----------+------------+-------+----------+ | 1 | 1 | 1 | 25.5 | 1 | | 2 | 1 | 2 | 13.9 | 3 | | 3 | 1 | 3 | 45.3 | 2 | +--------+----------+------------+-------+----------+ 3 rows in set (0.00 sec)

      The output confirms that the transaction was saved to the database and your helper DBTransaction class is working as expected.

      Conclusion

      In this guide, you used the PHP PDO to work with MySQL transactions. Although this is not a conclusive article on designing an e-commerce software, it has provided an example for using MySQL transactions in your applications.

      To learn more about the MySQL ACID model, consider visiting the InnoDB and the ACID Model guide from the official MySQL website. Visit our MySQL content page for more related tutorials, articles, and Q&A.



      Source link

      How To Run Transactions in Redis


      Introduction

      Redis is an open-source, in-memory key-value data store. Redis allows you to plan a sequence of commands and run them one after another, a procedure known as a transaction. Each transaction is treated as an uninterrupted and isolated operation, which ensures data integrity. Clients cannot run commands while a transaction block is being executed

      This tutorial goes over how to execute and cancel transactions, and also includes some information on pitfalls commonly associated with transactions.

      How To Use This Guide
      This guide is written as a cheat sheet with self-contained examples. We encourage you to jump to any section that is relevant to the task you’re trying to complete.

      The commands shown in this guide were tested on an Ubuntu 18.04 server running Redis version 4.0.9. To obtain a similar setup, you can follow Step 1 of our guide on How To Install and Secure Redis on Ubuntu 18.04. We will demonstrate how these commands behave by running them with redis-cli, the Redis command line interface. Note that if you’re using a different Redis interface — Redli, for example — the exact output of certain commands may differ.

      Alternatively, you could provision a managed Redis database instance to test these commands, but note that depending on the level of control allowed by your database provider, some commands in this guide may not work as described. To provision a DigitalOcean Managed Database, follow our Managed Databases product documentation. Then, you must either install Redli or set up a TLS tunnel in order to connect to the Managed Database over TLS.

      Running Transactions

      The multi command tells Redis to begin a transaction block. Any subsequent commands will be queued up until you run an exec command, which will execute them.

      The following commands form a single transaction block. The first command initiates the transaction, the second sets a key holding a string with the value of 1, the third increases the value by 1, the fourth increases its value by 40, the fifth returns the current value of the string, and the last one executes the transaction block:

      • multi
      • set key_MeaningOfLife 1
      • incr key_MeaningOfLife
      • incrby key_MeaningOfLife 40
      • get key_MeaningOfLife
      • exec

      After running multi, redis-cli will respond to each of the following commands with QUEUED. After you run the exec command, it will show the output of each of those commands individually:

      Output

      1) OK 2) (integer) 2 3) (integer) 42 4) "42"

      Commands included in a transaction block are run sequentially in the order they’re queued. Redis transactions are atomic, meaning that either every command in a transaction block is processed (meaning that it’s accepted as valid and queued to be executed) or none are. However, even if a command is successfully queued, it may still produce an error when executed. In such cases, the other commands in the transaction can still run, but Redis will skip the error-causing command. See the section on understanding transaction errors for more details.

      Canceling Transactions

      To cancel a transaction, run the discard command. This prevents any previously-queued commands from running:

      • multi
      • set key_A 146
      • incrby key_A 10
      • discard

      Output

      OK

      The discard command returns the connection to a normal state, which tells Redis to run single commands as usual. You’ll need to run multi again to tell the server you’re starting another transaction.

      Understanding Transaction Errors

      Some commands may be impossible to queue, such as commands with syntax errors. If you attempt to queue a syntactically incorrect command Redis will return an error.

      The following transaction createst a key named key_A and then attempts to increment it by 10. However, a spelling error in the incrby command causes and error and closes the transaction:

      • multi
      • set key_A 146
      • incrbuy key_A 10

      Output

      (error) ERR unknown command 'incrbuy'

      If you try to run an exec command after trying to queue a command with a syntax error like this one, you will receive another error message telling you that the transaction was discarded:

      Output

      (error) EXECABORT Transaction discarded because of previous errors.

      In cases like this, you’ll need to restart the transaction block and make sure you enter each command correctly.

      Some impossible commands are possible to queue, such as running incr on a key containing only a string. Because such command is syntactically correct, Redis won’t return an error if you try to include it in a transaction and won’t prevent you from running exec. In cases like this, all other commands in the queue will be executed, but the impossible command will return an error:

      • multi
      • set key_A 146
      • incrby key_A "ten"
      • exec

      Output

      1) OK 2) (error) ERR value is not an integer or out of range

      For more information on how Redis handles errors inside transactions, see the official documentation on the subject.

      Conclusion

      This guide details a number of commands used to create, run, and cancel transactions in Redis. If there are other related commands, arguments, or procedures you’d like to see outlined in this guide, please ask or make suggestions in the comments below.

      For more information on Redis commands, see our tutorial series on How to Manage a Redis Database.



      Source link