Hello,
Here I'm implementing a session management, which has a connections table partitioned between
active and archived connections. A connection represents a connection between a user and a chatroom.
I use partitioning for performance reasons.
The active table contains all the data for the active session : user_id, chatroom_id, session start
time, and other information.
The archive table contains just the user_id, chatroom_id, session start and end time, for logging
purposes, and for displaying on the site, which user was logged to which chatroom and from when to when.
Thus, when a user disconnects from a chatroom, I must move one row from the active to the archive
table. This poses no problem as there is a UNIQUE index (iser_id,chatroom_id) so I select the row FOR
UPDATE, insert it in the archive table, then delete it.
Now, when a user logs out from the site, or when his session is purged by the auto-expiration cron
job, I must also expire ALL his open chatroom connections.
INSERT INTO archive (...) SELECT ... FROM active WHERE user_id = ...;
DELETE FROM active WHERE user_id = ...;
Now, if the user inserts a connection between the two queries above, the thing will fail (the
connection will just be deleted). I know that there are many ways to do it right :
- LOCK the table in exclusive mode
- use an additional primary key on the active table which is not related to the user_id and the
chatroom_id, select the id's of the sessions to expire in a temporary table, and use that
- use an extra field in the table to mark that the rows are being processed
- use transaction isolation level SERIALIZABLE
However, all these methods somehow don't feel right, and as this is an often encountered problem,
I'd really like to have a sql command, say MOVE, or SELECT AND DELETE, whatever, which acts like a SELECT,
returning the rows, but deleting them as well. Then I'd just do INSERT INTO archive (...) SELECT ... AND
DELETE FROM active WHERE user_id = ...;
which would have the following advantages : - No worries about locks : - less chance of bugs - higher performance because locks have to be waited on, by definition - No need to do the request twice (so, it is twice as fast !) - Simplicity and elegance
There would be an hidden bonus, that if you acquire locks, you better COMMIT the transaction as
soon as possible to release them, whereas here, you can happily continue in the transaction.
I think this command would make a nice cousin to the also very popular INSERT... OR UPDATE which
tries to insert a row, and if it exists, UPDATES it instead of inserting it !
What do you think ?
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
