Interesting things have been happening around but I did not write down here for quite sometime.
I remember the incident - I got call around 3 am from the ops team when they noticed very high jdbc connection usage. I found that, one of our delete feature taking too much time in db and also causing others to be blocked.
This 'delete' is not such an important feature and we had been thinking to get rid of this permanent delete from database. We had been thinking not to delete any user permanently from database because, we are no longer running a small system. We have FK reference over thousand places in database. A permanent delete checks all the FK references in child tables, anyways. This takes a lot of time even though FK references were indexed. Till then, unfortunately we could never find a suitable window to do the release with the fix. The alternatives was - changing the user status to "deleted" by a flag.
Anyways, the flag change approach requires some programming time and a release schedule, which was not going to happen in a day or two even if we had been locking by this delete that night. User complaining slowness...
I thought, as a quick fix, I can put a trigger and prevent user from using that feature - I mean it's possible to stop user from deleting the records from that table.
CREATE OR REPLACE TRIGGER PREVENT_USER_DELETE
BEFORE DELETE
ON app_user
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20005,'User delete is temporarily unavailable');
END;
/
This simple trigger was a life saver that night. Few users who tried to delete application user permanently, they received application error page though, but the db never got locked in this cascading effect. This bought us some time to improve the feature of permanent delete - changing status flag.
***
I remember the incident - I got call around 3 am from the ops team when they noticed very high jdbc connection usage. I found that, one of our delete feature taking too much time in db and also causing others to be blocked.
This 'delete' is not such an important feature and we had been thinking to get rid of this permanent delete from database. We had been thinking not to delete any user permanently from database because, we are no longer running a small system. We have FK reference over thousand places in database. A permanent delete checks all the FK references in child tables, anyways. This takes a lot of time even though FK references were indexed. Till then, unfortunately we could never find a suitable window to do the release with the fix. The alternatives was - changing the user status to "deleted" by a flag.
Anyways, the flag change approach requires some programming time and a release schedule, which was not going to happen in a day or two even if we had been locking by this delete that night. User complaining slowness...
I thought, as a quick fix, I can put a trigger and prevent user from using that feature - I mean it's possible to stop user from deleting the records from that table.
CREATE OR REPLACE TRIGGER PREVENT_USER_DELETE
BEFORE DELETE
ON app_user
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20005,'User delete is temporarily unavailable');
END;
/
This simple trigger was a life saver that night. Few users who tried to delete application user permanently, they received application error page though, but the db never got locked in this cascading effect. This bought us some time to improve the feature of permanent delete - changing status flag.
***
No comments:
Post a Comment