CODEWITHSUNDEEP
javasqlmultithreadingbukkit
Ariana
Ariana

Reputation: 755

Java - SQL And Threads - Dealing With Freezing

I'm developing a plugin for Bukkit and I'm starting to experiment with SQL. I have everything working, and have functions for less code bloat, etc.

My problem is that I noticed when I execute SQL updates the server will freeze all operations for about 5 seconds. With more looking into it, I had just enclosed all SQL statements into threads, and the issue has gone away. From my own personal guess, I imagine the freezing is caused by the SQL executing inside of the server thread, when these statements are executed since I'm waiting back on a result from the database, it will cause the main server thread to freeze until it retrieves these results, thus by putting the statements into a seperate thread this fixes the issue.

My question is, is it okay that I just put all SQL statements in threads? Is this a duct tape fix? Can I do a better job at fixing this issue?

Example Function 

public void makeUpdate(String statement){

    // TO BE USED WITH - INSERT, UPDATE, or DELETE
    Connection con = null;
    Statement st = null;

    String url = "jdbc:mysql://127.0.0.1/mydata_base";
    String user = "myuser";
    String password = "mypassword";

    try {
        con = (Connection) DriverManager.getConnection(url, user, password);
        st = (Statement) con.createStatement();

        int rs = st.executeUpdate(statement);

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Version.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {
        try {
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
    }
}

Example of code that causes server to freeze

// Here would be 3 statements updating user info.
// This would cause the server to freeze for about 5 seconds until these completed.
makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");
makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");
makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");

Example of code that fixes freezing.

new Thread(new Runnable(){
public void run(){
    makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");
    makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");
    makeUpdate("UPDATE blah SET blah = blah WHERE blah = blah");
    }
}).start();

Upvotes: 0

Views: 150

Answers (1)

biziclop
biziclop

Reputation: 49754

I don't think it's a duct tape fix, threads are the right way of dealing with asynchronous tasks.

Having said that, you should take a look at the Executor interface, which gives you a less hands-on way of creating and managing background tasks. (It will still be backed by threads but it gives you a lot of common functionality out of the box.)

Upvotes: 1

Related Questions