Unintentional table lock



  •  Lately we had been noticing all kinds of random failures in our functional tests--and they all had something in common.  All of our random failures seemed to be database calls that were chosen to be deadlock victims.

    This was perplexing, to say the least. You see, we had carefully designed our process so that only one service call could be executed on one travel record at a time (we are an airline), thus guaranteeing that nobody should be competing for the same records in any database table.  So why the deadlocks?

    One of my colleagues just found the answer this morning... And it lies in a little easter egg introduced by a long-departed database developer:

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE [SEAT].[TRANSACTION] SET UPDATE_DATETIME = GETDATE()
    END
    

    facepalm



  • For those non-DBAs among us, is the problem here that it will trigger on its own update?



  •  @Sutherlands said:

    For those non-DBAs among us, is the problem here that it will trigger on its own update?

    No... The WTF is in the thread title... In updating any one row, because the UPDATE statement within the trigger is missing a WHERE clause, it will update every single other row in the database, thereby locking the whole table, and causing all sorts of concurrancy issues...



  • @ShatteredArm said:

     @Sutherlands said:

    For those non-DBAs among us, is the problem here that it will trigger on its own update?

    No... The WTF is in the thread title... In updating any one row, because the UPDATE statement within the trigger is missing a WHERE clause, it will update every single other row in the database, thereby locking the whole table, and causing all sorts of concurrancy issues...

    Ah, I see it now.


  • NM, I'm still learning TSQL; In oracle this would much easier.
    :S

    If you are using SQL 2005 or later this would do it:

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE [SEAT].[TRANSACTION]  SET UPDATE_DATETIME = GETDATE()
       WHERE id IN(SELECT id FROM inserted)
    END
    

    This would not; my bad

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE inserted SET UPDATE_DATETIME = GETDATE()
    END
    


  • @shoeboy said:

    NM, I'm still learning TSQL; In oracle this would much easier. :S

    If you are using SQL 2005 or later this would do it:

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE [SEAT].[TRANSACTION]  SET UPDATE_DATETIME = GETDATE()
       WHERE id IN(SELECT id FROM inserted)
    END
    

    This would not; my bad

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE inserted SET UPDATE_DATETIME = GETDATE()
    END
    

     

    Needs more cursors

     



  • @RTapeLoadingError said:

    @shoeboy said:

    NM, I'm still learning TSQL; In oracle this would much easier. :S

    If you are using SQL 2005 or later this would do it:

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE [SEAT].[TRANSACTION]  SET UPDATE_DATETIME = GETDATE()
       WHERE id IN(SELECT id FROM inserted)
    END
    

    This would not; my bad

    ALTER TRIGGER [SEAT].[TR_TRANSACTION_UPDATE_DATETIME]
    ON [SEAT].[TRANSACTION]
    AFTER UPDATE
    AS
    BEGIN
       UPDATE inserted SET UPDATE_DATETIME = GETDATE()
    END
    

     

    Needs more cursors

     

    If I thought you were serious, I would hunt you down and feed you, kicking and screaming, to pigs.

     



  • *Raises eyes heavenward* Why, for the love of all that is holy, is missing out the where clause not a fireable offence?



  • @havokk said:

    *Raises eyes heavenward* Why, for the love of all that is holy, is missing out the where clause not a fireable offence?

     

    Author was described as "long-departed" so who knows!?

     



  • @havokk said:

    Raises eyes heavenward Why, for the love of all that is holy, is missing out the where clause not an operation that fails without adding an additional clause?

    FTFY


    How many people have lost their job because SQL doesn't support: delete from important_table ALL RECORDS

    and fail on: delete from important_table



  • @cmccormick said:

    How many people have lost their job because their database lacked ROLLBACK?

    The feature exists...

    @cmccormick said:

    How many people have lost their job because they made a stupid unrecoverable mistake

     .. is probably more general. I suppose we'll descend into a discussion about great power demands great responsibility(and careful checking) but.. to err is human. I find many things in the IT world over-cautious, and others almost setting the user up for failure. YMMV.



  • @cmccormick said:

    @havokk said:

    *Raises eyes heavenward* Why, for the love of all that is holy, is missing out the where clause not an operation that fails without adding an additional clause?

    FTFY

    How many people have lost their job because SQL doesn't support: delete from important_table ALL RECORDS
    and fail on: delete from important_table
    And *why* are you running ad-hoc and untested queries against a production environment again? Of course this is assuming your dev and prod environments aren't the same...


  • Heh.  I remember several years ago, when Artix Entertainment was coming out with a new game, and I was in the beta.  A few days into the beta, suddenly no one was able to log in.  The next day, we got an explanation: all our accounts were gone and we had to recreate them.  Turns out one of the staff members wanted to change his character's name, and he did it directly in the DB.  Something like:

    UPDATE CHARACTERS SET NAME = :new_name

    Oops...



  • @C-Octothorpe said:

    And *why* are you running ad-hoc and untested queries against a production environment again? Of course this is assuming your dev and prod environments aren't the same...

     And why does *ANYONE* (keyword ONE) have an account with permissions to do such things.... Split Authentication is a great tool in these scenarios.


  • Trolleybus Mechanic

    @TheCPUWizard said:

    @C-Octothorpe said:

    And *why* are you running ad-hoc and untested queries against a production environment again? Of course this is assuming your dev and prod environments aren't the same...

     And why does *ANYONE* (keyword ONE) have an account with permissions to do such things.... Split Authentication is a great tool in these scenarios.

     

    For the same reason the same, shared database password is hardcoded into every production web.config I've ever seen.

    I sad.

     



  • @everyone said:

    The solution is more process, rules and punishments.

    Which planet are you from?



  • @Lorne Kates said:

    @TheCPUWizard said:

    @C-Octothorpe said:

    And why are you running ad-hoc and untested queries against a production environment again? Of course this is assuming your dev and prod environments aren't the same...

     And why does ANYONE (keyword ONE) have an account with permissions to do such things.... Split Authentication is a great tool in these scenarios.

     

    For the same reason the same, shared database password is hardcoded into every production web.config I've ever seen.

    I sad.

     

    I'm not surprised. When I started the current web project I'm on, we had a chat with the security guys to see how they wanted us to deploy the site. We were instructed to use the brand-spankin' new DMZ that has just been configured. They went through a long presentation of how the DMZ worked and about halfway through they mentioned that the DMZ was on its own AD domain and had no trusts with the internal domain. The database server is on the internal domain. I asked how we were going to use Windows Authentication to connect the ASP.Net web application to the SQL Server and the Chief Security Architect of a Fortune 20 company told me "put a username and password in web.config".

    I'm not sure what good a half dozen hardened firewalls do if you force everyone to sprinkle passwords in text files by policy.


  • :belt_onion:

    @Mason Wheeler said:

    UPDATE CHARACTERS SET NAME = :new_name

    Oops...

    The bigger oops being that NAME wasn't defined as a unique key. An ounce of prevention and all that.



  • Ummm... i've never configured IIS servers but wouldn't the proper solution be to have a pass thru client facing webserver in the DMZ pointing to a webserver in the same AD domain as the DB?

     Or you can enable sql authentication and put the password in the web.config because that just make more sense... :S



  • @shoeboy said:

    Ummm... i've never configured IIS servers but wouldn't the proper solution be to have a pass thru client facing webserver in the DMZ pointing to a webserver in the same AD domain as the DB?

     Or you can enable sql authentication and put the password in the web.config because that just make more sense... :S

    Riiiight... because having a fairly high-privileged account password in a config file is way less risk than allowing a very controlled amount of LDAP traffic through a firewall.

    Your first solution is bad because it takes the web server out of the DMZ, defeating the entire point.



  • @Jaime said:


    Riiiight... because having a fairly high-privileged account password in a config file is way less risk than allowing a very controlled amount of LDAP traffic through a firewall.
    Your first solution is bad because it takes the web server out of the DMZ, defeating the entire point.


    SQL authentication was supposed to be sarcasm; sorry;

    What I'm suggesting makes sense; let explain with Apache and Tomcat:
    Apache server in DMZ
    Tomcat server in same AD domain as DB

    Then you simply configure Apachae server to pass data between outside world and specific apps on the Tomcat server.
    What's wrog with this?



  • @shoeboy said:

    Then you simply configure Apachae server to pass data between outside world and specific apps on the Tomcat server.
    What's wrog with this?

    Ooo, ooo, I know! Tomcat is Java, and sucks shit. Right? Right?



  • @shoeboy said:

    @Jaime said:


    Riiiight... because having a fairly high-privileged account password in a config file is way less risk than allowing a very controlled amount of LDAP traffic through a firewall.
    Your first solution is bad because it takes the web server out of the DMZ, defeating the entire point.


    SQL authentication was supposed to be sarcasm; sorry;

    What I'm suggesting makes sense; let explain with Apache and Tomcat:
    Apache server in DMZ
    Tomcat server in same AD domain as DB

    Then you simply configure Apachae server to pass data between outside world and specific apps on the Tomcat server.
    What's wrog with this?

    Nothing... but my original problem was the the DMZ is set up so that nothing can talk to the internal domain.  Splitting the servers and solving the problem is no different from just solving the problem.  My company thinks that it's more import to isolate domain traffic than it is to centrally manage identities.


  • @Jaime said:

    @shoeboy said:

    @Jaime said:


    Riiiight... because having a fairly high-privileged account password in a config file is way less risk than allowing a very controlled amount of LDAP traffic through a firewall.
    Your first solution is bad because it takes the web server out of the DMZ, defeating the entire point.


    SQL authentication was supposed to be sarcasm; sorry;

    What I'm suggesting makes sense; let explain with Apache and Tomcat:
    Apache server in DMZ
    Tomcat server in same AD domain as DB

    Then you simply configure Apachae server to pass data between outside world and specific apps on the Tomcat server.
    What's wrog with this?

    Nothing... but my original problem was the the DMZ is set up so that nothing can talk to the internal domain.  Splitting the servers and solving the problem is no different from just solving the problem.  My company thinks that it's more import to isolate domain traffic than it is to centrally manage identities.
    They're fine with having the SQL server instance, the server itself and possibly the intire internal network p0wned because they're too retarded to setup cross domain authentication or trust between the domains? If all you're worried about is authentication against sql server, then you could get away with putting the username/password and encrypting the config file (or just the connection string section).  You can even then put the config source pointing to some internal NAS drive where the app pool account can only access it, but this would also still be a dead end because of the lack of trust between the domains.  Hmm, well, good luck with that!  :)



  • @C-Octothorpe said:

    They're fine with having the SQL server instance, the server itself and possibly the intire internal network p0wned because they're too retarded to setup cross domain authentication or trust between the domains?
    Yup.  BTW, the "SQL Server Instance" is an instance of SQL Server 2008 R2 DataCenter Edition with 256GB of RAM, 16 processors and about a hundred production databases.  But at least the guy who p0wns the web server isn't going to be able to talk to the internal domain controller!!

    @C-Octothorpe said:

    If all you're worried about is authentication against sql server, then you could get away with putting the username/password and encrypting the config file (or just the connection string section).  You can even then put the config source pointing to some internal NAS drive where the app pool account can only access it, but this would also still be a dead end because of the lack of trust between the domains.  Hmm, well, good luck with that!  :)

    The only sensible way forward here is to encrypt the config file and I did that, but that misses the point.  The security team is supposed to provide guidance.  If I simply did what they said, there would be a big risk.  They should have said:

    "We don't allow app teams to manage production service identities.  After you get your application installed, put in a ticket to request an identity.  We'll have someone from Identity Management create an account, add it to the proper groups, and assign the identity to the web site."



  • @Jaime said:

    @C-Octothorpe said:

    They're fine with having the SQL server instance, the server itself and possibly the intire internal network p0wned because they're too retarded to setup cross domain authentication or trust between the domains?
    Yup.  BTW, the "SQL Server Instance" is an instance of SQL Server 2008 R2 DataCenter Edition with 256GB of RAM, 16 processors and about a hundred production databases.  But at least the guy who p0wns the web server isn't going to be able to talk to the internal domain controller!!

    @C-Octothorpe said:

    If all you're worried about is authentication against sql server, then you could get away with putting the username/password and encrypting the config file (or just the connection string section).  You can even then put the config source pointing to some internal NAS drive where the app pool account can only access it, but this would also still be a dead end because of the lack of trust between the domains.  Hmm, well, good luck with that!  :)

    The only sensible way forward here is to encrypt the config file and I did that, but that misses the point.  The security team is supposed to provide guidance.  If I simply did what they said, there would be a big risk.  They should have said:

    "We don't allow app teams to manage production service identities.  After you get your application installed, put in a ticket to request an identity.  We'll have someone from Identity Management create an account, add it to the proper groups, and assign the identity to the web site."

    Traditionally, this has been my experience, give or take.  I have a much higher confidence when the production support team says things that I want to hear, like "you have to make sure your app-pool uses a non-interactive identity which will authenticate against the sql server instance, or no go. Passwords are forbidden, encrypted or not".  I've also had the flip-side of things when trying to use certs on our external facing web services, and they try to weasel out of having to install them because "it's too hard to manage", so we should just use anonymous access.

Log in to reply