Transaction over two Tables with Postgres in Sails.js

I am trying to implement a transaction in Node.js (Sails.js) with Postgresql and Sails Waterline.js ORM.

I tried to adapt this answer. In that answer we have only one table. In my problem I have 2 tables which have to be locked till the transaction is over.

What should this code do:

• find User with :Id
• if this User.coins > 0:
  • Update User.coins = 0
  • Create a new record in the table MyTransaction

But the snippet doesn't lock the transaction over both tables:

try {   // Start the transaction   sails.models.user.query("BEGIN", function (err) {     if (err) {       throw new Error(err);     }     // Find the user     sails.models.user.findOne(currentUser.id).exec(function (err, user) {       if (err) {         throw new Error(err);       }       if (user.coins > 0) {         var params = {           user_id: req.session.passport.user,           publicAddress: bitcoin_address,           amount: user.coins,           wohnAdresse: wohnAdresse         }         // Update the user balance         user.coins = 0;         // Save the user         user.save(function (err) {           if (err) {             throw new Error(err);           }               sails.models.myTransaction.create(params).exec(function (err, transaction) {               if (err) {                 payout = {success: false};                 payout.transactionError = err;                 console.log("ROLLBACK! ROLLBACK!")                 return res.serverError(e);               }               // Commit the transaction                sails.models.user.query("COMMIT", function (err) {                  if (err) {                    throw new Error(err);                  }                    payout = {success: true};                    console.log("PAYOUT PAYOUT", payout);                    console.log("PAYOUT PAYOUT", transaction);                    return res.json(payout);             });           });         });       } // END of if user.coins>0     });   }); }   // If there are any problems, roll back the transaction catch (e) {   User.query("ROLLBACK", function (err) {     // The rollback failed--Catastrophic error!     if (err) {       return res.serverError(err);     }     // Return the error that resulted in the rollback     return res.serverError(e);   }); } 

So when I run a loop(5times) in the frontend console, then it creates 5 records in the myTransaction table. How Can I do this properly?

0 Answers

Read More

Reliably tracking changes made by Hibernate

I'm using a PostUpdateEventListener registered via

registry.appendListeners(EventType.POST_COMMIT_UPDATE, listener) 

and a few other listeners in order to track changes made by Hibernate. This works perfectly, however, I see a problem there:

Let's say, for tracking some amount by id, I simply execute

 amountByIdConcurrentMap.put(id, amount); 

on every POST_COMMIT_UPDATE (let's ignore other operations). The problem is that this call happens some time after the commit. So with two commits writing the same entity shortly one after the other, I can receive the events in the wrong order, ending up with the older amount stored.

• Is this really possible or are the operations synchronized somehow?
• Is there a way how to prevent or at least detect such situation?

2 Answers

Answers 1

No, there are no ordering guarantees, so you'll have to take care to ensure proper synchronization manually.

If the real problem you are solving is caching of entity state and if it is suitable to use second-level cache for the entity in question, then you would get everything out of the box by enabling the L2 cache.

Otherwise, instead of updating the map from the update listeners directly, you could submit tasks to an Executor or messaging system that would asynchronously start a new transaction and select for update the amount for the given id from the database. Then update the map in the same transaction while holding the corresponding row lock in the db, so that map updates for the same id are done serially.

Answers 2

Two questions and a proposal later

1. Are you sure, that you need this optimization. Why not fetch the amount as it is written to the database by querying there. What gives you reason to work with caching.

2. How do you make sure, that the calculation of the amount before writing it to the database is properly synchronized, so that multiple threads or probably nodes do not use old data to calculate the amount and therefore overwrite the result of a later calculation?

I suppose you handle question number 2 right. Then you have to options:

1. Pessimistic locking, that means that immediatly before the commit you can exclusively update your cache without concurrency issues.
2. Optimistic locking: In that case you have a kind of timestamp or counter in your database-record you can also put into the cache together with the amount. This value you can use to find out, what the more recent value is.

Read More

Combining multiple Maven goals into one transaction (e.g. deploy and site-deploy, what if site-deploy fails?)

I configured the build server to do

clean javadoc:jar deploy site-deploy

Now if site-deploy fails (because the site did not build, or somebody used the wrong parent pom), the build server shows a failed build, but the deploy was already applied.

Is there a way to "combine" deploy and site-deploy in a transactional way?

Or should I use a different chain of goals/phases (e.g. install before site-deploy)?

3 Answers

Answers 1

Here I am proposing a close solution with the help bash && operator

mvn clean javadoc:jar install site-deploy && mvn deploy:deploy-file {PARAM} 

This will let you

• fails the build if any of the goal fails during install phase
• if site-deploy fails, then your artifact will not be pushed/deployed to your remote repository
• no need to write any custom rollback mechanism if site-deploy fails

The only case this will fail is when your deploy goal fails, there will be no rollback site-deploy. I think failure of deploy phase will be very much rare.

Answers 2

The only way I could find using Maven core features is through the Release Plugin's rollback goal. This however implies that you'd better change the original build command to use the Release Plugin as well, since this page in the documentation mentions the following:

To rollback a release, the following requirement must be met:

• You haven't run release:clean on the project. This means that the backup files and the release descriptor from the previous release command still exists.

However, it seems it is easy to do this, as the Release Plugin's perform goal already executes deploy site-deploy by default which is exactly what you are doing. As for the javadoc:jar goal, it can be attached to the release profile as mentioned in this FAQ.

If for some reason, the Release Plugin could not be used, then I think the only way to get around this is through the build environment itself. You could split the build into two stages:

1. The first stage runs the deploy goal.
2. The second stage run site-deploy.

In case the second stage fails, you manually rollback by executing the first stage but against the previous revision in your SCM (the one that was last deployed successfully).

Answers 3

deploy and site-deploy are usually done over HTTP and potentially deploy to different places, so achieving transactional semantics is probably impossible and you need to approach this as an eventual consistency problem.

In a CI context (that you mentioned you are using), if site-deploy fails, you'd need to either:

• rollback the deploy;
• retry the site-deploy goal.

I'm not familiar with quickbuild but with other common CI tools you are able to split the tasks into separate jobs to make it easy to apply either solution.

Since the goal (pun intended) seems to be to catch errors while building the site, an option might be to create a chain of jobs:

1. mvn install site to run regular tests and build the site;
2. two jobs that run in parallel and can be retried if failed:
   • mvn deploy;
   • mvn site-deploy.

This could more or less complex depending on how the workspace is shared between jobs.

Read More

Postgres concurrency and serializability. Do I need a SERIALIZABLE isolation level?

I have an Items and Jobs table:

Items

• id = PK
• job_id = Jobs FK
• status = IN_PROGRESS | COMPLETE

Jobs

• id = PK

Items start out as IN_PROGRESS, but work is performed on them, and handed off to a worker to update. I have an updater process that is updating Items as they come in, with a new status. The approach I have been doing so far has been (in pseudocode):

def work(item: Item) = {   insideTransaction {     updateItemWithNewStatus(item)     jobs, items = getParentJobAndAllItems(item)     newJobStatus = computeParentJobStatus(jobs, items)     // do some stuff depending on newJobStatus   } } 

Does that make sense? I want this to work in a concurrent environment. The issue I have right now, is that COMPLETE is arrived at multiple times for a job, when I only want to do logic on COMPLETE, once.

If I change my transaction level to SERIALIZABLE, I do get the "ERROR: could not serialize access due to read/write dependencies among transactions" error as described.

So my questions are:

• Do I need SERIALIZABLE?
• Can I get away with SELECT FOR UPDATE, and where?
• Can someone explain to me what is happening, and why?

Edit: I have reopened this question because I was not satisfied with the previous answers explanation. Is anyone able to explain this for me? Specifically, I want some example queries for that pseudocode.

2 Answers

Answers 1

You can use a SELECT FOR UPDATE on items and jobs and work on the affected rows in both tables within a single transaction. That should be enough to enforce the integrity of the whole operation without the overhead of SERIALIZABLE or a table lock.

I would suggest you create a function that is called after an insert or update is made on the items table, passing the PK of the item:

CREATE FUNCTION process_item(item integer) RETURNS void AS $$ DECLARE     item items%ROWTYPE;     job  jobs%ROWTYPE; BEGIN  -- Implicitly starting a transaction     SELECT * INTO job FROM jobs     WHERE id = (SELECT job_id FROM items WHERE id = item)     FOR UPDATE;  -- Lock the row for other users      FOR item IN SELECT * FROM items FOR UPDATE LOOP      -- Rows locked         -- Work on items individually           UPDATE items         SET status = 'COMPLETED'         WHERE id = item.id;     END LOOP;      -- Do any work on the job itself END;  -- Implicitly close the transaction, releasing the locks $$ LANGUAGE plpgsql; 

If some other process is already work on the job or any of its associated items, then the execution will halt until that other lock is released. This is different from SERIALIZABLE which will work until it fails and then you'd have to re-do all of the processing in a second try.

Answers 2

If you want the jobs to be able to run concurrently, neither SERIALIZABLE or SELECT FOR UPDATE will work directly.

If you lock the row using SELECT FOR UPDATE, then another process will simply block when it executes the SELECT FOR UPDATE until the first process commits the transaction.

If you do SERIALIZABLE, both processes could run concurrently (processing the same row) but at least one should be excepted to fail by the time it does a COMMIT since the database will detect the conflict. Also SERIALIZABLE might fail if it conflicts with any other queries going on in the database at the same time which affect related rows. The real reason to use SERIALIZABLE is precisely if you are trying to protect against concurrent database updates made by other jobs, as opposed to blocking the same job from executing twice.

Note there are tricks to make SELECT FOR UPDATE skip locked rows. If you do that then you can have actual concurrency. See Select unlocked row in Postgresql.

Another approach I see more often is to change your "status" column to have a 3rd temporary state which is used while a job is being processed. Typically one would have states like 'PENDING', 'IN_PROGRESS', 'COMPLETE'. When your process searches for work to do, it finds a 'PENDING' jobs, immediately moves it to 'IN_PROGRESS' and commits the transaction, then carries on with the work and finally moves it to 'COMPLETE'. The disadvantage is that if the process dies while processing a job, it will be left in 'IN_PROGRESS' indefinitely.

Read More

Spring native query executed within a transaction taking outdated value

I'm using Spring Boot (1.4.4.REALEASE) with Spring Data in order to manage a MySql Database. I've got the following case:

1. We update one revision performed in one equipment using the RevisionService.
2. RevisionService saves the revision and calls the EquipmentService to update the equipment status.
3. The updateEquipmentStatus does a call to a Db stored procedure in order to evaluate the equipment with its revisions altogether and update the field.

I've tried some options but don't achieve to get the updated status for the equipment. The updateEquipmentStatus method keeps writing the previous status for the equipment (not considering the current revision being stored in the transaction). The code is written this way:

RevisionService

@Service public class RevisionService{      @org.springframework.transaction.annotation.Transactional     public Long saveRevision(Revision rev){         //save the revision using JPA-Hibernate         repo.save(rev);         equipmentService.updateEquipmentStatus(idEquipment);     } } 

EquipmentService

@Service public class EquipmentService{      @org.springframework.transaction.annotation.Transactional     public Long updateEquipmentStatus(Long idEquipment){         repo.updateEquipmentStatus(idEquipment);     } } 

EquipmentRepo

@Repository public interface EquipmentRepo extends CrudRepository<Equipment, Long> {      @Modifying     @Procedure(name = "pupdate_equipment_status")     void updateEquipmentStatus(@Param("id_param") Long idEquipment);  } 

As far as I understand, as both methods are annotated with Spring's transactional, the updateEquipmentStatus method should be executed in the scope of the current transaction. I've also tried with different options for the @Transactional annotation from updateEquipmentStatus, such as @Transactional(isolation=Isolation.READ_UNCOMMITTED) (which shouldn't be required, because I'm using the same transaction) and @Transactional(propagation=Propagation.REQUIRES_NEW), but keeps not considering the current status. That's how my stored procedure is saved into the MySql DB:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pupdate_equipment_status`(IN `id_param` INT)     LANGUAGE SQL     NOT DETERMINISTIC     MODIFIES SQL DATA     SQL SECURITY DEFINER     COMMENT '' BEGIN  /*Performs the update considering tequipment and trevision*/  /*to calculate the equipment status, no transaction is managed here*/  END 

I also want to clarify that if I execute some modification in the equipment itself (which affects only tequipment), the status is being properly updated. InnoDb is the engine being used for all the tables.

---

UPDATE

Just changed the repo method to use a nativeQuery instead and the same problem keeps happening, so the Db procedure being involved should be discarded:

@Modifying @Query(nativeQuery = true, value= "update tequipment set equipment_status = (CASE WHEN (...))") void updateEquipmentStatus(@Param("id_param") Long idEquipment); 

---

UPDATE2

Having done more tests and added a log with TransactionSynchronizationManager.getCurrentTransactionName() in the methods, that's the concrete issue:

• Changes done in the equipment service are properly picked by the updating function (When something in tequipment changes, the status in tequipment is calculated properly).
• Changes done in the revision service (trevision) result in an outdated value in tequipment (it doesn't matter if Spring does it in a different transaction using REQUIRES_NEW or not). Spring seems to create a new transaction properly when using REQUIRES_NEW in establishEquipmentStatus, because the current transaction name changes, but the native query doesn't have the latest values (because of the transaction before not being commited?). Also tried removing @Transactional from establishEquipmentStatus so the same transaction is used, but the issue keeps happening.
• I would like to highlight that the query used to update equipment status has a case expression with multiple subqueries using trevision.

---

UPDATE3

Adding the following code fixes it (programatically flushing the transaction state to the Database):

@Service public class EquipmentService{      @PersistenceContext     private EntityManager entityManager;      @org.springframework.transaction.annotation.Transactional     public Long updateEquipmentStatus(Long idEquipment){         entityManager.flush();         repo.updateEquipmentStatus(idEquipment);     } } 

Still it would be great to find a declarative way to do it..

1 Answers

Answers 1

Changing to read uncommitted is the right idea but you'd also need to flush the entitymanager before your stored procedure is called. See this thread:

How to make the queries in a stored procedure aware of the Spring Transaction?

Personally I'd do it all in Spring unless you are absolutely forced to use a stored procedure.

Read More

REST API Subscriptions and transactions(payments) endpoints in Laravel

I am developing a web app and I observe REST API standards. I am searching for REST API best practice for subscription and payments.

When a new user subscribe for "pro plan", user should pay money for plan and it is a transaction.

Should I set POST: users/{id}/subscriptions and SubscriptionsController@store when new user subscribes?

And because subscription is a transaction and 2 separated request (before/after bank), all of subscribe codes should be in SubscriptionController@store?

For upgrade, cancel or updating a plan should I set PUT: users/{id}/subscriptions/{id} and SubscriptionController@update or other endpoint?

3 Answers

Answers 1

Generally you would not pass the user id in the route, unless there was some sort of authentication in the controller. eg. Admin is updating the user. Instead use the Auth::user() object in the controller.

With regard to your question, there are many options and it is entirely up to you, but a possible way of doing it would be to use a resource route\controller for this.

Route::resource('user/subscription', 'User\SubscriptionController');

Then the controller would look something like this:

<?php  namespace App\Http\Controllers\User;  use Illuminate\Http\Request; use Illuminate\Support\Facades\Auth;  class SubscriptionController extends Controller {      public function index()     {         // get user         $user = Auth::user();          // list all user subscriptions     }      public function store(Request $request)     {          // get user         $user = Auth::user();          if(empty($user)) {             // create user         }          // create and process subscription for the user          // possibly using a plan id in the $request     }      public function show($id)     {         // get user         $user = Auth::user();          // return user subscription details for $id     }      public function update(Request $request, $id)     {         // get user         $user = Auth::user();          // update or change user subscription         // possibly using a plan id in the $request      }       public function destroy($id)     {         // get user         $user = Auth::user();          // cancel user subscription with $id     } } 

And your routes would be like this:

GET user/subscription list all user subscriptions index()

POST user/subscription create a user subscription store(Request $request)

GET user/subscription/{subscription_id} show a user subscription show($id)

PUT/PATCH user/subscription/{subscription_id} update a user subscription update($id)

DELETE user/subscription/{subscription_id} cancel a user subscription destroy($id)

Answers 2

I am trying to understand what you asking for but it's a little bit blur for me so if i got right , you are trying to figure what is the best practice for API End point naming , it's really depend on the functions you will provide and how you will expose the documentation.

But from my point i don't see a reason to chain User ID and Subscriber ID in the URL , i recommend something like this and you can pass all the information you want in the Body

$router->post('settings/user/plan', 'Settings\SubscriptionController@subscribe'); $router->put('settings/user/plan', 'Settings\SubscriptionController@changeSubscriptionPlan'); $router->delete('settings/user/plan', 'Settings\SubscriptionController@cancelSubscription'); $router->post('settings/user/plan/resume', 'Settings\SubscriptionController@resumeSubscription'); $router->put('settings/user/card', 'Settings\SubscriptionController@updateCard'); $router->put('settings/user/vat', 'Settings\SubscriptionController@updateExtraBillingInfo'); $router->get('settings/user/plan/invoice/{id}', 'Settings\SubscriptionController@downloadInvoice'); 

it's really up to you how you define your endpoints

Answers 3

If your try for Braintree or Stripe both payment gateway through easily maintan plans and subscription.

Main Benifits :-

1. Less coding for subscription and plan
2. UI ready in Braintree tree drop-ui its reponsive
3. Easily attach add-ons charges

Read More

Slick 3 session with rollback

I am using slick 3 and I am trying to perform some integration tests with some inserts, some code that uses the db and then I want to rollback all the insert or deletion at the end of the test itself but I cannot find any documentation about it.

Is it possible? How can I achieve it?

2 Answers

Answers 1

I can advice to drop and create table schema before and after test using BeforeAndAfter scala-test trait with next code:

def createTable(): Future[Unit] = {         db.run(DBIO.seq(           MTable.getTables.map(tables =>             if (!tables.exists(_.name.name == table.baseTableRow.tableName))               db.run(table.schema.create)           )         )) }  def dropTable(): Future[Unit] = db.run(table.schema.drop) 

Answers 2

You need to use . transactionally around the DBIOAction

eg

val a = (for {   ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result   _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*) } yield ()).transactionally  val f: Future[Unit] = db.run(a) 

For more see http://slick.typesafe.com/doc/3.1.1/dbio.html#transactions-and-pinned-sessions

Read More