Hi,

I'm doing some selects in row, like in 2 threads to be more specific.

The same select in both threads, and the resultset need to be the same too (6 rows).

All works fine, but in a random point of time, the select return 0 rows, and in the next iterations return 6 rows again.

I had this in random times, sometimes in 56 minutes and others with less than 1 minute.

Here it's my select:

SELECT *
  FROM titulo_receber
 WHERE dt_manutencao > CAST('2013-06-12 16:17:44.484' AS DATETIME)

I already tried:

SELECT *
  FROM titulo_receber
 WHERE dt_manutencao > '2013-06-12 16:17:44.484'

No errors are returned, neither in database or application.

I'm running on Java JDK7, I had turn on the debug log (-vr option) in db start, in the debug log were the selects all right, without any problem.

I'm out of solutions.

-- EDIT --

This is my code for the select:

public static Table_Object verifica_tabela(int n, Tabela_Sincronizacao ts) {        
    String table_name = ts.getNm_tabela();

    Table_Object table_object = new Table_Object();
    table_object.setNm_tabela(table_name);

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = conectorSybase.getConnection("-1");
        Long tempo1 = System.currentTimeMillis();
        if (conn != null) {
            String sql = "";

            java.sql.Timestamp sql_dt_ultima_sincronizacao = new java.sql.Timestamp(ts.getDt_ultima_sincronizacao().getTime());

            sql = "SELECT *"
                + "  FROM titulo_receber"
                + " WHERE dt_manutencao > CAST('2013-06-12 16:17:44.484' AS DATETIME)";

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            int row = 0;
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();

                row++;

                table_object.putRow(row);

                for(int i = 1; i <= rsmd.getColumnCount(); i++){
                    String  column = rsmd.getColumnName(i);
                    Integer type   = rsmd.getColumnType(i);
                    Object  object = null;

                    DateFormat dateFormat = null;

                    //Se for ds_encrypt joga null
                    if(column.equals("ds_encrypt")){
                        object = null;
                    } else {
                        switch(rsmd.getColumnType(i)){
                            case Types.ARRAY:
                                object = String.valueOf(rs.getArray(i));
                                break;
                            case Types.BIGINT:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.BINARY:
                                object = String.valueOf(rs.getBinaryStream(i));
                                break;
                            case Types.BIT:
                                object = String.valueOf(rs.getByte(i));
                                break;
                            case Types.BLOB:
                                object = String.valueOf(rs.getBlob(i));
                                break;
                            case Types.BOOLEAN:
                                object = String.valueOf(rs.getBoolean(i));
                                break;
                            case Types.CHAR:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.CLOB:
                                object = String.valueOf(rs.getClob(i));
                                break;
                            case Types.DATALINK:
                                object = null;
                                break;
                            case Types.DATE:
                                dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                java.sql.Date data = rs.getDate(i);
                                object = data == null ? null : dateFormat.format(data);
                                break;
                            case Types.DECIMAL:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.DISTINCT:
                                object = null;
                                break;
                            case Types.DOUBLE:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.FLOAT:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.INTEGER:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.JAVA_OBJECT:
                                object = String.valueOf(rs.getObject(i));
                                break;
                            case Types.LONGNVARCHAR:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.LONGVARBINARY:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.LONGVARCHAR:
                                object = null;
                                break;
                            case Types.NCHAR:
                                object = null;
                                break;
                            case Types.NCLOB:
                                object = String.valueOf(rs.getNClob(i));
                                break;
                            case Types.NULL:
                                object = null;
                                break;
                            case Types.NUMERIC:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.NVARCHAR:
                                object = null;
                                break;
                            case Types.OTHER:
                                object = null;
                                break;
                            case Types.REAL:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.REF:
                                object = String.valueOf(rs.getRef(i));
                                break;
                            case Types.ROWID:
                                object = String.valueOf(rs.getRowId(i));
                                break;
                            case Types.SMALLINT:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.SQLXML:
                                object = String.valueOf(rs.getSQLXML(i));
                                break;
                            case Types.STRUCT:
                                object = null;
                                break;
                            case Types.TIME:
                                dateFormat = new SimpleDateFormat("HH:mm:ss");
                                java.sql.Time time = rs.getTime(i);
                                object = time == null ? null : dateFormat.format(time);
                                break;
                            case Types.TIMESTAMP:
                                dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                java.sql.Timestamp timestamp = rs.getTimestamp(i);
                                object = timestamp == null ? null : dateFormat.format(timestamp);
                                break;
                            case Types.TINYINT:
                                object = String.valueOf(rs.getString(i));
                                break;
                            case Types.VARBINARY:
                                object = null;
                                break;
                            case Types.VARCHAR:
                                object = String.valueOf(rs.getString(i));
                                break;
                        }   
                    }                        
                    table_object.putRowValue(row, column, type, object);
                }
            }                
            System.out.println("Thread[" + n + "] - " + "SQL: " + sql + "\nRows: " + row + "\ntable_object: " + table_object.getHm_rows().size());
            Long tempo2 = System.currentTimeMillis();
        } else {
            System.out.println("Thread[" + n + "] - " + "Conexão com o Banco foi NULL!");
        }
    } catch (SQLException e) {
        System.out.println("Thread[" + n + "] - " + "[ERRO] SQLException em Tabela_SincronizacaoMB.getTabela_Sincronizacao_Consulta(): " + e.getMessage()
                            + "\nErrorCode: " + e.getErrorCode() 
                            + "\nSQLState: " + e.getSQLState());
        e.printStackTrace();

        table_object = null;
    } catch (Exception e) {
        System.out.println("Thread[" + n + "] - " + "[ERRO] Exception em Tabela_SincronizacaoMB.getTabela_Sincronizacaos()\n" + e.getMessage());
        e.printStackTrace();

        table_object = null;
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException ex) {
                System.out.println("Thread[" + n + "] - " + "ERRO ao fechar PS: " + ex.getStackTrace());
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                System.out.println("Thread[" + n + "] - " + "ERRO ao fechar CONN: " + ex.getStackTrace());
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                System.out.println("Thread[" + n + "] - " + "ERRO ao fechar RS: " + ex.getStackTrace());
            }
        }
    }        
    return table_object;
}

