I upgraded my FreeBSD 9.2 VPS to FreeBSD 10 upon its release. The upgrade was largely painless, but I found that after the upgrade, pkgng no longer worked. I just figured I'd post this in the event someone else ran into the same issue I experienced.
When I would run a pkg install command, after updating the repo, I would see the following error message: "pkg: sqlite: database repo-Freebsd is already in use" - I tried the common fix for pkgng db issues, which is to 'rm /var/db/pkg/repo-*' to purge local pkg repo stuff. This didn't work. What did work, however, was modifying my repo configuration (stored in /etc/pkg/FreeBSD.conf)
I changed the name of the repo defined from its default of FreeBSD to FreeBSD10 (anything should work). After making this change, I fetched the repo again and was able to install a test package.
Bear in mind, I only have one or two packages on my system; everything else installed was built from ports. So, I guess it was just a bit of 'WHY ISN'T THIS WORKING??' OCD on my part, but there you have it. It works now.