suppose I have following operation in a SP:
truncate table mytab
insert into mytab(...)
then if bot different user run this SP at same time(for example both start within less one second, but the SP need to run more then one second), the second user will remove the data for the first user or need to wait first user done the transaction?
Any conflict for this case?
I know if mytab is temp table like #mytab, there is no problem as each temp tab only visible for its session. but not sure about a physical table.
It doesn't matter if these statements are in a stored procedure or not.
A stored procedure is not the same thing as a transaction.
The behavior depends on whether the clients are using chained mode or not.
If not using chained mode or explicit BEGIN TRANSACTION, each statement is an atomic transaction that takes locks, does its thing, and then releases the locks. The second sessions truncate could occur between any two of the first sessions statements.
Within a transaction, the truncate table will take an exclusive table lock and hold it until the end of the transaction. The second session won't be able to truncate the table until the first session's transaction commits and it releases its lock(s).