Tuesday, 28 October 2014

Optimistic locking & Pessimistic locking


Optimistic locking allows you to lower the isolation level that you use in an application so that fewer locks are placed on the database assets. It allows more applications to run concurrently against the database, and potentially increase the throughput of your applications.
To protect against potential database integrity problems brought on by lowering the isolation level, an optimistic locking implementation has to ensure that no integrity problems occur. The WebSphere Commerce implementation utilizes an optimistic predicate column for each table in the WebSphere Commerce database - OPTCOUNTER.

This helps ensure that every time there is a change to the database, the optimistic predicate can be:
1. Updated with a new value
2. Checked for change
 

In this way, the implementation can guarantee that a row in the database has not changed between the time it was read and the time it was written, to protect the integrity of the database.
With this new freedom that optimistic locking offers, of placing fewer locks on the database, for a shorter period of time, one has to realize that there can also be drawbacks.


A Pessimistic locking strategy means the avoidance of optimistic locking issues by placing locks on the database. In other words, pessimistic locking assumes there will be a collision in the database, and takes precautions to avoid these collisions. A pessimistic locking strategy protects the integrity of the database by locking database assets over the entire duration of a transaction, from the beginning to the end. During this period, no other transactions can access the same assets because they are locked. This strategy results in greater wait times for more transactions. It also raises a possibility that these transactions will either time out or potentially become deadlocked.
An Optimistic locking strategy can improve the application execution by shortening the window in which these pessimistic consequences can occur. At the same time, it opens itself up to collisions where two or more transactions attempt to update the same row in the database. When this happens in an optimistic locking strategy, rollbacks (or failures) for one or more (or all) of the transactions can occur.


WebSphere Commerce uses an optimistic locking scheme that allows the database transaction isolation level to be lowered from repeatable read to read committed. Using that scheme, database rows not normally accessed concurrently are not locked with an intent to update when they are read. Instead, when the update is eventually made, the row is checked to make sure it has not been updated concurrently since it was read. If it has been updated concurrently, then the transaction is rolled back and the command may be restarted from the beginning in a new transaction, if appropriate. In this scheme, performance is improved when concurrent updates do not normally occur, because the relatively expensive process of obtaining the database locks with intent to update is avoided. On the other hand, for those operations where concurrent updates are more likely to occur, pessimistic locking, whereby intent to update locks are obtained when the row is read, continues to be used, thus avoiding the more expensive process of rolling back and re-starting from the beginning in a new transaction.

Attribute Data-Model and important tables with queries.



Attribute Data-Model
--Information related to ATTR_ID & IDENTIFIER based on this we will get the ATTR_ID with which we will be proceeding further for the next table.
select * from attr;

-- Information related to ATTRVAL_ID & ATTR_ID & IDENTIFIER based on the previous step we will be getting the ATTRVAL_ID with which we further proceed to get the catentries related to the particular value.
select * from attrval where attr_id=7000000000000009685;

-- Information related to ATTRVAL_ID  & ATTR_ID & CATENTRY_ID based on the previous step we will get the catEntries which are associated to a particular ATTR_ID
(Stores the catalog entry (CATENTRY) and attribute dictionary attribute (ATTR) relationship)
select * from catentryattr where attrval_id=7000000000000021190 and attr_id =7000000000000009685;

Attribute data model

Attribute Data Model-tables


Saturday, 25 October 2014

SQL QUERRIES usefull while accessing the database



SQL INNER JOIN Syntax :

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
OR
SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;
-- This is used to filter with the ids for the tables which have the relation
Example :
SELECT users.users_ID, Member.Member_Id FROM users INNER JOIN member ON users.Users_id=member.Member_Id;

SQL LEFT JOIN Syntax :

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
OR
SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Example :
SELECT users.users_id, Orders.Orders_ID FROM Users LEFT JOIN Orders ON users.users_ID=Orders.orders_id ORDER BY users.users_id;
SQL RIGHT JOIN Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

