ports-mgmt/pkg: 1.18.4 excess "already" message when pkg lock

From: Tatsuki Makino <tatsuki_makino_at_hotmail.com>
Date: Mon, 12 Sep 2022 22:51:40 UTC

When pkg lock/unlock with pkg-1.18.4, some packages may say "already" message.

# pkg lock pkg llvm13
pkg-1.18.4: lock this package? [y/N]: y
Locking pkg-1.18.4
llvm13-13.0.1_3: lock this package? [y/N]: y
Locking llvm13-13.0.1_3
llvm13-13.0.1_3: already locked
# pkg unlock pkg llvm13
pkg-1.18.4: unlock this package? [y/N]: y
Unlocking pkg-1.18.4
llvm13-13.0.1_3: unlock this package? [y/N]: y
Unlocking llvm13-13.0.1_3
llvm13-13.0.1_3: already unlocked

The following is how to find a package that can reproduce it.
In other words, it is ports with 2 or more CATEGORIES.

pkg query -e "%#C >= 2" %n

The reason for this is....

SQL obtained by pkg -dddd is as follows
Some parts are omitted because they are long.

WITH ... SELECT DISTINCT ... locked, ... FROM packages AS p LEFT JOIN pkg_categories ON p.id = pkg_categories.package_id LEFT JOIN categories ON categories.id = pkg_categories.category_id ...  WHERE (p.name = 'llvm13' ...
UPDATE packages SET locked = 1 WHERE id = ...

The WITH... one is a SQL statement executed by pkgdb_it_next.
The UPDATE... one is a SQL statement executed by pkgdb_set.

WITH... statement has DISTINCT, but if there is no DISTINCT, it seems to return the same entry for a number of categories. It usually returns only 1 entry due to DISTINCT.
However, if pkgdb_it_next and pkgdb_set are executed alternately and the locked column returned by SELECT is updated in the process, it appears that DISTINCT will not treat them as the same entry.
Therefore, the message "already locked/unlocked" is displayed once.

I don't know how to fix it :)
That's all for now :)