It is normal that you can't write one record in two separate queries at the same time.
In general, the idea is that you should not keep the transactions open for a long time, but immediately after adding/modifying/deleting data close the transaction, then the record lock will be released and the second job will perform another data modification without any problem.
You can also set in MSSQL the amount of time the queries will wait for the lock to be released (
LOCK_TIMEOUT https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql?view=sql-server-ver16), then if two programs call
UPDATE of the same record, the application that did it second will wait the specified time, and if still the record locked then return an error.
But to start with, check if the database has indexes you can use. Because the lack of suitable indexes can strongly increase the execution time of each query.
And remember that each
SELECT also waits for the release of the lock on the records it has to retrieve, but here you can use the
WITH(NOLOCK) table hint (
https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/ ), the use of which will not wait for the release of locks, but will retrieve the data immediately, and for the locked records the state of the data will be from before the transaction.