John Davidson

Message:

I would like to use transactions and/or locking tables in my e-shop, mainly to avoid order-id duplication (it is not only autoincrement ID).

Now I have this scenario:

1) cart submitted by user

2) check if all informations are correcly filled

3) lock tables with products, orders and discount cupons

4) begin transaction

-- try { --

5) update products stocks

6) mark discount cupon as used

7) get order-id (it is based on number of orders today)

8) save order

-- } catch { --

9a) rollback transaction

10a) unlock tables

-- } --

9b) commit transaction

10b) unlock tables

11) send mail and show thankfull message to user

Q1: I am wondering if this scenario is "correct" or there is a better way?

Q2: Do I need to lock tables when I use transactions? Beside not getting order-id duplicated, I would like to affect users and admins of e-shop as little as possible. I decided to use transaction because of making data consistent - for example, when products stocks are updated but saving order fail, it would be nice to rollback previous stocks numbers.

I appreciate all comments and recommendations guys.

---

My Answer:
correct and safe to use in an e-shop environment?

A1: Your scenario looks mostly correct, but there are a few things to consider to ensure safety and consistency in your e-shop transactions:

1. Make sure to handle exceptions properly in your try-catch block. If an exception occurs during the transaction, you should rollback the changes and unlock the tables to avoid leaving the database in an inconsistent state.

2. Consider using row-level locking instead of table-level locking to minimize the impact on other transactions. You can use the SELECT ... FOR UPDATE statement to lock specific rows in the products, orders, and discount coupons tables.

3. Ensure that your database engine supports transactions and locking mechanisms. InnoDB is a popular choice for MySQL databases due to its support for transactions and row-level locking.

4. Test your transaction logic thoroughly to identify and address any potential issues before deploying it in a production environment.

Overall, using transactions and locking tables can help ensure data integrity and consistency in your e-shop transactions. Just make sure to handle exceptions properly and test your logic to ensure it works as expected.