[vox-tech] Sales/Shipping/Payment without Transactions.

[Mike Simons <msimons@moria.simons-clan.MAPScom>]

On Fri, Apr 25, 2003 at 09:26:44AM -0700, Micah J. Cowan wrote:
> Answer the question: what would you do to avoid having either an
> order in the DB marked to be shipped without payment, or payment for
> an order which didn't get into your DB?

  I will attempt to give an example, I'm not saying this is the only 
way to do this without transactions.  This may be a bit long winded.

  Point out problems if you see them.

    Later,
      Mike Simons

  To keep this "simple", no transactions, no locking, many definitions,
I focus on only the shipping part of the question and simply payment 
types to only credit cards.

  The samples below target mysql, and have a perl-ish syntax to them... 
but syntax is not correct.

  There is one requirement: when an insert or update statement is run it 
*must* hit the disk before any other insert or update statements that
come after it.  Said another way, if the database stores things out of
order you have a problem.


table definitions:
===
CREATE TABLE Customers (
  c_id INT UNSIGNED NOT NULL AUTO_INCREMENT,   # customer id
  c_first VARCHAR(20) NOT NULL,                # customer first name
  c_last VARCHAR(20) NOT NULL                  # customer last name
);

CREATE TABLE Items (
  i_id INT UNSIGNED NOT NULL AUTO_INCREMENT,   # item id
  i_name VARCHAR(20) NOT NULL,                 # item name
  i_price DOUBLE(16,2) NOT NULL                # item price
);

CREATE TABLE Orders (
  o_id INT UNSIGNED NOT NULL AUTO_INCREMENT,   # order id
  o_time TIMESTAMP NOT NULL,                   # timestamp
  o_c_id INT UNSIGNED NOT NULL,                # customer id
  o_p_id INT UNSIGNED NULL                     # payment id
  p_status ENUM ('creating', 'rejected', 'pending', 'shipped')
                                               # order processing status
);

CREATE TABLE OrderedItems (
  oi_o_id INT UNSIGNED NOT NULL,               # order id
  oi_i_id INT UNSIGNED NOT NULL,               # item id
  oi_count INT UNSIGNED NOT NULL,              # how many items
);

CREATE TABLE Payments (
  p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,   # payment id
  p_time TIMESTAMP NOT NULL,                   # timestamp
  p_ccn INT(16) NOT NULL,                      # credit card number
  p_expr INT(4) NOT NULL,                      # expiration date
  p_auth INT(6) NULL                           # authorization number
  p_status ENUM ('checking', 'rejected', 'approved', 'done') # approval status
);
===


sample transaction:
===
# Get the Customer ID...
  select c_id from Customers where (c_first = 'John' && c_last = 'Doe');
  $customer_id = $c_id
or...
  insert into Customers values (NULL, 'John', 'Doe');
  $customer_id = $mysql_insertid


# Start an order
insert into Orders values (NULL, NULL, c_id, NULL, 'creating');
$order_id = $mysql_insertid


# Add items into ordered Items
for each item in the list to order
  insert into OrderedItems values ($order_id, $item_type, $how_many);


# Start payment authorization
insert into Payments values 
  (NULL, NULL, $card_number, $card_expire, NULL, 'checking');
$payment_id = $mysql_insertid

($auth_number, #status) = get_credit_auth();  # payment authorization
# status is either 'rejected' or 'approved'

#### race condition (1)

# Finish payment authorization
update Payments set p_auth=$auth_number, p_status=$status
  where (p_id = $payment_id);

#### race condition (2)

# Finish order
if ($status not equal 'approved') {
  update Orders set p_id=$payment_id,o_status='rejected'
    where (o_id = $order_id);

  print "your payment has been rejected";
} else {
  update Orders set p_id=$payment_id,o_status='pending'
    where (o_id = $order_id);

  print "your authorization number is $auth_number";
}
===


logic:
===
to find orders that are being entered right now:
  select o_id from Orders where o_status = 'creating';

to find orders that were rejected for credit reasons:
  select o_id from Orders where o_status = 'rejected';

to find orders that need product shipped:
  select o_id from Orders where o_status = 'pending';


to find which payments need to be billed to the credit card company:
  select p_id from Payments where p_status = 'approved';


to find out which transactions are "stale" or left over from
  an application crash, look for order status 'creating' or 
  payment status 'checking'... at a time when the system is 
  offline or after the time stamp is X days old.
===


Race Condition 1:
====
  Credit card company issued a authorization, but the authorization
number is not stored.

  Since the payment status never changes to 'approved', order status
never moves to 'pending', and items are not shipped.
  Even though a credit card authorization is issued I expect the customer
is not billed.  I have not dealt with credit card transactions before, but 
here is why I suspect that this is fine.

  In my mind the authorization step just puts a short term hold on the
funds, the item_selling_company must *bill* the credit card company
with authorization number to actually collect money.  This is why some
transactions clear on one's credit card statement days away from when
the store receipt says.
====

Race Condition 2:
====
  Credit authorization is stored, but customer is not informed that
the transaction is complete.
  
  The items will ship to the customer.  The only concern is they may
redo the transaction and order a second shipment... this is out of
scope.

  The application could make it easy for the user to check the status 
of they most recent order.
====

  I think a system like the one above would be very easy to track down
problems in, at each important step a log is made.

  Instead of using status ENUM columns like I did above or if you were
working with a DB that doesn't support them.  You can do null-able 
timestamps one for each order state:
  Created
  Approved
  Shipped
and for each payment state:
  Requested
  Approved      (set to a not-null known value for a reject)
  Billed

-- 
GPG key: http://simons-clan.com/~msimons/gpg/msimons.asc
Fingerprint: 524D A726 77CB 62C9 4D56  8109 E10C 249F B7FA ACBE

Attachment: pgp00018.pgp
Description: PGP signature