Example :
SELECT users.users_id, Orders.Orders_ID FROM Users RIGHT JOIN Orders ON users.users_ID=Orders.orders_id ORDER BY users.users_id;

SQL FULL OUTER JOIN SYNTAX


SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Example :
SELECT Users.Users_id, Orders.orders_id FROM users FULL OUTER JOIN Orders ON users.users_id=Orders.orders_ID ORDER BY users.users_id;

Tuesday, 4 February 2014

Promotions in WCS. 

Promotions provide the ability to attract the customers to purchase the items from the site and make them visit once again once the promotions are available. WCS supports 3. types of promotions which play the vital role for any site which is developed in WCS, the following are the types of promotions that WCS supports
  1. Catalog-Entry level
  2. Order level
  3. Shipping level
Catalog-Entry Level Promotions: Under this type the WCS supports some kind of promotions that are related to the catalog,category.
For example : Buy Catalog entry A,Get Catalog Entry B free
                      Buy Catalog Entry A, Get some percentage off on the purchase of Catalog entry B.
                      Percentage off on the total amount on purchase of catalog entries fro a category.
                      Amount off on the subtotal of catalog entries of a category.
                      Buy X & Y , get a percentage off on both, etc.

Order level Promotions: Under this type of promotions we deal the promotions which are enabled during when the order is getting placed by a customer in the site.
For example : Percentage off on an order
                      Certain amount off on the order being placed
                      Free gift with an order.
Shipping level Promotions: This type of promotions deals with the shipping of the item that is being placed for order.
For Example : Free Shipping of the order
                      Discounted Shipping for an order using a selected shipping mode.
                      Discounted Shipping for an item using a selected shipping mode.

Data Model and the tables related to Promotions:

Promotion Data Model

The main tables in Promotion data model are :
  1. PX_PROMOTION: this table contains Promotions and its status (i.e. Active & Inactive),Starttime and Endtime ,Code-that is is related to that particular promotion.
  2. PX_POLICY: this table tells about the promotion policies.
  3. PX_USAGE: this table tells about statistics  the promotion usage. also contains the ORDERS_ID of the orders to which the promotion was applied.
  4. CLCD_PROMO: this table stores information about the relationship between the Calculation Framework and the promotion standalone infrastructure. Each calculation code matches one promotion in the PX_PROMOTION table.



Monday, 24 June 2013

Insertion of ACPOLCIES using SQL commands...

SQL Insertion of ACPOLCIES for a newly Created Command :

1.insert into acrescgry (ACRESCGRY_ID,RESCLASSNAME) values ((select counter from keys where tablename='acrescgry'),'com.sample.commands.PaypalForwardCmd');

2.insert into acresact (ACRESCGRY_ID, ACACTION_ID) values ((select counter from keys where tablename='acrescgry'),(select ACACTION_ID from acaction where action='Execute'));

3.insert into acresgpres (ACRESGRP_ID, ACRESCGRY_ID) values ((select ACRESGRP_ID from acresgrp where MEMBER_ID in (select orgentity_id from orgentity where orgentityname='Root Organization') and GRPNAME='AllSiteUserCmdResourceGroup'), (select counter from keys where tablename='acrescgry'));

4.UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acrescgry';

5.Commit;

Finally Check for these tables for the entries :

  1. ACRESCGRY
  2. ACRESACT
  3. ACRESGPRES
SQL Insertion of ACPOLCIES for a newly Created VIEW :

1.insert into acaction (acaction_id, action) values ((select counter from keys where tablename='acaction'), 'PaypalForwardView');

2.insert into acactactgp (ACACTGRP_ID,ACACTION_ID) values ((SELECT ACACTGRP_ID FROM ACACTGRP WHERE GROUPNAME = 'AllSiteUsersViews' 
and member_id in (select orgentity_id from orgentity where orgentityname='Root Organization') 
), (select acaction_id from acaction where action='PaypalForwardView'));

3.UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acaction';

4.commit;

Finally Check for these tables for the entries :
  1. ACACTION
  2. ACACTACTGP