A database ":memory:" exists only in the process virtual private address space and is never stored on disk (except, of course, that virtual memory may in fact live in the swap file when a page is not mapped into V:R address space). The cache_size setting does not limit (has no effect on) the size of the database in memory. It is limited only by the virtual address space that the process can allocate (or the heap allocation limit for sqlite3's use, which is configurable, and effective only if memory management is in effect).
A database with a zero-length name ("") has a virtual private address space size limit that is equal to the cache_size setting. Once the entire number of pages specified in the cache_size are used, then a temporary file will be allocated in the location specified for the process to create temporary files, wherever that happens to be on the Operating System in question, in order to "spill pages" that will not fit in the specified page cache all at the same time. This file is a temporary file that will be deleted by the Operating System when the process ends (and assumes that the Operating System works properly -- if it does not, then the "temporary file" may be left behind if the Operating System fails to wipe-its-arse properly -- for example, Microsoft Operating Systems are known to require lots of help managing temporary files and has a tendency to leave turds scattered about with wild abandon). The temporary file is opened for EXCLUSIVE use by the process which created it and cannot be accessed by anything save the connection which created it.