if two different cores try to write to the same spot in main memory at the same time, what happens? Does main memory automatically only allow a spot in memory to be written one-at-a-time, or does some kind of expensive locking have to occur?
(Basically I want to know how expensive writing to an atomic volatile int is, and if it has affects on other processes & threads that don't access it, e.g. if the bus got locked it would affect everything, right?)
Every multi core CPU has a means to ensure that each core's write is sequenced on a first come first served basis. If it didn't you'd have a situation where the two cores were both trying to drive the memory bus at the same time, and that will cause smoke to be emitted.
So yes, locking does occur at the electronic level, but it's not expensive. It is relatively complex though because the memory is reflected in cache, so a mechanism called bus snooping allows a cache to spot when another core or peripheral changes the contents of a memory location that it is currently caching.
So if no other process or core is using the same memory location then their caches won't be caching it so your read write will take place unhindered. Initially the write will only be to cache, and it will only be written to main memory if the running processes access enough memory elsewhere to oblige the cache to flush.