Hello everyone,
I need session locking to prevent overwriting of the data from code executions running at the same time. Up to the moment I was using the MySQL specific DO GET_LOCK and DO RELEASE_LOCK mechanism, but now I need something more generic (to work with at least MySQL/innoDB, PostgreSQL and Oracle). I was thinking of three ways:
1. Lock manager implemented by myself with a backend in the database (a table with two columns - key, timestamp). Then I can have code very similar to the MySQL specific GET_LOCK.
2. use a flag in the session table - like an additional (boolean) column in the table "locked"
3. is it possible to use transactions for this? Is it possible thes example:
- starting session with transaction start (reading from the sessions table)
- execute the rest of the code (transational/nontransactional)
- commit the first transaction (write to the sessions table)
As I understand the transactions this will lock the specific row by sess_id and release it after commit. As I found this is not quite correct because I can not have started another transaction before I closed the previous one (no matter they use different tables). Will this work if I open two connections - one for the session handling and the other for the rest?

Which one of these has the smallest overhead? Will the third work and is it logically correct (I'm not very familiar with transactions - used them just a few times in simpler situations)?

Any comments are welcome.

Vesko Kenashkov