Checking record for existence before inserting to db in one transaction

Hi guys, I need your help.

My script made syncronization job. Before adding new record to db, I need to check db for existence such record (depends on 2 columns). If it have not found, then record is inserted, else do nothing. How to do it in one transaction, for safety insert and avoiding duplications?

I used this code but it does not help.

$databases = \common\models\Database::find()->active()->all();

        foreach ($databases as $database) {

            $dbConnection = Yii::$app->get($database->name);

            $result = (new \yii\db\Query)

                ->select('tst.kod agent_id, tst.fio fullname, tst.depo_kod depo_id')

                ->from('sales tst')


            foreach ($result as $row) {

                $transaction = Agent::getDb()->beginTransaction();

                try {

                    $found = Agent::findOne([

                            'agent_id' => $row['agent_id'],

                            'database_id' => $database->id,


                    if ($found == null) {

                        $agent = new Agent();

                        $agent->agent_id = $row['agent_id'];

                        $agent->depo_id = $row['depo_id'];

                        $agent->database_id = $database->id;;

                        if ($agent->save()) {

                            $agentProfile = new AgentProfile();

                            $agentProfile->fullname = $row['fullname'];

                            $agentProfile->is_supervisor = false;

                            $agent->link('profile', $agentProfile);

                        } else {


                            throw new \Exception('Ошибка при сохранении агента.');




                } catch (\Exception $e) {



                    throw $e;




Calling this code immideately more than one time, there will be duplications on tables.

To avoid multiple executions together, you should track job execution in a table record and delete the record (or set a flag).

Then, to have better performance, you can check if record count is > 0:

                    $nr = Agent::find()->where([

                            'agent_id' => $row['agent_id'],

                            'database_id' => $database->id,


$found = ($nr>0);

if($found) {



Do you mean setting a flag of lock to access to table before processing request and unlock it on the end?

HOw to do it on postgres?


Create a table, for example "Jobs" with date_start, date_end, executing. But it could be built with fields you need.

I got it, thank you.