How exactly are locked files handled by the os ?
Or to be precise, why is it that sometimes I cannot delete a locked file from a Windows Explorer, but if I open a command prompt in the directory in question, I can delete it from there ?
When a process opens a handle to a file or directory it specifies what share level it wants. The share level can be any combination of the flags FILE_SHARE_DELETE, FILE_SHARE_READ and FILE_SHARE_WRITE. The names of the flags are pretty obvious. If I specify FILE_SHARE_READ this allows other processes to read the file, FILE_SHARE_WRITE allows other processes to write the file (possibly changing it under my feet) and FILE_SHARE_DELETE allows other processes to delete the file (though I think it doesn't actually get deleted until after I close my file handle).
Anyhow, if any process opens a handle to a file and does not specify FILE_SHARE_DELETE this means no other process can delete the file until the first process has closed it's handle. This is the usual reason you get those "file is locked by another process" errors. Note that processes can open handles to directories as well, so this also applies to deleting directories, even if they are empty.
As for why you can delete a file from a command prompt when you can't delete it from Explorer, I don't understand why this would happen. I would need to see it with my own eyes.
John's right... you can't delete files that are in use, because... they are in use. It's the same reason you can't change your tires while driving to work. All sorts of unexpected things would happen.
If you want to know what process is using the file (so you can temporily kill the process so that it releases the lock on the file, use Process Explorer from Microsoft/Sysinternals.
If a file is in use by Windows, you can use MoveOnBoot by Gibin Software to delete the locked file after a reboot.
I'd guess that you have some third party extension on explorer that opens the file for a bit to scan the file, causing explorer to lock it. Is it only larger files that have this issue? Locks are locks in the windows world, no command prompt can delete a locked file (although as noted above there are ways to close the lock, then delete the file)