Detecting Problems in the Database Access Code of Large Scale - PowerPoint PPT Presentation

Detecting Problems in the Database Access Code of Large Scale Systems An industrial Experience Report 1

Existing static analysis tools focus on language-related problems PMD Google error-prone Coverity FindBugs Facebook Infer However, many problems are related to how developers use different frameworks 2

Over 67% of Java developers use Object-Relational Mapping (Hibernate) to access databases 67% 22% Existing static analysis tools provide mostly rudimentary support for JDBC! 3

Over 40% of Java web application developers use Spring Developers use Spring to manage database transactions in web applications None of the static analysis tools support Spring! 4

There is a huge need for framework- specific tools Developers leverage MANY frameworks, but existing tools only support detecting language-related problems. 5

An example class with Java ORM code User class is User.java mapped to “ user” @Entity table in DB @Table(name = “user”) @DynamicUpdate Performance- public class User{ related configs @Column(name=“id”) id is mapped to the private int id; column “id” in the @Column(name=“name”) user table String userName; A user can belong @OneToMany(fetch= FetchType.EAGER ) to multiple teams List<Team> teams; Eagerly retrieve public void setName(String n){ associated teams userName = n; when retrieving a } 6 … other getter and setter methods user object

Accessing the database using ORM select u from user where u.id = 1; User u = findUserByID(1); ORM update user set database name=“Peter” where user.id = 1; u.setName(“Peter”); Objects SQLs 7

Transaction management using Spring Create a DB @Transaction ( Propogation.REQUIRED ) transaction getUser(){ … Entire business logic will updateUserGroup(u) be executed with the … same DB transaction } By using ORM and Spring, developers can focus more on the business logic and functionality 8

Implementing DBChecker • DBChecker looks for both functional and performance bug patterns • DBChecker is integrated in industrial practice Source code 9

Overview of the presentation Lessons learned when Bug patterns adopting the tool in practice 10

Overview of the presentation Lessons learned when Bug patterns adopting the tool in practice More patterns and learned lessons in the paper 11

ORM excessive data bug pattern Class User{ Eagerly retrieve @ EAGER teams from DB List<Team> teams; } User u = findUserById(1); Objects u.getName(); EOF User Table Team Table join Team data is never SQL used! 12

Detecting excessive data using static analysis Class User{ First find all the objects that @ EAGER eagerly retrieve data from DB List<Team> teams; } User user = findUserByID(1); Identify all the data usages of ORM-managed objects user team user.getName(); Check if the eagerly retrieved data is ever used user team 13

Nested transaction bug pattern Create a DB @Transaction ( Propogation. transaction REQUIRED ) getUser(){ @Transaction ( Propogation. updateUserGroup(u) REQUIRES_NEW ) … } Create a child transaction, and suspend parent transaction until child is finished Misconfigurations can cause unexpected transaction timeout, deadlock, or other performance-related problems 14

Detecting nested transaction bug pattern @Transaction ( Propogation. Parse all transaction REQUIRED ) configurations getUser(){ … Identify all methods with the updateUserGroup(u) annotation … } Propogation.REQUIRED Traverse the call graph to identify calls potential misconfigurations Propogation.REQUIRS_NEW 15

Limitation of current static analysis tools @Transaction ( Propo gation.REQUIRED ) @EAGER Annotations are lost Do not consider how when converting source developers configure code to byte code frameworks Many Many problems configurations are are related to set through framework annotations configurations 16

Overview of the presentation Lessons learned when Bug patterns adopting the tool in practice Most discussed bug patterns are related to incorrect usage of frameworks 17

Overview of the presentation Lessons learned when Bug patterns adopting the tool in practice Most discussed bug patterns are related to incorrect usage of frameworks 18

Handling a large number of detection results • Developers have limited time to fix detected problems • Most existing static analysis frameworks do not prioritize the detected instances for the same bug pattern 19

Prioritizing based on DB tables User • Problems related to large or frequently-accessed tables are Time zone ranked higher (more likely to be performance bottlenecks) • Problems related to highly dependable tables are ranked higher 20

Developers have different backgrounds • Not all developers are familiar with these frameworks and databases • Developers may not take the problems seriously if they don’t understand the impact 21

Educating developers about the detected problems • We hosted several workshops to educate developers about the impact and cause of the problems • Walk developers through examples of detected problems • May learn new bug patterns from developers 22

Overview of the presentation Lessons learned when Bug patterns adopting the tool in practice Most discussed bug We prioritize problems patterns are related to based on DB tables, and incorrect usage of educate developers about frameworks the problems 23

24

25

26

27

28

29

30

31