On the SQL side you shouldn't be having issues. Unless you have a large number of write to the SQL server, most SQL servers do server locking very well to the point where you have have hundreds of insert per second while the database is active for large amount of data retrival. There is always a theoretical limit to currurent insert and slowdown or possibly insertion failure, but I haven't really see any database have data integrity problems as a result of take locks.
The problem with SQL and FM have more to do with data out of date problem. We have noticed in our application that it seem to not refresh at all after the same record on the SQL server changes for quite a while. Someone can be looking at records linked in from SQL and still see those record minutes after I've deleted them from the SQL database. This is the part where you can have 2 people change the same record based on what they see, even though what they see might be different as changes occur and FM is not refreshing the data. Having 2 separate set of SQL tables or databases does not help at all in this case unfortunately.
We keep control records in FM and data records where the changes a very very limit but where there are much more data records in SQL databases. It's a hybrid system that work very well.