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

[Mike Simons]

--tNQTSEo8WG/FKZ8E
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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=20
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=20
types to only credit cards.

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

  There is one requirement: when an insert or update statement is run it=20
*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:
=3D=3D=3D
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 sta=
tus
);
=3D=3D=3D


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


# Start an order
insert into Orders values (NULL, NULL, c_id, NULL, 'creating');
$order_id =3D $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=20
  (NULL, NULL, $card_number, $card_expire, NULL, 'checking');
$payment_id =3D $mysql_insertid

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

#### race condition (1)

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

#### race condition (2)

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

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

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


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

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

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


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


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


Race Condition 1:
=3D=3D=3D=3D
  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=
=20
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.
=3D=3D=3D=3D

Race Condition 2:
=3D=3D=3D=3D
  Credit authorization is stored, but customer is not informed that
the transaction is complete.
 =20
  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=20
of they most recent order.
=3D=3D=3D=3D

  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=20
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

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

--tNQTSEo8WG/FKZ8E
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE+qdwu4Qwkn7f6rL4RAt2dAJ4wK/7XeOphiT72PHZaL8i5bEdo6ACeLP44
OR24B7vP5yIJHObgshZ6CoY=
=jozw
-----END PGP SIGNATURE-----

--tNQTSEo8WG/FKZ8E--