事务中数据库查询的时间戳

Question

I'm using DB::transaction and SoftDeletes.

For those unfamiliar with Laravel the SoftDelete functionality of Laravel abstracts away the setting / querying of rows in the database. When a record is deleted the field deleted_at is given a timestamp otherwise this field is null. This field is then used on querying to only get non deleted rows.

Currently I have a script that runs every day that deletes all the soft deleted entries that are older than 7 days.

This is where my question/problem is. What if my DB::transaction spanned for let's say 10 seconds and add the end writes to Table A and Table B so that the entries in both tables have a different timestamp.

I can picture a scenario where when the cleanup script executes it could delete HALF of the entry records.

My question then is

1. would a DB::transaction give the same timestamp to all the records?
2. if not how can I remedy this problem?

Answer 1

The only way I can seeing it working, as it seems mission critical that your data isn't messed up with a partial restore as you say, is to include a transaction number in your affected tables.

The cleanup routine would need to be tweaked. A transaction cannot be soft deleted and subsequently wiped unless all the timestamps in the transaction exceed 7 days.