[]'s William Bertan

asked 12 Jun '13, 16:47

WilliamBertan's gravatar image

WilliamBertan
31114
accept rate: 0%

edited 14 Jun '13, 07:10

Is that the exact query your application is executing? Using a timestamp literal that is always 2013-06-12 16:17:44.484?

(12 Jun '13, 17:50) Breck Carter

Yes, in this test yes, but I'm afraid the same problem is happening in our production servers, where that timestamp is programmatically inserted, but in that same format (yyyy-MM-dd hh:mm:ss.SSS)

(12 Jun '13, 18:03) WilliamBertan
Replies hidden
1

If the timestamp was obtained from the Windows system date and time, I would say the computer has an unstable clock that gains or loses time, and Windows periodically resets the system time based on a global NTP clock, as described here: http://sqlanywhere.blogspot.com/2011/07/beware-current-timestamp.html

BUT, you say it is a fixed literal which sometimes works and sometimes does not work... the exact same timestamp literal which never ever changes... so that can't be the explanation. I am baffled.

(13 Jun '13, 09:52) Breck Carter

Yes, I looked in the debug log in the db, and the select was all right, if the right datetime.

I can only simulate this issue when I run more than one thread, because with only one thread this can't happen.

Maybe this can be an issue, the db can get crazy and block the results?

(13 Jun '13, 10:10) WilliamBertan
1

I want to know what the SELECT looks like in your code, NOT what the SELECT looks like when it goes to the server. Please show us your code.

(13 Jun '13, 16:55) Breck Carter

I edited my question and now has the code.

(14 Jun '13, 07:13) WilliamBertan

In case the select only returns 0 rows, are there any warnings/errors returned to your code (like timeouts because of blocking)?

And the according rows are not modified while you're doing your tests?

(14 Jun '13, 07:45) Volker Barth
Replies hidden

No Warnings, No errors, nothing, even debugging the code that is all right! In the debug log of the db it's all right if the select, and in the Java Program too. I'm doing update in those rows, but it's select and in the select even with the table locked, that must return the result, right? And I tested without the update statement and the error occurs too. :(

(14 Jun '13, 08:22) WilliamBertan

Whether a SELECT will return rows while an UPDATE of the same table takes place, does depend on the settings of the isolation level and of the blocking options (and possibly other circumstances). For example, if you do use isolation level 1 or higher, your SELECT may have to wait until the UPDATE transaction has committed (or rolled back) if BLOCKING is on, or will return an error if BLOCKING is off or the blocking timeout has been reached. However, in case of blocked transactions, you should receive SQLCODE -210 or other errors.

(14 Jun '13, 09:32) Volker Barth
showing 3 of 9 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×108
×69
×20

question asked: 12 Jun '13, 16:47

question was seen: 2,558 times

last updated: 14 Jun '13, 09:32