I haven't found a way to tune the indexing on temp tables, which can present some difficulties if the temp tables get large and the lookups are based on multiple keys. In addition, because the data changes each and every time you load the temp tables, the queries against the temp table will usually be automatically re-planned - whereas permanent tables tend to be more stable and the query plans don't have to be compiled with each invocation.

Not sure of the other servers, but SQLServer does have read/write locks on the temp tables. Of course, the tables from which you got the data are not locked - which is what you were probably trying to indicate.

SQLServer has a new concept for procedures called the Table Variables, which oddly enuf are not locked - getting you some performance boost. I like the idea, but it would be nice if their version of T-SQL was consistent and let you pass table variables around like any other variable. As it is, you can't seem to pass the table vars from one stored procedure to another, seriously crimping how you can modularize an application.