Simple Guide to SAP CAP Transactions and Rollbacks
Introduction
When building business applications, you often need to ensure that multiple database operations either all succeed or all fail together. We use transactions to deal with this. Here we demonstrate transactions, rollback, and error handling. The original article may be found here.
CAP automatically manages transactions for before/on/after handlers using req.context. You generally do not need to call cds.tx() manually inside service handlers, as the framework commits/rolls back automatically.
srv.on(’CREATE’, ‘Data’, async (req) => {
const tx = cds.tx(req)
await tx.run(INSERT.into(Data).entries(req.data))
})If you need to manage the lifecycle yourself, use cds.tx() in an async block. If an error is thrown, the transaction is automatically rolled back.
const cds=require(’@sap/cds’);
const LOG=cds.log(’test’);
module.exports=async function(){
const{Data}=this.entities;
this.on(’test’,async(req)=>{
return await cds.tx(async (tx) => {
try{
await tx.run(INSERT.into(Data).entries({value:”abc”}));
throw(”some error”);
await tx.commit();
return({success:true});
}catch(err){
await tx.rollback();
throw err;
}
});
});
};Above cds.tx(async (tx) => {...} is the transaction wrapper. This creates a transaction context for you to use to commit or roll back changes. If the function completes normally you commit, otherwise your roll back.
It is advisable to use transactions in service handlers where multiple INSERT , UPDATE , and DELETE , operations are performed.
Locking rows so other user cannot change them at the same time can be performed in the following :
const product = await tx.run(
SELECT.one.from(DATA)
.where({ ID: item.ID})
.forUpdate()
);Here forUpdate() locks the row.
Project Setup
cds init transaction-demo
cd transaction-demo
npm installData Model
Create db/schema.cds:
namespace com.shop;
using { managed } from ‘@sap/cds/common’;
entity Products : managed {
key ID : UUID;
name : String(100);
price : Decimal(10, 2);
stock : Integer; // Available quantity
}
entity Orders : managed {
key ID : UUID;
orderNumber : String(20);
customerName : String(100);
totalAmount : Decimal(10, 2);
status : String(20); // pending, confirmed, failed
items : Composition of many OrderItems on items.order = $self;
}
entity OrderItems : managed {
key ID : UUID;
order : Association to Orders;
product : Association to Products;
quantity : Integer;
price : Decimal(10, 2);
}Service
Create srv/shop-service.cds:
using { com.shop as db } from ‘../db/schema’;
service ShopService {
entity Products as projection on db.Products;
entity Orders as projection on db.Orders;
entity OrderItems as projection on db.OrderItems;
// Create order action
action createOrder(
customerName: String,
items: array of {
productID: UUID;
quantity: Integer;
}
) returns {
success: Boolean;
orderID: UUID;
message: String;
};
}Implementation
Create srv/shop-service.js:
const cds = require(’@sap/cds’);
const LOG = cds.log(”shop”);
module.exports = async function() {
const { Products, Orders, OrderItems } = this.entities;
//Create Order with Transaction
this.on(’createOrder’, async (req) => {
const { customerName, items } = req.data;
//Start a transaction
return await cds.tx(async (tx) => {
try {
//Generate order number
const orderNumber = `ORD-${Date.now()}`;
let totalAmount = 0;
//Create the order
const order = await tx.run(
INSERT.into(Orders).entries({
orderNumber: orderNumber,
customerName: customerName,
status: ‘pending’,
totalAmount: 0
})
);
const orderID = order.ID;
//Process items
for (const item of items) {
//Get product with lock to prevent concurrent modification
const product = await tx.run(
SELECT.one.from(Products)
.where({ ID: item.productID })
.forUpdate()
);
if (!product) {
throw new Error(`Product ${item.productID} not found`);
}
//Stock check
if (product.stock < item.quantity) {
throw new Error(
`Insufficient stock for ${product.name}. ` +
`Available: ${product.stock}, Requested: ${item.quantity}`
);
}
//Create order item
await tx.run(
INSERT.into(OrderItems).entries({
order_ID: orderID,
product_ID: item.productID,
quantity: item.quantity,
price: product.price
})
);
//Decrement stock
await tx.run(
UPDATE(Products, item.productID).set({
stock: product.stock - item.quantity
})
);
totalAmount += product.price * item.quantity;
}
//Update total
await tx.run(
UPDATE(Orders, orderID).set({
totalAmount: totalAmount,
status: ‘confirmed’
})
);
//At this point the transaction will be automatically committed
return {
success: true,
orderID: orderID,
message: `Order ${orderNumber} created successfully!`
};
} catch (error) {
//Transaction failed
LOG.error(’Transaction failed:’, error.message);
//Transaction automatically rolled back
//Throw error again so it gets sent to the client
throw error;
}
});
});
//Before DELETE
this.before(’DELETE’, Products, async (req) => {
const productID = req.data.ID;
const product = await SELECT.one.from(Products).where({ ID: productID });
//Avoid deleting products with stock
if (product && product.stock > 0) {
req.reject(
400,
`Cannot delete ${product.name}. Stock: ${product.stock}`
);
}
});
//Before UPDATE
this.before(’UPDATE’, Products, async (req) => {
//Validation
if (req.data.stock !== undefined && req.data.stock < 0) {
req.reject(400, ‘Stock cannot be negative’);
}
});
};At this point you can deploy and run the project:
cds deploy
npm run startTesting
First create some products using the following:
#!/bin/bash
curl -X POST http://localhost:4004/odata/v4/shop/Products \
-H “Content-Type: application/json” \
-d ‘{”name”:”Laptop”,”price”:999.99,”stock”:10}’
curl -X POST http://localhost:4004/odata/v4/shop/Products \
-H “Content-Type: application/json” \
-d ‘{”name”:”Mouse”,”price”:29.99,”stock”:10}’
curl -X POST http://localhost:4004/odata/v4/shop/Products \
-H “Content-Type: application/json” \
-d ‘{”name”:”Keyboard”,”price”:79.99,”stock”:10}’Next, use the following script to place an order:
#!/bin/bash
PRODUCT_1_ID=$(curl -s “http://localhost:4004/odata/v4/shop/Products?\$top=1&\$filter=name%20eq%20’Keyboard’” |jq ‘.value[0].ID’ -r)
PRODUCT_2_ID=$(curl -s “http://localhost:4004/odata/v4/shop/Products?\$top=1&\$filter=name%20eq%20’Mouse’” |jq ‘.value[0].ID’ -r)
curl -s -X POST http://localhost:4004/odata/v4/shop/createOrder \
-H “Content-Type: application/json” \
-d ‘{
“customerName”: “John Doe”,
“items”: [
{
“productID”: “’$PRODUCT_1_ID’”,
“quantity”: 10
},
{
“productID”: “’$PRODUCT_2_ID’”,
“quantity”: 10
}
]
}’
echoHere we get the first Product IDs of the Keyboard and the Mouse, and use them to create an Order.
Creating Orders
Running this the first time will result in an order being place. The second time around this will fail and the database changes will be rolled back.
The server log will also indicate the issue:
Server Logs
Checking the stock values will also show remain unchanged.
Comments
Post a Comment